Oracle PL/SQL Bulk Collect With Save Exceptions Example

In my previous posts, I have given examples for Bulk Collect operations in Oracle. Here is an example of Bulk Collect with Save Exceptions to handle errors during bulk collect processing.

PL/SQL Bulk Collect With Save Exceptions Example

In the following PL/SQL program, it will update the HR schema EMPLOYEES table LAST_NAME column, and in two attempts it will try to update with a NULL value, which is not allowed for the LAST_NAME column due to not null constraint is applied. So, in this case, it will raise the error, and it will print on the screen, but the task will continue to update for other records because here we are using Save Exceptions clause with Bulk Collect.

SET SERVEROUTPUT ON

--Start the PL/SQL block--

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;
/

COMMIT
/

Output

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
PL/SQL procedure successfully completed.
Commit complete.

See also:

Vinish Kapoor
Vinish Kapoor

Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.