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.
