REF CURSOR returned from Java -- How to use?
Date: Tue, 21 Sep 2004 11:13:36 -0500
Message-ID: <3o-dncQ_ROKMzs3cRVn-vQ_at_giganews.com>
Oracle has a tech article describing how a Java stored procedure can return a REF CURSOR:
http://www.oracle.com/technology/sample_code/tech/java/codesnippet/jdbc/refcur/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? Received on Tue Sep 21 2004 - 18:13:36 CEST