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?

Jim Garrison wrote:

TYPE RC IS REF CURSOR;
and the problem goes away

I guess the cursor loses its class identity when being
marshalled through the interface and becomes a generic
REF CURSOR, and gets cast back to the correct type
automagically on the fetch.