Home » SQL & PL/SQL » SQL & PL/SQL » Simplify SQL Interface to Oracle R Enterprise (Oracle 12c)
Simplify SQL Interface to Oracle R Enterprise [message #649453] Sat, 26 March 2016 03:31 Go to next message
vinhnh
Messages: 2
Registered: March 2016
Location: Viet Nam
Junior Member
I need to simplify Oracle SQL Interface to Oracle R Enterprise so that it is earsier to other users.

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

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
     (
        f_sts_one_sample_t_test
        (
            cursor(select * from ODMR_CARS_DATA),
            10
         )
     )


To do it, I write the following code:


create or replace type sts_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 sts_one_sample_t_test_table is table of  sts_one_sample_t_test;

     create or replace
     function f_sts_one_sample_t_test
     (
            p_data in sys_refcursor, 
            p_target_number in number
     ) 
    return sts_one_sample_t_test_table pipelined
     is
            v_sts_one_sample_t_test  sts_one_sample_t_test;
            --v_cursor  sys_refcursor;
            type ref_cursor is ref cursor;
            v_cursor ref_cursor;
     begin
            v_sts_one_sample_t_test:=sts_one_sample_t_test(null,null,null,null,null,null);
            
           open v_cursor for 
           select *
           from 
           table
            (
                   cast
                   (
                         rqTableEval
                         (
                              p_data, 
                              cursor
                              (
                                select 
                                        p_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 
                        sts_one_sample_t_test_table   ----PL/SQL: ORA-00902: invalid datatype
                 )
          );

            
            loop
                        fetch v_cursor into 
                                   v_sts_one_sample_t_test."Variable Name" ,
                                   v_sts_one_sample_t_test. "Average" ,
                                   v_sts_one_sample_t_test."T-Test" ,
                                   v_sts_one_sample_t_test."P-Value" ,
                                   v_sts_one_sample_t_test."Con.Level Lower Bound (95%)" ,
                                   v_sts_one_sample_t_test."Con.Level Upper Bound (95%)" 
                        ;   
                exit when v_cursor%notfound;
                pipe row(v_sts_one_sample_t_test);
                null;
            end loop;
            close v_cursor;
            --return v_col_list;
            return;
     end f_sts_one_sample_t_test;


My code is compiled successfully. But when I run my SQL


select * 
from table
     (
        f_sts_one_sample_t_test
        (
            cursor(select * from ODMR_CARS_DATA),
            10
         )
     )


It seem to go into infinite loop.

Do anyone can guide me to fix the problem or give me some hint about another way to do it?

Report message to a moderator

Re: Simplify SQL Interface to Oracle R Enterprise [message #649454 is a reply to message #649453] Sat, 26 March 2016 03:48 Go to previous messageGo to next message
garan
Messages: 27
Registered: January 2016
Junior Member
Please include the complete table definitions script which you are using in your function ex RQSYS.RQ_TEMP so that others can simulate your problem

Report message to a moderator

Re: Simplify SQL Interface to Oracle R Enterprise [message #649455 is a reply to message #649454] Sat, 26 March 2016 04:11 Go to previous message
vinhnh
Messages: 2
Registered: March 2016
Location: Viet Nam
Junior Member
Hi garan

Here is RQ_Temp table script. 


create table RQ_SYS.RQ_TEMP 
(
    str_col varchar2(4000),
    num_col number
)

GRANT ALL ON RQSYS.RQ_TEMP TO PUBLIC;


And here is my R Script:


begin
  -- sys.rqScriptDrop('R_ONE_SAMPLE_T_TEST');
  sys.rqScriptCreate('R_T_TEST_ONE_SAMPLE',
  '	function(dat, target_number)
	{
	  t.test.col_colname = c()
	  t.test.statistic=c()
	  t.test.p.value= c()
	  t.test.mean = c()
	  t.test.cf.lowerBound = c()
	  t.test.cf.upperBound = c()
	  for (col_name in names(dat))
	  {
		if (is.numeric(dat[,col_name])) 
		{
		  t.test_res <- t.test(dat[,col_name], mu= target_number)
		  t.test.col_colname <- append(t.test.col_colname,col_name)
		  t.test.statistic <- append(t.test.statistic,t.test_res$statistic) 
		  t.test.p.value <- append(t.test.p.value,t.test_res$p.value)
		  t.test.mean <- append(t.test.mean,t.test_res$estimate)
		  t.test.cf.lowerBound <- append(t.test.cf.lowerBound,t.test_res$conf.int[1])
		  t.test.cf.upperBound <- append(t.test.cf.upperBound,t.test_res$conf.int[2])
		}
	  }
	  
	  df <- data.frame(Colname=t.test.col_colname,
					   mean=t.test.mean, 
					   t_value=t.test.statistic,
					   p_value=t.test.p.value,
					   lowerbound=t.test.cf.lowerBound,
					   upperbound=t.test.cf.upperBound
					   )
	}'
	);
	rqGrant('R_T_TEST_ONE_SAMPLE', 'rqscript', '');
end;

Report message to a moderator

Previous Topic: Multi Set operator (MULTISET UNION DISTINCT) in PL/SQL
Next Topic: Column to Row
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 18 22:08:30 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.