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.

​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

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