elmama
​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


Speeding up bulk data processing using FORALL and BULK COLLECT

In another of my articles I discussed the ideas of PL/SQL tables and how they could be used as an efficient means of dealing with bulk data. Things have moved on since I wrote that article and although all the concepts in it still hold true, a number of important enhancements have been made in this area that you ought to be taking advantage of. First of all PL/SQL tables are now called collections and more importantly there are two new ways of dealing with collections that can significantly speed up the processing of large amounts of data. The things I want to draw your attention to are BULK COLLECT and  FORALL clauses. To show how you how to use them I'll show you two ways in which you might use PL/SQL to populate a table with the contents of another table.The first way uses the old-fashioned way of doing it using a cursor and  FOR loop, and the second uses the new , and much faster, BULK COLLECT and FORALL techniques. 

Example 1 - The "old" way of bulk processing 

create or replace procedure pop_big_table_slowly
is begin    
for c1 in (select * from bigtable1)    
loop        
insert into bigtable2 (col1,col2, col3 etc ...)  
values(c1.col1,c1.col2,c1.col3 etc ...)    
end loop; 
end procedure pop_big_table_slowly; 


Example 2 - The "new" way of bulk processing 

create or replace procedure pop_big_table_quickly
is 
type bigtable1row is table of bigtable1%rowtype; 
table1row  bigtable1row; 
begin    

select * BULK COLLECT INTO table1row from bigtable1;    

FORALL c1 in table1row.First..table1row.Last        
insert into bigtable2  values( table1row(c1) ); 
end procedure pop_big_table_quickly; 


​There are two new implicit SQL cursor attributes that may be useful when coding FORALL statements. The first of these is the %BULK_ROWCOUNT. This is  rather like an index_by table and has as many members are there are in the collection you are processing. Each nth member holds the number of records processed by the nth INSERT, UPDATE or DELETE statement within the FORALL. The second useful attribute is %BULK_EXCEPTIONS which is populated when you use the SAVE EXCEPTIONS clause of the FORALL statement e.g FORALL index in low.. high SAVE EXCEPTIONS ... All exceptions raised during the FORALL are stored in %BULK_COLLECTIONS which is a collection of records. Each record has two fields. The first is %BULK_COLLECTIONS(i).ERROR_INDEX which holds the iteration of the FORALL statement where the error was raised. The other field is %BULK_COLLECTION(i).ERROR_CODE which holds the corresponding ORACLE error code. The total number of exceptions raised is kept in the %BULK_EXCEPTIONS.COUNT attribute. Note if you omit the SAVE EXCEPTIONS clause, the FORALL statements stops at the first exception raised and %BULK_COLLECTIONS.COUNT = 1 and  %BULK_COLLECTIONS contains just one record. The examples below show the use of the attributes described above. 

Example 3 - Using the %BULK_ROWCOUNT attribute 

​-- Give all employees in each department who earn less that
-- $15000 a 10 % raise 

declare type nums is table of NUMBER; 
depnos nums; 

begin    

​select dep_no BULK COLLECT INTO  depnos from departments;    

​FORALL  i in 1..depnos.count        
update employees  set sal = sal*1.10        
where dept = depnos(i) and sal <= 15000;    
for i in 1..depnos.count    
loop        
​ dbms_output.put_line(SQL%BULK_ROWCOUNT(i) ||
' salaries updated for department number ' ||
depnos(i));    
end loop;    

dbms_output.put_line('Total  salaries updated = ' ||
SQL%ROWCOUNT)); 
end; 

​Note that the FOARALL is NOT THE SAME as a FOR loop, so we can't just output the attribute values here. We need a separate proper FOR loop to do that. 


Example 4 - Using the %BULK_EXCEPTIONS attribute 

-- Deliberate divide by zero errors here. Print out any errors
--that occur 

declare 

type nums is table of NUMBER; 
depnos nums := (1,0,3,0); 

begin    

FORALL  i in depnos.First..depnos.Last        
​ insert into numtab(result)        
values(9/depnos(i)); 

exception when others then    

for i in 1..SQL%BULK_EXCEPTIONS.COUNT    
loop        
dbms_output.put_line('Error number ' || i || ' at iteration '
|| SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);        
dbms_output.put_line('Error message is ' ||
SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));    
end loop;    

​dbms_output.put_line('Total  salaries updated = ' || SQL%ROWCOUNT)); 

​end; 

The above produces the output:- 
Error number 1 at iteration 2 
Error message is ORA-1476:divisor is equal to zero 
Error number 2 at iteration 4 
​Error message is ORA-1476:divisor is equal to zero 



Example 5 - Corrrect way of using the LIMIT keyword



PROCEDURE process_all_rows
IS
  CURSOR table_with_227_rows_cur
  IS
  SELECT * FROM table_with_227_rows;
  TYPE table_with_227_rows_aat IS TABLE OF table_with_227_rows_cur%ROWTYPE INDEX BY PLS_INTEGER;
  l_table_with_227_rows table_with_227_rows_aat;
  BEGIN OPEN table_with_227_rows_cur;
  LOOP
  FETCH table_with_227_rows_cur BULK COLLECT INTO
  l_table_with_227_rows LIMIT 100;
  EXIT WHEN table_with_227_rows_cur.count=0;
  FOR indx IN 1 .. l_table_with_227_rows.COUNT
  LOOP
  analyze_compensation (l_table_with_227_rows(indx));
  END LOOP;
  END LOOP;
  CLOSE table_with_227_rows_cur;
 END process_all_rows;