Bulk Collect Examples

BULK COLLECT
============================================
This is used for array fetches
With this you can retrieve multiple rows of data with a single roundtrip.
This reduces the number of context switches between the pl/sql and sql engines.
Reduces the overhead of retrieving data.
You can use bulk collect in both dynamic and static sql.
You can use bulk collect in select, fetch into and returning into clauses.
SQL engine automatically initializes and extends the collections you reference in the bulk collect clause.
Bulk collect operation empties the collection referenced in the into clause before executing the query.
You can use the limit clause of bulk collect to restrict the no of rows retrieved.
You can fetch into multible collections with one column each.
Using the returning clause we can return data to the another collection

BULK COLLECT WITH EXAMPLE 1:---------
=======================================================
declare
   TYPE employees_aat
   IS TABLE OF emp%ROWTYPE
      INDEX BY PLS_INTEGER;
   l_employees employees_aat;
BEGIN
   SELECT *
   BULK COLLECT INTO l_employees
      FROM emp;
    
   FOR indx IN 1 .. l_employees.COUNT
   LOOP
       dbms_output.put_line(l_employees(indx).ename||'--->'||
                               l_employees(indx).sal||'--->'||
                              l_employees(indx).hiredate||'--->'||
                               l_employees(indx).deptno );
   END LOOP;
END;

BULK COLLECT WITH EXAMPLE 2:---------
=======================================================
CREATE OR REPLACE PROCEDURE process_all_rows (limit_in IN PLS_INTEGER DEFAULT 5)
IS
    CURSOR employees_cur
    IS
        SELECT * FROM emp;

    TYPE employees_aat IS TABLE OF employees_cur%ROWTYPE
        INDEX BY PLS_INTEGER;

    l_employees employees_aat;
BEGIN  

    OPEN employees_cur;
 
        FETCH employees_cur
            BULK COLLECT INTO l_employees LIMIT limit_in;
            CLOSE employees_cur;

        FOR indx IN 1 .. l_employees.COUNT
        LOOP
            DBMS_OUTPUT.PUT_LINE( l_employees(indx).ENAME);
        END LOOP;
END process_all_rows;

BEGIN
process_all_rows(5);
END;

BULK COLLECT WITH EXAMPLE 3:---------
=======================================================
DECLARE
    CURSOR employees_cur
    IS
        SELECT * FROM emp;

    TYPE employees_aat IS TABLE OF employees_cur%ROWTYPE
        INDEX BY PLS_INTEGER;

    l_employees employees_aat;
   
BEGIN  

    OPEN employees_cur;
 
        FETCH employees_cur
            BULK COLLECT INTO l_employees ;
           

            CLOSE employees_cur;

        FOR indx IN 1 .. l_employees.COUNT
        LOOP
            DBMS_OUTPUT.PUT_LINE( l_employees(indx).ENAME);
            EXIT WHEN
                      indx = 5;
        END LOOP;
  
END;

BULK COLLECT WITH EXAMPLE 4:---------
=======================================================
Bulk bind process
=====================================================
DECLARE
     TYPE array_ty is TABLE OF san%ROWTYPE
     INDEX BY BINARY_INTEGER ;

     data array_ty;

     errors   NUMBER ;

     l_cnt   NUMBER := 0;

     dml_errors EXCEPTION;

 PRAGMA EXCEPTION_INIT (dml_errors, -24381) ;

 CURSOR cur is SELECT * FROM san;

  BEGIN
      OPEN cur;
          LOOP
            FETCH c BULK COLLECT INTO data LIMIT 10; /*fetch for 10 records only */
            data.delete(2);
   BEGIN
      FORALL i IN 1..data.COUNT
     
         SAVE EXCEPTIONS

            INSERT INTO emp values data(i) ;
           
            commit;
           
   EXCEPTION
          WHEN dml_errors THEN

              errors := SQL%BULK_EXCEPTIONS.COUNT ;
               l_cnt := l_cnt + 1 ;
        FOR i IN 1..errors LOOP
           
            dbms_output.put_line('Error occurred during iteration ' ||
                      
                       SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
                         
                          '. Oracle error is ' ||
                         
                       SQL%BULK_EXCEPTIONS(i).ERROR_CODE  );
        END LOOP;
    END;
      EXIT WHEN cur%NOTFOUND ;
      END LOOP;
  CLOSE c;
