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.”
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;
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