Oracle PLSQL : Bulk Collect

 Oracle PLSQL : Bulk Collect Example

declare

Cursor cur_gl_stg is

Select  je_header_id,name,description from gl_je_headers where trunc(creation_date) >= '01-APR-2023';

 

TYPE cur_gl_stg_type IS TABLE OF cur_gl_stg%rowtype INDEX BY PLS_INTEGER;

v_gl_stg_type    cur_gl_stg_type;

l_index_cnt Number:= 0;

 

Begin

 OPEN cur_gl_stg; ---opening cursor

 loop --loop start

   l_index_cnt := l_index_cnt+1;

   FETCH cur_gl_stg

   BULK COLLECT INTO v_gl_stg_type LIMIT 30;

   EXIT WHEN v_gl_stg_type.COUNT = 0;

   dbms_output.put_line('l_index_cnt:'||l_index_cnt||','||v_gl_stg_type.count);

  

    FORALL indx IN 1 .. v_gl_stg_type.COUNT

        UPDATE gl_je_headers

      Set description = description||'BulkCollect'

      where je_header_id =v_gl_stg_type(indx).je_header_id ;

   

       dbms_output.put_line('Rowcount:'||SQL%ROWCOUNT||','||v_gl_stg_type.count);

     

 end loop;

 

    dbms_output.put_line('Total count:'||v_gl_stg_type.count);

 /*for i in 1..v_gl_stg_type.count loop

  dbms_output.put_line('Name :'||v_gl_stg_type(i).name);

 end loop;*/

 

exception when others then

   dbms_output.put_line('Error main :'||SQLERRM);

null;

End;