CODEWITHSUNDEEP
sqlroracle-databaseplsqlenterprise
Huu Vinh Nguyen
Huu Vinh Nguyen

Reputation: 81

PL/SQL: Type mismatch when fetching cursor from pipeline table function

I would like to simply SQL interface to Oracle R Enterprise.

First, I create a global object type and a table type from the type. 

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;

Second, I make the pipelined function to return the table object. 

create or replace function f_sts_one_sample_t_test
     (p_data in sys_refcursor, 
        target_number in number) 
     return sts_one_sample_t_test_table pipelined
is
    v_sts_one_sample_t_test  sts_one_sample_t_test;
    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  sts_one_sample_t_test_table  
             )
      );
begin
    v_sts_one_sample_t_test:=sts_one_sample_t_test(null,null,null,null,null,null);
    open v_cursor;
    loop
        fetch v_cursor into v_sts_one_sample_t_test;       --- [Error] PLS-00386 (49: 17): PLS-00386: type mismatch found at 'V_STS_ONE_SAMPLE_T_TEST' between FETCH cursor and INTO variables
        exit when v_cursor%notfound;
        pipe row(v_sts_one_sample_t_test);
    end loop;
    close v_cursor;
    return;
end f_sts_one_sample_t_test;

But error is raised from compiler:

[Error] PLS-00386 (49: 17): PLS-00386: type mismatch found at 'V_STS_ONE_SAMPLE_T_TEST' between FETCH cursor and INTO variables

Please help me.

Upvotes: 1

Views: 1111

Answers (1)

Huu Vinh Nguyen
Huu Vinh Nguyen

Reputation: 81

I have found solution.

Instead of using fetch into object type, I fetch into each object element explicitly. 

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%)" ;

The error is passed, but the function go to infinite loop when running.

Upvotes: 1

Related Questions