Combine Cursor using Dynamic SQL and BULK COLLECT

From: C Chang <cschang_at_maxinter.net>
Date: Sat, 20 Jul 2002 00:47:53 -0400
Message-ID: <3D38EB79.229C@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 Received on Fri Jul 19 2002 - 23:47:53 CDT