Re: result cursor using dynamic sql

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?


> >

> > Thanks

> >

> > --

> > Jim Poe (jpoe_at_fulcrumit.com)

>

> What version of Oracle are you using? And what are you returning it to?

>

> Daniel A. Morgan

>