Re: REF CURSORS as procedure output parameters

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;
Received on Fri Jan 16 2004 - 05:49:14 CST