Running against Oracle 10.2.0.1.0 database with 11g client or 10g client,
the following package fails.
As sample, run the spec and body to create the testpackage script:
CREATE OR REPLACE PACKAGE testPackage AS
PROCEDURE CallMe( user_id_i IN VARCHAR2,
c1_o OUT SYS_REFCURSOR,
c2_o OUT SYS_REFCURSOR);
PROCEDURE p1( user_id_i IN VARCHAR2,
c1_o OUT SYS_REFCURSOR);
PROCEDURE p2( user_id_i IN VARCHAR2,
c2_o OUT SYS_REFCURSOR);
END testPackage;
/
CREATE OR REPLACE PACKAGE BODY testPackage AS
PROCEDURE CallMe( user_id_i IN VARCHAR2,
c1_o OUT SYS_REFCURSOR,
c2_o OUT SYS_REFCURSOR) IS
BEGIN
p1(user_id_i,c1_o);
p2(user_id_i,c2_o);
END;
PROCEDURE p1( user_id_i IN VARCHAR2,
c1_o OUT SYS_REFCURSOR) IS
BEGIN
OPEN c1_o FOR
select to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss') from dual;
END;
PROCEDURE p2( user_id_i IN VARCHAR2,
c2_o OUT SYS_REFCURSOR) IS
BEGIN
OPEN c2_o FOR
select to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss') from dual;
END;
END testPackage;
/
After creating the package | click on the Set Parameter window | click on the Output Options
for Anonymous Block | check on "Load into grid from memory (strong and weak)
| close the Set Parameter window and click on "Execute PLSQL with debugger."
Then, immediately the "ORA-00604: error occurred at recursive SQL level 1 ORA-01001: invalid cursor" error message will appear.
WORKAROUND
None
STATUS
Waiting for fix in a future release of TOAD for Oracle.
