Handling BULK EXCEPTION

How BULK COLLECT is used with FORALL SAVE EXCEPTIONS? (Handling Exception with BULK Collect)

PL/SQL provides the different ways of handling exceptions. The Bulk Exceptions are used to save the exception information and continue processing.

All exceptions raised during execution are saved in %BULK_EXCEPTION attribute. It also stores a collection of records similar to BULK COLLECT.

•    %BULK_EXCEPTIONS(i).ERROR_CODE holds the corresponding Oracle error code.
•    %BULK_EXCEPTIONS(i).ERROR_INDEX holds the iteration number of the FORALL statement.
•    %BULK_EXCEPTIONS.COUNT holds total number of exceptions encountered.

In order to bulk collect exceptions, we have to use FORALL clause with SAVE EXCEPTIONS keyword.

•       Connect to SQL*Plus with proper credentials and run following query to create the table and populate it with some data.
CREATE TABLE TEST
(
Test_ID     NUMBER(9) NOT NULL PRIMARY KEY,
Test_Desc   VARCHAR(15),
New_Desc    VARCHAR(15)
)

SQL> DESC TEST;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TEST_ID                                   NOT NULL NUMBER(9)
 TEST_DESC                                          VARCHAR2(15)
 NEW_DESC                                           VARCHAR2(15)

SQL> SELECT * FROM TEST;

   TEST_ID TEST_DESC       NEW_DESC
---------- --------------- ---------------
       101 Selecting
       102 Inserting Data
       103 Delete Records
       104 Trigger Records
       105 Create View

Run following PL/SQL block to populate the table and later on update it to see the exception behaviour.

DECLARE

TYPE Test_ID   IS TABLE OF TEST.TEST_Id%TYPE;

TYPE Test_Desc IS TABLE OF TEST.TEST_DESC%TYPE;

v_Test_ID    Test_ID;

v_Test_Desc  Test_Desc;

v_Err_count   NUMBER;

BEGIN

SELECT Test_ID, Test_Desc

BULK COLLECT INTO v_Test_ID, v_Test_Desc

FROM Test;

FORALL i IN v_test_ID.FIRST..v_test_ID.LAST SAVE EXCEPTIONS

UPDATE TEST

SET NEW_DESC = TO_CHAR(TEST_ID)||TEST_DESC

WHERE TEST_ID = v_test_ID(i);

Commit;

EXCEPTION

WHEN OTHERS THEN

v_Err_Count := SQL%BULK_EXCEPTIONS.COUNT;

DBMS_OUTPUT.PUT_LINE('Number of statements that failed:  ' || v_Err_Count);

FOR i IN 1..v_Err_Count

LOOP

DBMS_OUTPUT.PUT_LINE('Error #' || i || ' occurred during '||'Iteration #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);

DBMS_OUTPUT.PUT_LINE('Error message is ' ||

SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));

END LOOP;

END;

If you execute above PL/SQL block, it will display following information based on Data inserted into your table.

SQLSQL> DECLARE
  2  TYPE Test_ID   IS TABLE OF TEST.TEST_Id%TYPE;
  3  TYPE Test_Desc IS TABLE OF TEST.TEST_DESC%TYPE;
  4  v_Test_ID    Test_ID;
  5  v_Test_Desc  Test_Desc;
  6  v_Err_count   NUMBER;
  7
  8  BEGIN
  9  SELECT Test_ID, Test_Desc
 10  BULK COLLECT INTO v_Test_ID, v_Test_Desc FROM Test;
 11  FORALL i IN v_test_ID.FIRST..v_test_ID.LAST SAVE EXCEPTIONS
 12  UPDATE TEST
 13  SET NEW_DESC = TO_CHAR(TEST_ID)||TEST_DESC
 14  WHERE TEST_ID = v_test_ID(i);
 15  Commit;
 16
 17  EXCEPTION
 18  WHEN OTHERS THEN
 19  v_Err_Count := SQL%BULK_EXCEPTIONS.COUNT;
 20  DBMS_OUTPUT.PUT_LINE('Number of statements that failed:  ' || v_Err_Count);
 21
 22  FOR i IN 1..v_Err_Count
 23  LOOP
 24  DBMS_OUTPUT.PUT_LINE('Error #' || i || ' occurred during '||'Iteration #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
 25  DBMS_OUTPUT.PUT_LINE('Error message is ' ||
 26  SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
 27  END LOOP;
 28  END;
 29  /
Number of statements that failed:  3
Error #1 occurred during Iteration #2
Error message is ORA-12899: value too large for column  (actual: , maximum: )
Error #2 occurred during Iteration #3
Error message is ORA-12899: value too large for column  (actual: , maximum: )
Error #3 occurred during Iteration #4
Error message is ORA-12899: value too large for column  (actual: , maximum: )

PL/SQL procedure successfully completed.

Check the Table Data-

SQL> SELECT * FROM TEST;

   TEST_ID TEST_DESC       NEW_DESC
---------- --------------- ---------------
       101 Selecting       101Selecting
       102 Inserting Data
       103 Delete Records
       104 Trigger Records
       105 Create View     105Create View

It's RUDE to Read and Run!

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.