RE: Packages passing REF CURSOR between procedures:

I need to use the cursor in parent as defined by one of the child processes. What I can't figure out is how to tell parent that ret_csr will one day be an actually cursor, not built in parent, but by one of the sub procedures..

This is the problem I'm having. How to declare ret_csr for use in parent but built elsewhere.

TIA 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 7:47 AM
> To: ORACLE-L_at_fatcity.com
> Cc: Shawn.Ferris_at_twtelecom.com
> Subject: RE: Packages passing REF CURSOR between procedures:
>
>
> Where did you get the variable "ret_csr" to pass to child1 and child2?
>
> > IF value = 1
> > THEN
> > child1(ret_csr);
> > ELSE
> > child2(ret_csr);
> > END IF;
>
>
> 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: RE: Packages passing REF CURSOR between procedures:
>
>
> DOH! Got a bit carried away on the cut and paste..
> (There should only be one package spec.)
>
> Again, any and all help will be greatly appreciated!
>
> Shawn M Ferris
> Oracle DBA - Time Warner Telecom
>
> > 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 - 09:25:32 CDT