Oracle Data Miner - 简化Oracle R Enterprise界面

I need to simplify Oracle SQL Interface to Oracle R Enterprise so that it is earsier to user to other users.

我需要将Oracle SQL接口简化为Oracle R Enterprise，以便用户对其他用户更敏感。

For example, to calculate One Sample T-Test by calling R funciton "R_ONE_SAMPLE_T_TEST", in original way I have run following SSQ statement:

例如，要通过调用R函数“R_ONE_SAMPLE_T_TEST”来计算One Sample T-Test，原始方式我运行了以下SSQ语句：

select *
from
table
(
rqTableEval(
  cursor
  (
    select * 
    from "INSUR_CUST_LTV_SAMPLE"
  ), -- Input Cursor 
  cursor
  (
    select 10 as "target_number",
           1 as "ore.connect"
    from dual
  ), -- Param Cursor 
  'select 
      str_col as "Variable Name",
      num_col as "Average",
      num_col as "T-Test",
      num_col as "P-Value",
      num_col as "Con.Level Lower Bound (95%)",
      num_col as "Con.Level Upper Bound (95%)"
   from RQSYS.RQ_TEMP 
   WHERE ROWNUM=1', -- Output Definition 
  'R_ONE_SAMPLE_T_TEST' -- R Script 
)
)

I hope i can simplify it by the following statement:

我希望我可以通过以下声明简化它：

select *
from table
(
   pkg_one_sample_t_test.f_run
   (
       cursor(select * from "INSUR_CUST_LTV_SAMPLE"),
       10 -- Target number
    )
)

So I wrote the package pkg_one_sample_t_test:

所以我写了包pkg_one_sample_t_test：

create or replace package pkg_one_sample_t_test as

     type one_sample_t_test is record 
     (
            "Variable Name" varchar2(4000),
            "Average" number,
            "T-Test" number,
            "P-Value" number,
            "Con.Level Lower Bound (95%)" number,
            "Con.Level Upper Bound (95%)" number
     );

     type one_sample_t_test_table is table of  one_sample_t_test;

     function f_run
     (
        p_data in sys_refcursor, 
        target_number in number
     ) 
     return one_sample_t_test_table pipelined;

end pkg_one_sample_t_test;


create or replace package body pkg_one_sample_t_test as

     function f_run
     (
        p_data in sys_refcursor, 
        target_number in number
     ) 
     return one_sample_t_test_table pipelined
     is
            v_one_sample_t_test  one_sample_t_test;
--            cursor v_cursor is
--            select 'a', 1, 1,1, 1, 1 from dual;
            cursor v_cursor is 
            select *
            from 
            table
            (
                   cast
                   (
                         rqTableEval
                         (
                              p_data, 
                              cursor
                              (
                                select 
                                        target_number as "target_number",
                                        1 as "ore.connect"
                                from dual
                              ), -- Param Cursor 
                              'select 
                                          str_col as "Variable Name",
                                          num_col as "Average",
                                          num_col as "T-Test",
                                          num_col as "P-Value",
                                          num_col as "Con.Level Lower Bound (95%)",
                                          num_col as "Con.Level Upper Bound (95%)"
                               from RQSYS.RQ_TEMP 
                               WHERE ROWNUM=1', -- Output Definition 
                              'R_ONE_SAMPLE_T_TEST' -- R Script 
                        )
                 as 
                        one_sample_t_test_table   ----PL/SQL: ORA-00902: invalid datatype
                 )
          );

     begin
            open v_cursor;
            loop
                fetch v_cursor into v_one_sample_t_test;
                exit when v_cursor%notfound;
                pipe row(v_one_sample_t_test);
            end loop;
            close v_cursor;
            return;
     end;

end pkg_one_sample_t_test;

But at the compiler return error PL/SQL: ORA-00902: invalid datatype for the table type one_sample_t_test_table which is already defined inside package header.

但是在编译器返回错误PL / SQL：ORA-00902：表类型one_sample_t_test_table的无效数据类型已经在包头内定义。

How can I solve this problem?

我怎么解决这个问题？

Thank you.

谢谢。

1 个解决方案

#1

Only Oracle 12c allows SQL statements to access package types. In earlier versions it is necessary to create those types as schema objects like below. Even though your SQL statement is only used inside a PL/SQL package, when it runs it is run in a SQL context and does not have access to the PL/SQL package types.

只有Oracle 12c允许SQL语句访问包类型。在早期版本中，有必要将这些类型创建为如下所示的模式对象。尽管您的SQL语句仅在PL / SQL包中使用，但在运行时它在SQL上下文中运行，并且无法访问PL / SQL包类型。

create or replace type one_sample_t_test is object
(
    "Variable Name" varchar2(4000),
    "Average" number,
    "T-Test" number,
    "P-Value" number,
    "Con.Level Lower Bound (95%)" number,
    "Con.Level Upper Bound (95%)" number
);

create or replace type one_sample_t_test_table is table of  one_sample_t_test;