REF CURSOR returned from Java -- How to use?

From: Jim Garrison <jhg_at_athensgroup.com>
Date: Tue, 21 Sep 2004 11:13:36 -0500
Message-ID: <3o-dncQ_ROKMzs3cRVn-vQ@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 - 11:13:36 CDT