Re: result cursor using dynamic sql

In article <9gr4tn$t47_at_dispatch.concentric.net>, "Jim says...
>
>Oracle 8.1.7. The calling program is a Delphi application. I currently use
>a number of procedures returning cursors to this application. In those
>procedures, the cursors are created using static sql.
>
>Thanks
>
>
>--
>Jim Poe (jpoe_at_fulcrumit.com)
>
>
>"Daniel A. Morgan" <Daniel.Morgan_at_attws.com> wrote in message
>news:3B30EFC0.637727A8_at_attws.com...
>> Jim Poe wrote:
>>
>> > I need to create a procedure that uses DBMS_SQL to generate a dynamic
 select
>> > cursor. How can I return the results of this select as a cursor
 parameter
>> > in the procedure call?
>> >
>> > procedure myproc( p_tabname in varchar2, p_dscr in varchar2,
>> > c_result_cursor in out cursor_types.t_c_clear_insd )
>> > begin
>> > vCursor := dbms_sql.open_cursor;
>> > vTabname := upper( p_tabname );
>> > vStmt := 'select ' || vTabName || '_id from ' || vTabName ||
>> > ' where upper( dscr )=upper(''' || p_dscr || ''')';
>> >
>> > dbms_output.put_line ( vStmt );
>> > dbms_sql.parse(vCursor, vStmt, dbms_sql.v7);
>> > vDummy := dbms_sql.execute( vCursor );
>> >
>> > // What now? How do I get the results of vCursor into
>c_result_cursor?

then code simply:

   open c_result_cursor for

     'select ' vTabname || '_id from ' || vTabName || 
           ' where upper(dscr) = upper( :x ) ' using p_dscr;

PLEASE use bind variables in your code -- it is soooo important.

>> >
>> > Thanks
>> >
>> > --
>> > Jim Poe (jpoe_at_fulcrumit.com)
>>
>> What version of Oracle are you using? And what are you returning it to?
>>
>> Daniel A. Morgan
>>
>
>

--
Thomas Kyte (tkyte@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