dbms_output.put_line(l_cnt || ' Total Errors.');
 END ;

BULK COLLECT WITH EXAMPLE 5:---------
=======================================================

Table Creation
=======================================

CREATE TABLE bul_col_test1 AS
SELECT owner,
       object_name,
       object_id
FROM   all_objects;

Check the Table
===================================

select * from bul_col_test1---total records 3,93,940

Annoymous Block
===================================
DECLARE
  TYPE t_bulk_collect_test_tab IS TABLE OF bul_col_test1%ROWTYPE;

  l_tab    t_bulk_collect_test_tab := t_bulk_collect_test_tab();
 
  l_start  NUMBER;
  
BEGIN
  -- Time a regular population.
  l_start := DBMS_UTILITY.get_time;

  FOR cur_rec IN (SELECT *
                  FROM   bulk_collect_test)
  LOOP
    l_tab.extend;
    l_tab(l_tab.last) := cur_rec;
  END LOOP;

  DBMS_OUTPUT.put_line('Regular (' || l_tab.count || ' rows): ' ||
                                         (DBMS_UTILITY.get_time - l_start)||'secs');
 
  -- Time bulk population. 
  l_start := DBMS_UTILITY.get_time;

  SELECT *
  BULK COLLECT INTO l_tab
  FROM   bul_col_test1;

  DBMS_OUTPUT.put_line('Bulk    (' || l_tab.count || ' rows): ' ||
                       (DBMS_UTILITY.get_time - l_start)||'secs');
END;

Output
=========================================
Regular (393937 rows): 158secs
Bulk    (393940 rows): 56secs

BULK COLLECT WITH EXAMPLE 6:---------
=======================================================

create table xx_san(sno number,sname varchar2(20))

create table xx_san1(sno number,sname varchar2(20)) 

select * from xx_san

delete xx_san

select * from xx_san1

declare
type t1 is table of number index by pls_integer;
type t2 is table of varchar2(11) index by pls_integer;
type t3 is table of pls_integer index by pls_integer;
sno_tab t1;
sname   t2;
exc_ind  t3;
--define user define error msg
errors  exception;
pragma  exception_init(errors,-24381);
begin
for i in 1..10 loop
sno_tab(i) :=i;
sname(i):= 'yelamanda'||i ;
end loop;
--modify 1,5,7
sno_tab(1):= 11;
forall i in 1..10 save exceptions
insert into xx_san(sno,sname)
values(sno_tab(i),sname(i));
commit;
exception
when errors then
for i in 1..sql%bulk_exceptions.count
loop
exc_ind(i):= sql%bulk_exceptions(i).error_index;
end loop;
forall i in values of exc_ind
insert into xx_san1(sno,sname)
values(sno_tab(i),sname(i));
commit;
end;


one type of example
===============================
DECLARE
type gro_rec is RECORD(
                        empno emp.empno%type,
                        ename emp.ename%type,
                        job emp.job%type,
                        deptno emp.deptno%type
                        );

TYPE gro_tab IS TABLE OF g_rec index by binary_integer;
  var_rec gro_rec;
  var_tab gro_tab ;

cnt number;
cursor c is select empno,ename,job,deptno from emp;
BEGIN
   cnt:=0;
      for i in c loop           ///*****Assign record and then plsql table******///
           cnt:=cnt+1;
           var_rec.empno :=i.empno;
           var_rec.ename :=i.ename;
           var_rec.job :=i.job;
           var_rec.deptno :=i.deptno;
           var_tab(cnt):=var_rec;
      end loop;
  for i in var_tab.first..var_tab.last loop
    if mod(i,2) <> 0 then
       dbms_output.put_line(var_tab(i).ename||' ---> '||
                               var_tab(i).empno||' ---> '||
                               var_tab(i).job||' ---> '||
                               var_tab(i).deptno);
    end if;
  END LOOP;
