470,863 Members | 1,150 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,863 developers. It's quick & easy.

REF CURSOR returned from Java -- How to use?

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?
Jul 19 '05 #1
1 9434
Jim Garrison wrote:
Oracle has a tech article describing how a Java stored procedure
can return a REF CURSOR:

declare
TYPE RC IS REF CURSOR RETURN TEST_INT_TRANS%rowtype;

Make the cursor type generic, thus:
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.
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Matthew Houseman | last post: by
reply views Thread by Prashanth | last post: by
4 posts views Thread by Bernard Dhooghe | last post: by
1 post views Thread by Jim Garrison | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.