Re: Combine Cursor using Dynamic SQL and BULK COLLECT

I think you need 9.2 to do this.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
 UK  Sept
 Australia August
 Malaysia September
 USA x 2  November

http://www.jlcomp.demon.co.uk/seminar.html

C Chang wrote in message <3D38EB79.229C_at_maxinter.net>...


>IS it possible to combine the REF curosr using the dynamic SQL with the

>BULK COLLECT INTO phrase?  I have tried several ways, it generated

>"invalid cursor" error at runtime.

>

>Why following syntax was wrong? Another way to use BULK COLLECT INTO

>

>  BEGIN

>        v_sqlstmt:= 'SELECT vendor_id,fsc||niin,';

>        v_sqlstmt:= v_sqlstmt || '

>vendor_list,vendor_lin,qty_period,contract_id';

> v_sqlstmt:= v_sqlstmt || ' BULK COLLECT INTO

>r_vendor_id,r_nsn,r_vendor_list,';

> v_sqlstmt:= v_sqlstmt || ' r_vendor_lin,r_qty_period,r_contract_id ';

> v_sqlstmt:= v_sqlstmt || ' FROM catalog_items ';

>

>   ..

>   EXECUTE IMMEDIATE v_sqlstmt;

>

>all the r_.. variables are TABLE TYPE of something and has been declared

>above BEGIN.

>at runtime it generated an error about the "FROM can not found .."

>

>C Chang