Oracle Stored Procedures - Returning a Cursor From a Procedure that Opens the Cursor...

Question

Using .Net and Oracle 11g - I've been returning dataTables from a procedure inside of a package by opening a Cursor.

IE - 'OPEN TABLEREF FOR SOMESQL; Where TableRef is an 'OUT' Param. It's been working great.

What I'm struggling to do is have that first Proc call another Proc and let that second Proc open the cursor.

Inside Proc1 (which has the TableRef as an OUT param) - I'm doing an Execute Immediate to call Proc2. Proc2 also has the TableRef defined as an out param, does the OPEN TABLEREF FOR SOMESQL. It all compiles fine - but when I try to run it; I get the following error:

ORA-00604: error occurred at recursive SQL level 1 ORA-01001: invalid cursor

Can anyone tell me what I'm doing wrong?

EDIT If I modify my execute immediate statement to include 'OUT TABLEREF' instead of 'TABLEREF' my error changes to... ORA-03113: end-of-file on communication channel

Answer 1

When passing cursor variables between dynamic SQL, you may need to declare it as IN OUT:

BEGIN
    EXECUTE IMMEDIATE '
    BEGIN
        EXECUTE IMMEDIATE ''BEGIN OPEN :tableRef FOR SELECT 1 FROM dual; END;'' USING IN OUT :tableRef;
    END;
    ' USING IN OUT :tableRef;
END;