Re: dynamic sql to return a result set

From: Daniel Morgan <dmorgan_at_exesolutions.com>
Date: Tue, 25 Jun 2002 21:54:21 GMT
Message-ID: <3D18E673.59135BB6@exesolutions.com>

Luciano Belotto wrote:

> I'd like to have a stored proc return a result set (via a parameter) using
> dynamic sql. Can someone help. This is what i have:
>
> CREATE OR REPLACE PACKAGE "ODB"."MYTYPES" as
> type cursorType is ref cursor;
> end;
>
> CREATE OR REPLACE PROCEDURE "ODB"."TRAX_CUST_RELEASE"
> (RESULT_SET in OUT ODB.myTypes.cursorType)
> is
> s_sql varchar2(100);
> BEGIN
> s_sql := ' select * from odb.trax_customer_release ';
> EXECUTE IMMEDIATE s_sql into RESULT_SET;
> END;
>
> it gives me: ORA-00932 inconsistent datatypes.
> --
> Luciano Belotto
> replace spam with traxsoftware for e-mail

s_sql makes no sense. How can you select multiple fields into a single VARCHAR2(100)? Try this demo:

CREATE OR REPLACE PACKAGE uw_type IS

TYPE t_ref_cursor IS REF CURSOR;

END;
/

--===========================================================

• create a child procedure to return a ref cursor

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 a parent procedure to call the child -- and display the records that are returned

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-1
   LOOP

      FETCH p_retcur
      INTO at_rec;

      DBMS_OUTPUT.PUT_LINE(at_rec.table_name);

   END LOOP;
END parent;
/

--===========================================================

SET SERVEROUTPUT ON exec parent(1)

I think this will show you how to do it.

Daniel Morgan Received on Tue Jun 25 2002 - 16:54:21 CDT