Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: REF CURSORS as procedure output parameters

Re: REF CURSORS as procedure output parameters

From: Eric <eric57e_at_hotmail.com>
Date: 20 Jan 2004 00:39:56 -0800
Message-ID: <bc870bf3.0401200039.7b3ac89c@posting.google.com>


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;
Received on Tue Jan 20 2004 - 02:39:56 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US