2 Replies Latest reply: Jul 14, 2009 1:12 PM by Hoek RSS

    bulk collect in the dynamic sql

    RKP
      Hello All,

      I am using oracle 8i and oracle 11g database.

      I have to modify one SQL file which will be run in the oracle 8i and 11g database. The file has been created in the 11g database. In the file it is using the bulk collect in the cursors to a collection which is like a record type. Oracle 8i will not support the bulk collect into statement to a record type so it is throughing the compilation error.

      Can I make the sql string as a dynamic sql so that it will not through the compilation error. I will pass one parameter to the script and it will execute the code according to the parameter.

      Example:-

      <code>
      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;
      </code>

      I want the bulk collect code to be dynamic.

      Thanks,
      SUN
        • 1. Re: bulk collect in the dynamic sql
          Billy~Verreynne
          User SUN@ wrote:
          I am using oracle 8i and oracle 11g database.
          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.

          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:-
          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; 
          I want the bulk collect code to be dynamic.
          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?

          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.
          • 2. Re: bulk collect in the dynamic sql
            Hoek
            I've noticed some very interesting points/events regarding this thread, so I've learned something here and other doubts have now become clear, thanks ;) .