Re: Seeing results of stored procedure

From: DA Morgan <damorgan_at_exesolutions.com>
Date: Thu, 02 Jan 2003 16:25:31 -0800
Message-ID: <3E14D87B.E385AF76@exesolutions.com>

Harry Boswell wrote:

> I've written a stored procedure which uses a ref cursor as output. I want
> to be able to test the procedure and see the output in either SQL*Plus or
> TOAD. So I try to execute the procedure like this:
>
> SQL> variable facility_out_cur REFCURSOR;
> SQL> exec Get_Facility.Get_Facility_By_ID(2290, facility_out_cur);
>
> But I get:
>
> ERROR at line 1:
> ORA-06550: line 1, column 45:
> PLS-00201: identifier 'FACILITY_OUT_CUR' must be declared
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
>
> (FACILITY_OUT_CUR is the name of the output refcursor within the procedure).
>
> What am I doing wrong?
>
> Thanks,
> Harry Boswell

Execute your procedure within the context of an anonymous block. Such as the following demo I use with my students

CREATE OR REPLACE PACKAGE uw_type IS

TYPE t_ref_cursor IS REF CURSOR;

END;
/

---

CREATE OR REPLACE PROCEDURE child (

   p_NumRecs IN PLS_INTEGER,
   p_return_cur OUT uw_type.t_ref_cursor) IS

BEGIN
   OPEN p_return_cur FOR
   'SELECT * FROM all_tables WHERE rownum <= ' || p_NumRecs ; END child;
/

---

CREATE OR REPLACE PROCEDURE parent (

   pNumRecs VARCHAR2)
IS

p_retcur uw_type.t_ref_cursor;
at_rec all_tables%ROWTYPE;

   BEGIN
      child(pNumRecs, p_retcur);

      FOR i IN 1 .. pNumRecs
      LOOP
      FETCH p_retcur
      INTO at_rec;

      DBMS_OUTPUT.PUT_LINE(at_rec.table_name ||
      ' - ' || at_rec.tablespace_name ||
      ' - ' || TO_CHAR(at_rec.initial_extent) ||
      ' - ' || TO_CHAR(at_rec.next_extent));

   END LOOP;
END parent;
/

Dan Morgan Received on Thu Jan 02 2003 - 18:25:31 CST