Oracle has a tech article describing how a Java stored procedure
can return a REF CURSOR:
http://www.oracle.com/technology/sam...cur/index.html
Here's the procedure spec:
CREATE OR REPLACE PACKAGE REF_CURSOR_TEST AS
TYPE RC is ref cursor return test_int_trans%rowtype;
FUNCTION TEST return rc;
END REF_CURSOR_TEST;
If I use the SQL/Plus syntax given in the example to access
the returned REF CURSOR, everything works:
SQL>variable x refcursor
SQL>execute :x := getemps;
SQL>print x
This prints all the rows in the cursor. However, I'm stumped on
how to use the resulting cursor from PL/SQL code. Here's what I've
tried:
declare
TYPE RC IS REF CURSOR RETURN TEST_INT_TRANS%rowtype;
C1 RC;
ITEM PRO_INT_TRANS_ITEM;
TRANS TEST_INT_TRANS%rowtype;
begin
c1 := ref_cursor_test.test();
loop
fetch c1 into trans;
exit when c1%notfound;
item := trans.item;
dbms_output.put_line(item.member_id);
end loop;
end;
The error message is:
ORA-06550: line 7, column 10:
PLS-00382: expression is of wrong type
It appears to be complaining about the assignment
to c1. Can anyone point out where I'm going wrong?