How do I view SYS_REFCURSOR output parameters in TOAD?

From: <seanbrasher_at_gmail.com>
Date: 10 Mar 2006 07:17:15 -0800
Message-ID: <1142003835.305386.71490@j52g2000cwj.googlegroups.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? Received on Fri Mar 10 2006 - 09:17:15 CST