Working with FORALL (BULK DML) and Understanding SAVE EXCEPTIONS

FORALL:
We will use FORALL statement , when we want do DML (insert,update,delete) operation with BULK COLLECT.

Its much faster than normal equivalent FOR -LOOP. 

PLSQL Guide tells as below:

• FORALL statement runs one DML statement multiple times, with different values in the VALUES and WHERE clauses.

• The different values come from existing, populated collections or host arrays. The FORALL statement is usually much faster than an equivalent FOR LOOP statement.

So if you insert N rows using a row-by-row FOR LOOP, it’ll execute the INSERT statement N times. If you go for bulk DML with FORALL instead, it’ll bind the same INSERT statement to N values. This reduces the overhead of context switching between SQL and PL/SQL.

Lets start with one scenario to understand it in practically.

we are taking below tables:

CREATE TABLE IDENTITY_DET(PAN VARCHAR2(10) PRIMARY KEY, NAME VARCHAR2(100),NATIONALITY VARCHAR2(100) DEFAULT 'INDIA');

CREATE TABLE PEOPLE_DET(PAN VARCHAR2(10), NAME VARCHAR2(100),WORK_STATUS VARCHAR2(1) DEFAULT 'Y');

SQL> SELECT * FROM PEOPLE_Det;

PAN              NAME          WORK_STATUS

----------------- ------------- -----------

AMZPV1234F        VENKY         Y

AMZPV1234F        VENKY         Y

APZPV1234F        CHINNI        N

BPZPV1234F        SATISH        N

CPZPV1234F        NIKITH        Y

CPZPV1234F        NIKITH        Y

DPZPV1234F        RAJU           Y

6 rows selected

SQL> select * from Identity_Det;

PAN   NAME     NATIONALITY

------- ----  --------------

"PEOPLE_Det" having data with duplicate PAN VALUES and "PAN" doesn't have unique constraint.

we are going to load data from people_det to identity_det table but here "PAN" column having constraint so it will not allow duplicate and it will throw error.

so we need to handle error also in this BULK DML operation with FORALL.

DECLARE
  TYPE TY_TB_V_PEOPLE_DET IS TABLE OF PEOPLE_DET%ROWTYPE;
  V_TY_TB_V_PEOPLE_DET TY_TB_V_PEOPLE_DET;
  l_error_count        NUMBER;
  IDX                  INTEGER := 0;
  CURSOR CUR_People_Det IS
    SELECT * FROM People_Det;
  bulk_dml_exp EXCEPTION;
  PRAGMA EXCEPTION_INIT(bulk_dml_exp, -24381);
BEGIN
  --Initializing Nested table
  V_TY_TB_V_PEOPLE_DET := TY_TB_V_PEOPLE_DET();
  FOR K IN CUR_People_Det LOOP
    --Allocating space Using Extend Method In loop so it will allocate until loop executes
    V_TY_TB_V_PEOPLE_DET.EXTEND;
    IDX := IDX + 1;
    V_TY_TB_V_PEOPLE_DET(IDX).PAN := K.PAN;
    V_TY_TB_V_PEOPLE_DET(IDX).NAME := K.NAME;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(' Count ' || V_TY_TB_V_PEOPLE_DET.COUNT);

  --Start Bulk DML Operation and handling exceptions using "Save Exceptions"
  BEGIN
    DBMS_OUTPUT.PUT_LINE(' Count before Forall ' || V_TY_TB_V_PEOPLE_DET.COUNT);
    FORALL MY_REC IN 1 .. V_TY_TB_V_PEOPLE_DET.COUNT SAVE EXCEPTIONS
    --Inserting data in bulk using FORALL
    
      INSERT INTO Identity_Det VALUES V_TY_TB_V_PEOPLE_DET (MY_REC);
  EXCEPTION
    WHEN bulk_dml_exp THEN
      l_error_count := SQL%BULK_EXCEPTIONS.COUNT;
      DBMS_OUTPUT.PUT_LINE('Error Count ' || l_error_count);
      for j in 1 .. l_error_count loop
        DBMS_OUTPUT.PUT_LINE('Error Count ' || l_error_count);
        dbms_output.put_line('Error ' || j || ' at Index ' ||
                             SQL%BULK_EXCEPTIONS(j)
                             .ERROR_INDEX || ' ERR MSG ' ||
                             SQLERRM(-SQL%BULK_EXCEPTIONS(J).ERROR_CODE));
      END LOOP;
  END;
EXCEPTION
  WHEN OTHERS THEN
  
    DBMS_OUTPUT.PUT_LINE('Error MSG ' || SQLERRM || ' LINE ' ||
                         DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;

Output:

 Count 7
 Count before Forall 7
Error Count 2
Error Count 2
Error 1 at Index 2 ERR MSG ORA-00001: unique constraint (.) violated
Error Count 2
Error 2 at Index 6 ERR MSG ORA-00001: unique constraint (.) violated

PL/SQL procedure successfully completed



Records got inserted and at the same we are handling errors also with SAVE EXCEPTION clause.

Table output:

SQL> select * from Identity_Det;

PAN                           NAME      NATIONALITY
----------------------- --------- -----------
AMZPV1234F              VENKY     
APZPV1234F              CHINNI    
BPZPV1234F              SATISH    
CPZPV1234F              NIKITH    
DPZPV1234F              RAJU 

Whats SAVE EXCEPTIONS

If any error occurs during Bulk DML, the SAVE EXCEPTIONS clause instructs the PL/SQL engine to store this information in a pseudo collection SQL%BULK_EXCEPTIONS and proceed with the next value in the collection.

After iterating through the whole collection, error ORA-24381 is raised in case any errors in the bulk DML.

ORA-24381: error(s) in array DML

ORA-24381 can be trapped in an EXCEPTION handler, and the contents of SQL%BULK_EXCEPTIONS can be extracted in a FOR LOOP.

Each value in the pseudo collection can be referenced using:

SQL%BULK_EXCEPTIONS(i).ERROR_INDEX: the iteration number in the FORALL statement during which the exception was raised.


SQL%BULK_EXCEPTIONS(i).ERROR_CODE: the Oracle error code of the exception.