END;

Second Type of Example
=================================
DECLARE
type g_rec is RECORD(
                        empno emp.empno%type,
                        ename emp.ename%type,
                        job emp.job%type,
                        deptno emp.deptno%type
                        );

TYPE g_tab IS TABLE OF g_rec index by binary_integer;

  var_tab g_tab ;

cnt number;
cursor c is select empno,ename,job,deptno from emp;
BEGIN
   cnt:=0;
      for i in c loop
     
           cnt:=cnt+1;  ////Assign Cursor to Plsql Table
         
           var_tab(cnt).empno  := i.empno;
           var_tab(cnt).ename  := i.ename;
           var_tab(cnt).job     := i.job;
           var_tab(cnt).deptno := i.deptno;
          
      end loop;
  for i in var_tab.first..var_tab.last loop
    if mod(i,2) <> 0 then
       dbms_output.put_line(var_tab(i).ename||' ---> '||
                               var_tab(i).empno||' ---> '||
                               var_tab(i).job||' ---> '||
                               var_tab(i).deptno);
    end if;
  END LOOP;
END;

Third Type of Example
=================================
DECLARE
type g_rec is RECORD(
                        empno emp.empno%type,
                        ename emp.ename%type,
                        job emp.job%type,
                        deptno emp.deptno%type
                        );

TYPE g_tab IS TABLE OF g_rec index by binary_integer;

  var_tab g_tab ;

cnt number;
cursor c is select empno,ename,job,deptno from emp;
BEGIN
   cnt:=0;
      for i in c loop
     
           cnt:=cnt+1;
         
           var_tab(cnt).empno := i.empno;
           var_tab(cnt).ename := i.ename;
           var_tab(cnt).job := i.job;
           var_tab(cnt).deptno := i.deptno;
          
      end loop;
  for i in var_tab.first..var_tab.last loop
    if mod(i,2) <> 0 then
       dbms_output.put_line(var_tab(i).ename||' ---> '||
                               var_tab(i).empno||' ---> '||
                               var_tab(i).job||' ---> '||
                               var_tab(i).deptno);
                              
            dbms_output.put_line(var_tab.count);
                              
                             
    end if;
      
  END LOOP;
             var_tab.delete(2);  
             --dbms_output.put_line(var_tab(2).ename);
            
         if  var_tab.exists(2) then
         dbms_output.put_line(var_tab(2).ename);
         else
         dbms_output.put_line('No Data Found');
         end if;
        
         dbms_output.put_line(var_tab.prior(4));
         dbms_output.put_line(var_tab.last);
         dbms_output.put_line(var_tab.count);
         --var_tab.trim(2);
          var_tab.delete(var_tab.last);
    dbms_output.put_line(var_tab.count);

END;

Forth Type of Example
=================================
declare
type number_array is varray(10000) of number;
type string_array is varray(10000) of varchar2(100);
 a_store string_array;
 a_qty number_array;
 cursor c1 is
        select empno, sum(sal) from emp
                                        group by empno;
 begin
   open c1;
    fetch c1 bulk collect into a_store, a_qty;
   close c1;
 for indx in a_store.first..a_store.last loop
 dbms_output.put_line(a_store(indx)||'....'||a_qty(indx));
 end loop;
 end;

Fifth Type of Example
=================================
 declare
 type sales_tab is table of emp%rowtype;
 t_sal sales_tab;
 begin
 select * bulk collect into t_sal from emp;
 dbms_output.put_line(t_sal.count);
 end;

Sixth Type Of Example
======================================
DECLARE
  TYPE t_tab IS TABLE OF emp%ROWTYPE;

  l_tab          t_tab := t_tab();
 
  l_error_count  NUMBER;
 
  ex_dml_errors EXCEPTION;
 
  PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);
