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