Re: How do I view SYS_REFCURSOR output parameters in TOAD?

seanbrasher_at_gmail.com a écrit :
> 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?
>

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 Received on Fri Mar 10 2006 - 09:33:13 CST