What is FORALL?

This instruction allows computing all the rows of a collection in a single pass. FORALL cannot be use on the client-side and can precede one and only one statement at a time.

If an error is raised by the FORALL statement, all the rows processed are rolled back. You can save the rows that raised an error (and do not abort the process) with the SAVE EXCEPTION keyword. Every exception raised during execution is stored in the BULK_EXCEPTIONS collection. This is a collection of records composed by two attributes:

FORALL index IN min_index .. max_index [SAVE EXCEPTION] sql_order

%BULK_EXCEPTIONS(n).ERROR_INDEX which contains the index number

%BULK_EXCEPTIONS(n).ERROR_CODE which contains the error code .

i.e.

SQL> Declare

  2    TYPE   TYP_TAB_TEST IS TABLE OF TEST%ROWTYPE;

  3    tabrec TYP_TAB_TEST;

  4   

CURSOR C_test is select A, B From TEST;

  5  Begin

  6     -- Load the collection from the table --

  7     Select A, B BULK COLLECT into tabrec From TEST;

  8    

  9     -- Insert into the table from the collection --

 10     Forall i in tabrec.first..tabrec.last

 11         Insert into TEST values tabrec(i);

 12     

 13     -- Update the table from the collection --

 14     For i in tabrec.first..tabrec.last Loop

 15         tabrec(i).B := tabrec(i).B * 2;   

 16     End loop ;

 17    

 18     -- Use of cursor --

 19     Open  C_test ;

 20     Fetch C_test BULK COLLECT Into tabrec;

 21     Close C_test ;

 22        

 23  End ;

 24  /

Implementation restriction

It is not allowed to use the FORALL statement and an UPDATE order that use the SET ROW functionality

SQL> Declare

  2     TYPE    TAB_EMP is table of EMP%ROWTYPE;

  3     emp_tab TAB_EMP;

  4     Cursor CEMP is Select * From EMP;

  5  Begin  

  6     Open CEMP;

  7     Fetch CEMP BULK COLLECT Into emp_tab;

  8     Close CEMP;

  9    

 10    Forall i in emp_tab.first..emp_tab.last

 11      Update EMP set row = emp_tab(i) where EMPNO = emp_tab(i).EMPNO ; -- ILLEGAL

 12     

 13  End ;

 14  /

    Update EMP set row = emp_tab(i) where EMPNO = emp_tab(i).EMPNO ; -- ILLEGAL

                                                   *

ERROR at line 11:

ORA-06550: line 11, column 52:

PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND

table of records

You have to use a standard FOR LOOP statement:

For i in emp_tab.first..emp_tab.last loop

   Update EMP set row = emp_tab(i) where EMPNO = emp_tab(i).EMPNO ;

End loop;

Or use simple collections:

Declare

   TYPE    TAB_EMPNO   is table of EMP.EMPNO%TYPE;

   TYPE    TAB_EMPNAME is table of EMP.ENAME%TYPE;

   no_tab  TAB_EMPNO ;

   na_tab  TAB_EMPNAME;  

   Cursor  CEMP is Select EMPNO, ENAME From EMP;

Begin  

   Open CEMP;

   Fetch CEMP BULK COLLECT Into no_tab, na_tab ;

   Close CEMP;

  

  Forall i in no_tab.first..no_tab.last

    Update EMP set ENAME = na_tab(i) where EMPNO = no_tab(i) ;   

End; 

Exceptions:

If an error is raised by the FORALL statement, all the rows processed are rolled back. You can save the rows that raised an error (and do not abort the process) with the SAVE EXCEPTION keyword.

Every exception raised during execution is stored in the %BULK_EXCEPTIONS collection. This is a collection of records composed by two attributes:

%BULK_EXCEPTIONS(n).ERROR_INDEX which contains the index number

%BULK_EXCEPTIONS(n).ERROR_CODE which contains the error code

The total amount of errors raised by the FORALL instruction is stored in the SQL%BULK_EXCEPTIONS.COUNT attribute.

SQL> Declare

  2    TYPE    TYP_TAB IS TABLE OF Number;

  3    tab     TYP_TAB := TYP_TAB( 2, 0, 1, 3, 0, 4, 5 ) ;  

  4    nb_err  Pls_integer ;  

  5  Begin

  6     Forall i in tab.first..tab.last SAVE EXCEPTIONS

  7         Delete from EMP where SAL = 5 / tab(i) ;

  8  Exception

  9    When others then    

 10       nb_err := SQL%BULK_EXCEPTIONS.COUNT ;

 11       dbms_output.put_line( to_char( nb_err ) || ' Errors ' ) ;

 12       For i in 1..nb_err Loop   

 13          dbms_output.put_line( 'Index ' || to_char( SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ) || ' Er

ror :  ' 

 14   || to_char( SQL%BULK_EXCEPTIONS(i).ERROR_CODE ) ) ;

 15       End loop ;     

 16  End ; 

 17  /

2 Errors

Index 2 Error :  1476

Index 5 Error :  1476

PL/SQL procedure successfully completed. 

The %BULK_ROWCOUNT attribute.

This is an INDEX-BY table that contains for each SQL order the number of rows processed.

If no row is impacted, SQL%BULK_ROWCOUNT(n) equals 0.

SQL> Declare

  2    TYPE   TYP_TAB_TEST IS TABLE OF TEST%ROWTYPE ;

  3    TYPE   TYP_TAB_A IS TABLE OF TEST.A%TYPE ;

  4    TYPE   TYP_TAB_B IS TABLE OF TEST.B%TYPE ;    

  5    tabrec TYP_TAB_TEST ;

  6    taba   TYP_TAB_A ;

  7    tabb   TYP_TAB_B ;

  8    total  Pls_integer := 0 ;  

  9    CURSOR C_test is select A, B From TEST;

 10  begin

 11     -- Load the collection from the table --

 12     Select A, B BULK COLLECT into tabrec From TEST ;

 13  

 14     -- Insert rows --

 15     Forall i in tabrec.first..tabrec.last

 16         insert into TEST values tabrec(i) ;

 17      

 18     For i in tabrec.first..tabrec.last Loop

 19         total := total + SQL%BULK_ROWCOUNT(i) ;

 20     End loop ;

 21      

 22     dbms_output.put_line('Total insert : ' || to_char( total) ) ;

 23          

 24     total := 0 ;

 25     -- Upadate rows --

 26     For i in tabrec.first..tabrec.last loop

 27       update TEST set row =  tabrec(i) where A = tabrec(i).A ;

 28     End loop ;

 29     

 30     For i in tabrec.first..tabrec.last Loop

 31         total := total + SQL%BULK_ROWCOUNT(i) ;

 32     End loop ;

 33      

 34     dbms_output.put_line('Total upfdate : ' || to_char( total) ) ; 

 35      

 36  End ;

 37  /

Total insert: 20

Total upfdate: 20

PL/SQL procedure successfully completed.