Re: Can a stored procedure return a record set with a dynamic SQL cursor

In article <70040f22.0107110747.238cb939_at_posting.google.com>, sarojini_at_multimodalinc.com says...
>
>I am using Oracle 8i.
>
>I define my reference cursor in the package
>
>PACKAGE TRF AS
> TYPE TRF_REC IS RECORD
> ( TRF_CAT_I NUMBER,
> TRF_CAT_CODE VARCHAR2(50),
> );
> TYPE CUR_TRF IS REF CURSOR RETURN TRF_REC;
>END TRF;
>
>I then define my stored procedure as
>PROCEDURE SP_TRAFFIC
> (p_CurTrfVar IN OUT TRF.CUR_TRF,
> WHERECLAUSE IN VARCHAR2 := NULL)
>AS
>BEGIN
> OPEN p_CurTrfVar FOR
> 'SELECT TRF_CAT_I, TRF_CAT_C
> FROM TRAFFIC
> WHERE' || NVL(WHERECLAUSE, '1=1');
>
>END SP_TRAFFIC;
>
>The stored procedure works fine with a static SQL statement, but the
>moment I turn it into a dynamic SQL statement, it complains that the
>cursor variable cannot be defined with an Open or dynamic SQL
>statement.
>I need the cursor as a IN OUT parameter to feed another program -
>Crystal reports.
>Is there a solution to this or a workaround ?
>Thanks for the help.
>
>Sarojini

You can only use WEAKLY typed cursors for dynamically opened ref cursors

use:

....
  type cur_trf is ref cursor;
.....

--
Thomas Kyte (tkyte_at_us.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