Re: REF CURSORS as procedure output parameters

Eric wrote:

> I have declared the my_cursor type inside the package header in order
> to be reachable outside the package. And in the packaged procedure
> call ( in SQL Worsheet) I have tried to declare a variable that will
> receive the cursor output parameter value .
> here is the call script:
>
> DECLARE
>
>
> TYPE my_newcursor IS REF CURSOR ;
> captive_cur my_newcursor;
>
> /* First try
> captive_cur TESTPACK.my_cursor;
> */
>
> BEGIN
> TESTPACK.SP_CAPTIVES_CURSOR('CO', captive_cur);
> dbms_output.put_line('Captive Name :'|| captive_cur.cap_nom);
> END;
>
>
> First I have declared this variable as TESTPACK.my_cursor type ( like
> defined in the package TESTPACK header) . And in a second try I
> declared the cursor variable as my_newcursor of type REF CURSOR.
> but when I try to use this cursor variable I get a error telling that
> : 'CAPTIVE_CUR' invalid reference.
>
> Here is the full package declaration
>
> CREATE OR REPLACE PACKAGE TESTPACK
> AS
> TYPE my_cursor IS REF CURSOR;
> PROCEDURE SP_CAPTIVES_CURSOR(in_Code IN f_captive.cap_code%type,
> out_captive_cur OUT my_cursor);
>
> FUNCTION GET_CAPTIVES_CURSOR(in_Code IN f_captive.cap_code%type)
> RETURN my_cursor;
>
> END Testpack;
>
> CREATE OR REPLACE PACKAGE BODY TESTPACK
> IS
>
> PROCEDURE SP_CAPTIVES_CURSOR(in_Code IN f_captive.cap_code%type,
> out_captive_cur OUT my_cursor)
> IS
> BEGIN
>
> OPEN out_captive_cur FOR
> select * from f_captive
> where cap_code like in_Code;
>
> END SP_CAPTIVES_CURSOR;
>
>
>
>
> FUNCTION GET_CAPTIVES_CURSOR(in_Code IN f_captive.cap_code%type)
> RETURN my_cursor
> IS
> out_captive_cur my_cursor;
>
> BEGIN
>
> OPEN out_captive_cur FOR
> select * from f_captive
> where cap_code like in_Code;
>
> RETURN out_captive_cur;
>
> END GET_CAPTIVES_CURSOR;
>
> END;
>
>
> The call of the procedure seems to work fine but when I try to display
> a field of the row returned by the procedure I get the error
> mentionned above.
> How can I get and use the procedure cursor in a script variable to
> display it in the calling script?
>
> thanks in advance
>
>
>
>
>
> "Andrew Carruthers" <andrewcarruthers_at_cri-uk.com> wrote in message news:<100fjlklfmmhe72_at_corp.supernews.com>...
>

>>Eric,
>>
>>I think your problem is the OUT cursor declaration
>>
>>PROCEDURE SP_CAPTIVES_CURSOR(in_Code IN f_captive.cap_code%type,
>>out_captive_cur OUT my_cursor)
>>
>>Where is the OUT parameter my_cursor declared?
>>
>>
>>
>>"Eric" <eric57e_at_hotmail.com> wrote in message
>>news:bc870bf3.0401160330.49312aa8_at_posting.google.com...
>>
>>>I'm new in the oracle world and I tried to call the procedure and the
>>>function in SQL Worksheet in the following way but I get an error that
>>>tells me that the cursor variable is of the wrong type.
>>>Could someone give me the right way to execute the procedure and get
>>>the out parameter, execute the function and get the result.
>>>Did I a mistake in the cursor definition that get the result. Or do i
>>>have only the solution to declare a cursor variable in the package.
>>>Thanks for any helpful response.
>>>
>>>Eric
>>>
>>>
>>>
>>>
>>>
>>>Here is the error message:
>>>
>>>ORA-06550: Ligne 7, colonne 53:
>>>PLS-00487: Référence de variable 'CAPTIVE_CUR' invalide
>>>ORA-06550: Ligne 7, colonne 1:
>>>PL/SQL: Statement ignored
>>>
>>>here is the code of the body of the package that i have created and
>>>the call to the procedure and function.
>>>
>>>
>>>/*
>>>SET SERVEROUTPUT ON
>>>SET ECHO OFF
>>>*/
>>>
>>>DECLARE
>>>TYPE my_newcursor IS REF CURSOR;
>>>captive_cur my_newcursor;
>>>
>>>BEGIN
>>>TESTPACK.SP_CAPTIVES_CURSOR('CO', captive_cur);
>>>captive_cur:=TESTPACK.SP_CAPTIVES_CURSOR('CO');
>>>dbms_output.put_line('Captive Name :'|| captive_cur.cap_nom);
>>>END;
>>>
>>>CREATE OR REPLACE PACKAGE TESTPACK
>>>
>>>IS
>>>
>>>PROCEDURE SP_CAPTIVES_CURSOR(in_Code IN f_captive.cap_code%type,
>>>out_captive_cur OUT my_cursor)
>>>IS
>>>BEGIN
>>>
>>>OPEN out_captive_cur FOR
>>>select * from f_captive
>>>where cap_code like in_Code;
>>>
>>>END SP_CAPTIVES_CURSOR;
>>>
>>>
>>>
>>>
>>>FUNCTION GET_CAPTIVES_CURSOR(in_Code IN f_captive.cap_code%type)
>>>RETURN my_cursor
>>>IS
>>>out_captive_cur my_cursor;
>>>
>>>BEGIN
>>>
>>>OPEN out_captive_cur FOR
>>>select * from f_captive
>>>where cap_code like in_Code;
>>>
>>>RETURN out_captive_cur;
>>>
>>>END GET_CAPTIVES_CURSOR;
>>>
>>>END;

You can not use dbms_output to display a REF CURSOR. The construct you need can be found at:

http://www.psoug.org/reference/ref_cursors.html

Ignore the unfinished part at the top and scroll to the bottom.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)