REF CURSORS as procedure output parameters

From: Eric <eric57e_at_hotmail.com>
Date: 16 Jan 2004 03:30:48 -0800
Message-ID: <bc870bf3.0401160330.49312aa8@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:30:48 CST