This content has been marked as final. Show 2 replies
User SUN@ wrote:Ouch.. you are using an old discontinued and unsupported version of Oracle. And you want to mix and match code between that version and 11g, using the latest dynamic SQL and bulk processing features of Oracle? Double ouch.
I am using oracle 8i and oracle 11g database.
Okay, +<insert preaching here about running unsupported s/w>+
To solve your problem, you will be restricted to the lowest common denominator - which will be whatever 8i supports in this regard. So grab the 8i PL/SQL Reference manual (you should find a copy over at http://tahiti.oracle.com) and see what is possible in that regard.
Example:-If 8i does not support dynamic bulk collection then it simply does not support dynamic bulk collection... have you looked at the 8i manual to see what is possible?
I want the bulk collect code to be dynamic.
declare type i_ref is ref cursor; itm_ref i_ref; type po_ln_rec is record ( c_itm_cd varchar2(20) ); type tt is table of po_ln_rec index by binary_integer; PO_LN_ITM_CD_COL tt; v_sql long := ' SELECT DISTINCT ITM_CD FROM PO_LN MINUS SELECT ITM_CD FROM ITM '; a number := 10; begin if a = 10 then execute immediate v_sql bulk collect into PO_LN_ITM_CD_COL ; if PO_LN_ITM_CD_COL.count > 0 then dbms_output.put_line('the are value s'); end if; end if; end;
Worse case - you cannot do a bulk collect in 8i which means that your code for 8i will then have to perform single cursor fetches. Also, as 8i does not support conditional PL/SQL parsing and compiling you will not be able to use #defines to create a single code unit that supports both versions.
In which case you may be forced to create 2 different versions of the code - one for 8i and one for 11g. What you can do to make this slightly less painful is to name these procedures differently. e.g. bulkProcess_11g and bulkProcess_8i and install both on both databases. Ignore the fact that the 11g version fails to compile on 8i. Then define a synonym called bulkProcess that is used to resolve the procedure reference on that database to the correct version of the procedure.