How do I view SYS_REFCURSOR output parameters in TOAD?

[s***@gmail.com]

I'm new to Oracle and TOAD although I have years of experience with SQL
Server. I'm comfortable creating procedures and such in TOAD and I can
get back the results of a function, but I can't figure out how to view
the results of output parameters in TOAD, specifically SYS_REFCURSOR
output parameters. Here is a simple example of what I am trying to do:

CREATE OR REPLACE PACKAGE MY_PKG AS
TYPE CURSOR_A IS REF CURSOR ;
TYPE CURSOR_B IS REF CURSOR ;
PROCEDURE SAMPLE_FUNC (CURSOR_A OUT SYS_REFCURSOR, CURSOR_B OUT
SYS_REFCURSOR, lastName VARCHAR);
END MY_PKG;
/
CREATE OR REPLACE PACKAGE BODY MY_PKG AS
PROCEDURE SAMPLE_FUNC (CURSOR_A OUT SYS_REFCURSOR, CURSOR_B OUT
SYS_REFCURSOR, lastName VARCHAR)
IS
BEGIN

OPEN CURSOR_A FOR
SELECT * from EMPLOYEES WHERE EMP_LAST_NAME = lastName;


OPEN CURSOR_B FOR
SELECT * from EMPLOYEE_GROUPS;

END SAMPLE_FUNC;
END MY_PKG;

I call this package and function from .NET and it works fine, returning
the two result sets, but I would like to be able to test this in TOAD
with something like this:

DECLARE
test1 SYS_REFCURSOR;
test2 SYS_REFCURSOR;

BEGIN

exec MY_PKG.SAMPLE_FUNC(test1, test2, 'Smith');

print test1;

print test2;

END;

Obviously this syntax does not work. What do I need to do to make it
work?

[Emmanuel]

Post by s***@gmail.com
I'm new to Oracle and TOAD although I have years of experience with SQL
Server. I'm comfortable creating procedures and such in TOAD and I can
get back the results of a function, but I can't figure out how to view
the results of output parameters in TOAD, specifically SYS_REFCURSOR
CREATE OR REPLACE PACKAGE MY_PKG AS
TYPE CURSOR_A IS REF CURSOR ;
TYPE CURSOR_B IS REF CURSOR ;
PROCEDURE SAMPLE_FUNC (CURSOR_A OUT SYS_REFCURSOR, CURSOR_B OUT
SYS_REFCURSOR, lastName VARCHAR);
END MY_PKG;
/
CREATE OR REPLACE PACKAGE BODY MY_PKG AS
PROCEDURE SAMPLE_FUNC (CURSOR_A OUT SYS_REFCURSOR, CURSOR_B OUT
SYS_REFCURSOR, lastName VARCHAR)
IS
BEGIN
OPEN CURSOR_A FOR
SELECT * from EMPLOYEES WHERE EMP_LAST_NAME = lastName;
OPEN CURSOR_B FOR
SELECT * from EMPLOYEE_GROUPS;
END SAMPLE_FUNC;
END MY_PKG;
I call this package and function from .NET and it works fine, returning
the two result sets, but I would like to be able to test this in TOAD
DECLARE
test1 SYS_REFCURSOR;
test2 SYS_REFCURSOR;
BEGIN
exec MY_PKG.SAMPLE_FUNC(test1, test2, 'Smith');
print test1;
print test2;
END;
Obviously this syntax does not work. What do I need to do to make it
work?

You can try this :

set serveroutput on
DECLARE
test1 SYS_REFCURSOR;
test2 SYS_REFCURSOR;

BEGIN

exec MY_PKG.SAMPLE_FUNC(test1, test2, 'Smith');

dbms_output.put_line(test1);

dbms_output.put_line(test2);

END;

Emmanuel

[s***@gmail.com]

Closer than I got, but still not working. I get this error:

PLS-00103: Encountered the symbol "MY_PKG" when expecting one of the
following:
:= . ( @ % ;

Any ideas?

[tinojam]

I don't think you can achieve what you want in TOAD but you can in
SQLPLUS.

Please see below and tell me if it works...

***@OCP> CREATE OR REPLACE PACKAGE MY_PKG AS
2 TYPE CURSOR_A IS REF CURSOR ;
3 TYPE CURSOR_B IS REF CURSOR ;
4 PROCEDURE SAMPLE_FUNC (CURSOR_A OUT SYS_REFCURSOR,
CURSOR_B OUT SYS_REFCURSOR, lastName
VARCHAR);
5 END MY_PKG;
6 /

Package created.

***@OCP>
***@OCP> CREATE OR REPLACE PACKAGE BODY MY_PKG AS
2 PROCEDURE SAMPLE_FUNC (CURSOR_A OUT SYS_REFCURSOR,
CURSOR_B OUT SYS_REFCURSOR, lastName
VARCHAR)
3 IS
4 BEGIN
5
6
7 OPEN CURSOR_A FOR
8 SELECT * from EMP WHERE ENAME = lastName;
9
10
11 OPEN CURSOR_B FOR
12 SELECT * from DEPT;
13
14
15 END SAMPLE_FUNC;
16 END MY_PKG;
17 /

Package body created.

***@OCP>
***@OCP>
***@OCP>
***@OCP> variable rc1 refcursor;
***@OCP> variable rc2 refcursor;
***@OCP>
***@OCP> execute MY_PKG.SAMPLE_FUNC(:rc1,:rc2,'ALLEN');

PL/SQL procedure successfully completed.

***@OCP>
***@OCP> print rc1

EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO
---------- ---------- --------- ---------- --------- ----------
---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600
300 30

***@OCP>
***@OCP> print rc2

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

***@OCP>
***@OCP>

[Sybrand Bakker]

Post by s***@gmail.com
I call this package and function from .NET and it works fine, returning
the two result sets, but I would like to be able to test this in TOAD
DECLARE
test1 SYS_REFCURSOR;
test2 SYS_REFCURSOR;
BEGIN
exec MY_PKG.SAMPLE_FUNC(test1, test2, 'Smith');
print test1;
print test2;
END;
Obviously this syntax does not work. What do I need to do to make it
work?

Toad has a refcursor tab in the output window, at least in 8.5.x and
higher.

--
Sybrand Bakker, Senior Oracle DBA

[s***@gmail.com]

I have 8.6 installed. How do I open that tab? Is it dependent on having
Debugging installed? The "Debug" menu option is permanently grayed out
for me, which makes me think that I do not have the Debugging option
installed, so perhaps it is still not possible for me.

[Terry Dykstra]

PLSQLDeveloper has that ability as well.
--
Terry Dykstra
Canadian Forest Oil Ltd.

Post by Sybrand Bakker

Post by s***@gmail.com
I call this package and function from .NET and it works fine, returning
the two result sets, but I would like to be able to test this in TOAD
DECLARE
test1 SYS_REFCURSOR;
test2 SYS_REFCURSOR;
BEGIN
exec MY_PKG.SAMPLE_FUNC(test1, test2, 'Smith');
print test1;
print test2;
END;
Obviously this syntax does not work. What do I need to do to make it
work?

Toad has a refcursor tab in the output window, at least in 8.5.x and
higher.
--
Sybrand Bakker, Senior Oracle DBA