RE: Packages passing REF CURSOR between procedures:

Thats what we thought.. 8) However, not valid in Pl/Sql:

PLS-00994: Cursor Variables cannot be declared as part of a package

Nice huh! 8)

Shawn M Ferris
Oracle DBA - Time Warner Telecom

> -----Original Message-----
> From: Thomas L. Harleman [mailto:tharleman_at_iquest.net]
> Sent: Thursday, June 01, 2000 10:29 AM
> To: ORACLE-L_at_fatcity.com
> Cc: Shawn.Ferris_at_twtelecom.com
> Subject: RE: Packages passing REF CURSOR between procedures:
>
>
> CREATE OR REPLACE PACKAGE test
> AS
>
> TYPE my_cursor IS REF CURSOR;
>
> csr_ref my_cursor; <--- YOU ARE MISSING THIS LINE.
>
> procedure parent(value number);
> procedure child1(ret_csr IN OUT my_cursor);
> procedure child2(ret_csr IN OUT my_cursor);
>
> END test;
> /
>
> Tom Harleman
> 11080 Willowmere Dr.
> Indianapolis, IN 46280
> 317-844-2884 Home
> 317-843-9122 Home Office
>
> ThinkFast CONSULTING, Inc. (formerly Pinnacle Solutions)
> Technical Consultant
> 3500 DePauw Blvd. Suite 2071
> Indianapolis, IN 46268
> tharleman_at_thinkfast.com
> 317-334-1317 Office
> 317-334-1301 Fax
>
>
>
> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Shawn
> Ferris
> Sent: Wednesday, May 31, 2000 1:40 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Packages passing REF CURSOR between procedures:
>
>
> I have a package. (sample code to follow) This package has several
> procedures.
>
> One is the parent procedure and calls the rest.
> The "child" procedures need to return a cursor reference to
> the top level
> procedure.
>
> When I try to compile this package it complains:
> PLS-00201: identifier 'RET_CSR' must be declared
>
> Now I've passed REF CURSORS via OCI, but never between
> procedures. What am I
> missing? Obviously I need to declare it but where and how?
>
> Oracle 8.1.5, Solaris 2.6+(?)
>
> TIA
> Shawn M Ferris
> Oracle DBA - Time Warner Telecom
>
> Sample code:
> CREATE OR REPLACE PACKAGE test
> AS
>
> TYPE my_cursor IS REF CURSOR;
>
> procedure parent(value number);
> procedure child1(ret_csr IN OUT my_cursor);
> procedure child2(ret_csr IN OUT my_cursor);
>
> END test;
> /
> CREATE OR REPLACE PACKAGE test
> AS
>
> TYPE my_cursor IS REF CURSOR;
>
> procedure parent(value number);
> procedure child1(ret_csr IN OUT my_cursor);
> procedure child2(ret_csr IN OUT my_cursor);
>
> END test;
> /
>
> CREATE OR REPLACE PACKAGE BODY test
> AS
>
> PROCEDURE parent (value number)
> IS
> BEGIN
> IF value = 1
> THEN
> child1(ret_csr);
> ELSE
> child2(ret_csr);
> END IF;
>
> FOR record IN ret_csr LOOP
> dbms_output.put_line('Procedure: '||record.proc);
> END LOOP;
>
> END parent;
>
> PROCEDURE child1(ret_csr IN OUT my_cursor)
> IS
> BEGIN
> OPEN ret_csr FOR
> select 'child1' proc from dual;
> END child1;
>
> PROCEDURE child2(ret_csr IN OUT my_cursor)
> IS
> BEGIN
> OPEN ret_csr FOR
> select 'child2' proc from dual;
> END child2;
>
> END test;
> /
> --
> Author: Shawn Ferris
> INET: Shawn.Ferris_at_twtelecom.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Thu Jun 01 2000 - 11:46:23 CDT