Oracle stored procedures - returning a cursor from a procedure that opens a cursor

Question

Using .Net and Oracle 11g - I am returning dataTables from the procedure inside the package by opening the cursor.

IE - "OPEN TABLEREF FOR SOMESQL; Where TableRef - parameter" OUT ". It works fine.

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

Inside Proc1 (which has a TableRef parameter as an OUT parameter). I am running Execute Immediate to call Proc2. Proc2 also has a TableRef parameter defined as an out parameter; OPEN TABLEREF FOR SOMESQL works. All of this compiles fine, but when I try to run it; I get the following error:

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

Can someone tell me what I am doing wrong?

EDIT If I modify my execute statement immediately 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 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;