how to join cursor returned from procedure with other tables in oracle

Question

i have a procedure that give me a refcursor as out parameter and i have to join that data with other tables in my own package.

i try to fetch cursor in a variable of the same return type but get: PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list

types defined in external package (i cannot alter it)

CURSOR CUR_MYTYPE IS SELECT f1... FROM xxx
TYPE MYTYPE IS REF_CURSOR RETURN CUR_MYTYPE%ROWTYPE

my own code:

result SCHEMA.MYTYPE;
cur sys_refcursor;
check VARCHAR2;

PKG.PROCEDURE_NAME(check,cur);

fetch cur bulk collect into result ;
    
      
select t.f1, t.f2, t.f3, o.f1, o.f2
from table(result ) t
   inner join otherTable o
    on o.f1 = t.f1

Answer 1

Are you sure you've got all that correct? You cannot bulk collect into "result" if result is defined as a refcursor, eg

SQL> declare
  2    cursor cur_mytype is select * from emp;
  3    type mytype is ref cursor return cur_mytype%rowtype;
  4    cur sys_refcursor;
  5    result mytype;
  6  begin
  7    open cur for select * from emp;
  8    fetch cur bulk collect into result;
  9  end;
 10  /
  fetch cur bulk collect into result;
                              *
ERROR at line 8:
ORA-06550: line 8, column 31:
PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list

I am going to assume it is more like this:

SQL> declare
  2    cursor cur_mytype is select * from emp;
  3    type mytype is table of cur_mytype%rowtype index by pls_integer;
  4
  5    cur sys_refcursor;
  6    result mytype;
  7  begin
  8    open cur for select * from emp;
  9    fetch cur bulk collect into result;
 10  end;
 11  /

PL/SQL procedure successfully completed.

In any event, MYTYPE needs to be a nested table type (which it is not in the case above) so that you can run the TABLE() function around it. If that is not the case, then you could create your own local schema type which maps to the columns and then transfer the rows to that, eg

--
-- incorrect type
--
SQL> declare
  2    cursor cur_mytype is select * from emp;
  3    type mytype is table of cur_mytype%rowtype index by pls_integer;
  4
  5    cur sys_refcursor;
  6    result mytype;
  7  begin
  8    open cur for select * from emp;
  9    fetch cur bulk collect into result;
 10
 11    for i in ( select * from table(result) )
 12    loop
 13      null;
 14    end loop;
 15  end;
 16  /
  for i in ( select * from table(result) )
                                 *
ERROR at line 11:
ORA-06550: line 11, column 34:
PLS-00382: expression is of wrong type
ORA-06550: line 11, column 28:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
ORA-06550: line 11, column 14:
PL/SQL: SQL Statement ignored

--
-- data copied to corrected type
--
SQL> create or replace type emp_row as object (
  2     empno      number(4)
  3    ,ename      varchar2(10)
  4    ,job        varchar2(9)
  5    ,mgr        number(4)
  6    ,hiredate   date
  7    ,sal        number(7,2)
  8    ,comm       number(7,2)
  9    ,deptno     number(2)
 10  );
 11  /

Type created.

SQL>
SQL> create or replace type my_fixed_type as table of emp_row;
  2  /

Type created.

SQL> declare
  2    cursor cur_mytype is select * from emp;
  3    type mytype is table of cur_mytype%rowtype index by pls_integer;
  4
  5    cur sys_refcursor;
  6    result mytype;
  7
  8    fixed_result my_fixed_type := my_fixed_type();
  9
 10  begin
 11    open cur for select * from emp;
 12    fetch cur bulk collect into result;
 13
 14    for i in 1 .. result.count loop
 15      fixed_result.extend;
 16      fixed_result(i) :=
 17        emp_row(
 18          result(i).empno,
 19          result(i).ename,
 20          result(i).job,
 21          result(i).mgr,
 22          result(i).hiredate,
 23          result(i).sal,
 24          result(i).comm,
 25          result(i).deptno);
 26    end loop;
 27
 28    for i in ( select * from table(fixed_result) )
 29    loop
 30      null;
 31    end loop;
 32  end;
 33  /

PL/SQL procedure successfully completed.