BEGIN
  -- Fill the collection.
  FOR i IN 1 .. 100 LOOP
 
    l_tab.extend;
   
    l_tab(l_tab.last).empno := i;
   
  END LOOP;

  -- Cause a failure.
  l_tab(50).empno := NULL;
 
  l_tab(51).empno := NULL;

  EXECUTE IMMEDIATE 'TRUNCATE TABLE emp';

  -- Perform a bulk operation.
  BEGIN
    FORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONS
   
      INSERT INTO emp
      VALUES l_tab(i);
     
  EXCEPTION
 
    WHEN ex_dml_errors THEN
   
      l_error_count := SQL%BULK_EXCEPTIONS.count;
     
      DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
     
      FOR i IN 1 .. l_error_count LOOP
        DBMS_OUTPUT.put_line('Error: ' || i ||
          ' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
          ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
      END LOOP;
     
  END;
END;
/

select * from emp

insert into emp (select * from sand)



--------------------Example for plsql table collection type---------------------
declare
   type ecode is table of scott.EMP.empno%type
   index by binary_integer;
   type ename is table of scott.emp.ename%type
   index by binary_integer;
   type pay is table of scott.emp.sal%type
   index by binary_integer;
   e ecode;
   n ename;
   p pay;
   ctl number:=1;
 begin
   for i in (select empno,ename,sal
               from scott.emp)
  loop
      e(ctl):=i.empno;
      n(ctl):=i.ename;
      p(ctl):=i.sal;
    ctl:=ctl+1;
  end loop;
  for j in 1..n.count
  loop
    if p(j) between 0 and 1000
    then
    p(j) := p(j)+p(j)*0.15;
    elsif
    p(j) between 1001 and 2000 then
    p(j) :=p(j)+p(j)*0.25;
    else
    p(j) :=p(j)+p(j)*0.50;
    end if;
    dbms_output.put_line('the updated sal is'  ||'~' ||p(j));
    end loop;
    end;              


----------------------Nested pl/Sql record types---------------------------------------
declare
   type pf_info is record(  pfno   number(4)
                          , amount number(14,2));
   type emp_rec is record(  eid    number(4)
                          , name   varchar2(20)
                          , pf    pf_info);
   type etab is table of emp_rec index by binary_integer;
   ctl number(3):=0;
   e etab;
   v_ename varchar2(30);
begin
    for i in (select  empno
                    , ename
                    , sal basic
                    , sal*0.12 pamt
                from   scott.emp
                where sal>5000
               )
    loop
       e(ctl).eid:=i.empno;
       e(ctl).name:=i.ename;
       v_ename  := i.ename;
       dbms_output.put_line('the details are '||'  ---  '||v_ename);
       e(ctl).pf.pfno:=i.empno ;
       e(ctl).pf.amount:=i.pamt;
       ctl:=ctl+1;
    end loop;
   dbms_output.put_line('The count is :'||ctl);
    for j in 1..e.count
    loop
    null;
   end loop;
    end;              
                                        
   
    select *
      from scott.emp
     where sal>2000
    
    
--------------------------------------------pl/sql Ref cursor---------------------------------------
create or replace procedure xx_refcur(pdeptno in number)
    is
      i scott.emp%rowtype;
      type ref_cur is ref cursor;
      c1 ref_cur;
      begin
      if   pdeptno = 10
      then open c1 for select   empno
                              , ename
                              , sal
                         from   scott.emp
                        where deptno = pdeptno;
      elsif
          pdeptno = 20
       then open c1 for select   empno
                               , ename
                               , job
                          from  scott.emp
                         where  deptno = pdeptno;
     else
      open c1 for select        empno
                              , sal
                              , deptno
                        from   scott.emp
                       where deptno = pdeptno;
     end if;
     if pdeptno  = 10
     then
     loop
       fetch c1 into  i.empno
                    , i.ename
                    , i.sal;
       exit when c1%notfound;
       dbms_output.put_line('----------------------------');
       end loop;
       close c1;
      elsif
          pdeptno=20
      then
      loop
      fetch c1 into   i.empno
                    , i.ename
                    , i.job;
      exit when c1%notfound;
      dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
      end loop;
      close c1;
      end if;
      end;
     
     
     
      exec xx_refcur(20)