Re: Dynamic SQL with DBMS_SQL cursor

In article <Xns91967E1AA2F8Edavidecavalleriliber_at_151.99.250.3>, Deivnet says...
>
>hi,
> i need to know how to assign to a cursor variable a multi row query
>result created by DBMS_SQL dynamic SQL.
>My code :
>
>
> cur := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(cur, sqlString, DBMS_SQL.NATIVE);
> rc := DBMS_SQL.EXECUTE(cur);
> DBMS_SQL.CLOSE_CURSOR(cur);
>
>cur and rc are Integer Variable, sqlString is the sql query i need to
>assign result to a REF CURSOR variable type.
>
>Thanks
>

you EITHER use dbms_sql and procedurally process the results or you use native dynamic sql.

Prior to Oracle8i (sigh, never ever a version in sight....), dynamically opening a ref cursor was physically and totally impossible. In 8i and up you can simply:

begin

   open ref_cursor_variable for plsql_variable; end;

where plsql_variable is some string that contains the query you want to execute. See
http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/appdev.817/a77069/10_dynam.htm#4376

for details and don't forget to use BIND VARIABLES!!

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