Bulk Collect Exceptions

Bulk Collect and Multiple Exceptions:

In one of our previous post, we had covered the scenario of getting a no data found exception when using Bulk Collect in PL/SQL.    In this post, we will look into how to handle multiple exceptions when using Bulk Collect.  BULK COLLECT construct is used to work with batches of data rather than single record at a time. Whenever we have to deal with large amount of data, bulk collect provides considerable performance improvement.

‘Bulk collect..into’ clause improve performance of the select statements. To loop through records collected using bulk collect we can use FORALL syntax. FORALL clause works with DML statements in batches and much faster than the regular for loop construct. In this blog post, we are going to talk about FORALL exceptions with the %BULK_EXCEPTIONS attribute. This attribute allows us to continue with the process, even if run into any DML exception for some record in between. Basically this mechanism allows us to complete the process without stopping if any error occurs.

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

•    %BULK_EXCEPTIONS(i).ERROR_CODE holds the corresponding Oracle error code.
•    %BULK_EXCEPTIONS(i).ERROR_INDEX holds the iteration number of the FORALL statement for which the exception was raised.
•    %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. Let us see with the example.

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(30),
New_Desc    VARCHAR(30)
)
/
SQL> INSERT INTO TEST(TEST_ID,TEST_DESC)
2  SELECT ROWNUM, TABLE_NAME
3    FROM USER_TABLES;

9 rows created.

Run following PL/SQL block to populate the table and later on update it to see the exception behavior. We have created this PL/SQL block based on the example shown in Oracle manual.

DECLARE

TYPE ga_Test_ID   IS TABLE OF TEST.TEST_Id%TYPE;
TYPE ga_Test_Desc IS TABLE OF TEST.TEST_DESC%TYPE;
va_Test_ID    ga_Test_ID;
va_Test_Desc  ga_Test_Desc;
V_Err_count   NUMBER;

BEGIN

SELECT Test_ID, Test_Desc
BULK COLLECT INTO va_Test_ID, va_Test_Desc
FROM Test;

FORALL i IN va_test_ID.FIRST..va_test_ID.LAST SAVE EXCEPTIONS
UPDATE TEST
SET NEW_DESC = TEST_DESC || LPAD(‘ ‘,22,’A’)
WHERE TEST_ID = va_test_ID(i);

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. It may be different for your scenario as data will be different.

Number of statements that failed: 2
Error #1 occurred during iteration #6
Error message is ORA-12899: value too large for column
Error #2 occurred during iteration #9
Error message is ORA-12899: value too large for column