Re: dynamic sql to return a result set

In article <afaaf1$hgj_at_dispatch.concentric.net>, "Luciano says...
>
>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 ';

   open result_set for s_sql;

is the way to do it. See
http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/appdev.817/a77069/10_dynam.htm#10961 for all the details (and don't forget to use BIND VARIABLES when you start adding predicates!)

>EXECUTE IMMEDIATE s_sql into RESULT_SET;
>END;
>
>it gives me: ORA-00932 inconsistent datatypes.
>--
>Luciano Belotto
>replace spam with traxsoftware for e-mail
>
>
>
>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp