Message-Id: <10515.107392@fatcity.com>
From: Shawn Ferris <Shawn.Ferris@twtelecom.com>
Date: Thu, 1 Jun 2000 08:25:32 -0600 
Subject: 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@iquest.net]
> Sent: Thursday, June 01, 2000 7:47 AM
> To: ORACLE-L@fatcity.com
> Cc: Shawn.Ferris@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@thinkfast.com 
> 317-334-1317 Office 
> 317-334-1301 Fax 
> 
> 
> 
> -----Original Message-----
> From: root@fatcity.com [mailto:root@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@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@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