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
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.