ForAll - (PL/SQL) (DML On Large Number of Rows)

In my previous post i gave an insight on Bulk Collect. This post is the continuation of the Bulk Collect feauture of Oracle.

Using ForAll supercedes the performance by far when you need to perform a DML operations on large set of rows in a loop, its preffered to use a For All feature of Pl/sql Programming in such case as the performance will increase significantly. The FORALL statement is not a loop; it is a declarative statement to the PL/SQL engine: “Generate all the DML statements that would have been executed one row at a time, and send them all across to the SQL engine with one context switch.”

FORALL

DECLARE
   TYPE EmpCurTyp IS REF CURSOR;
   TYPE NumList IS TABLE OF EMPLOYEES%ROWTYPE;
   emp_cv EmpCurTyp;
   emp NumList;
 
BEGIN
  OPEN emp_cv FOR 'SELECT * FROM employees WHERE SALARY > 5000';
   FETCH emp_cv BULK COLLECT INTO emp;
   FORALL  i in 1..emp.count 
    UPDATE EMPLOYEES SET SALARY = SALARY+1000 WHERE SALARY = emp(i).salary;
    FOR i in 1 .. emp.count loop
      DBMS_OUTPUT.PUT_LINE(emp(i).FIRST_NAME || ' '|| emp(i).LAST_NAME|| ' ' || (emp(i).SALARY+1000)  );
 END LOOP;
 END;
 

SAVE EXCEPTIONS

Since version 9 it is possible to do bulk DML using FORALL and use the SAVE EXCEPTIONS clause. It makes sure that all invalid rows - the exceptions - are saved into the sql%bulk_exceptions array, while all valid rows are still processed. This array stores a record for each invalid row, containing an ERROR_INDEX which is the iteration number during the FORALL statement and an ERROR_CODE which corresponds with the Oracle error code.

 DECLARE
   --A local PL/SQL table holds the list of new names--
   TYPE T_EMP IS TABLE OF VARCHAR2 (100);

   L_EMP T_EMP
         := T_EMP ('Smith',
                   'Adams',
                   NULL,
                   'King',
                   NULL,
                   'George');
   BULK_ERRORS EXCEPTION;
   PRAGMA EXCEPTION_INIT (BULK_ERRORS, -24381);
BEGIN
   --FORALL to update the employee names--
   FORALL I IN 1 .. L_EMP.COUNT
   SAVE EXCEPTIONS
      UPDATE EMPLOYEES
         SET last_NAME = L_EMP (I);
EXCEPTION
   --BULK_ERRORS exception handler--
   WHEN BULK_ERRORS
   THEN
      --Display the errors occurred during BULK DML transaction--
      FOR J IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
      LOOP
         DBMS_OUTPUT.PUT_LINE (CHR (10));
         DBMS_OUTPUT.PUT_LINE (
            'Error in UPDATE: ' || SQL%BULK_EXCEPTIONS (J).ERROR_INDEX);
         DBMS_OUTPUT.PUT_LINE (
            'Error Code is: ' || SQL%BULK_EXCEPTIONS (J).ERROR_CODE);
         DBMS_OUTPUT.PUT_LINE('Error Message is: '
                              || SQLERRM('-'
                                         || SQL%BULK_EXCEPTIONS (J).ERROR_CODE));
      END LOOP;
END;
-------------------------------------------------------------------

Error in UPDATE: 3
Error Code is: 1407
Error Message is: ORA-01407: cannot update () to NULL


Error in UPDATE: 5
Error Code is: 1407
Error Message is: ORA-01407: cannot update () to NULL