Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Handling Exceptions in Bulk Operations


Oracle Tips by Burleson Consulting

 

The following Tip is from the outstanding book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006:

There are a number of issues regarding exception handling that must be considered when using bulk operations.  In this section, rollback behavior of bulk operations and the methods available to control this behavior is examined.

In order to demonstrate this functionality, a simple test table containing a single mandatory column must first be created.  This is performed using the exception_test.sql script listed below.

exception_test.sql

CREATE TABLE exception_test (
  id  NUMBER(10) NOT NULL
);

After the table is created, the way unhandled exceptions are treated during bulk operations can be examined.

Unhandled Exceptions

Unhandled exceptions during the execution of a bulk operation cause the entire operation to be rolled back.    This functionality is demonstrated using the unhandled_exception.sql script listed below.

unhandled_exception.sql

DECLARE
  TYPE t_tab IS TABLE OF exception_test.id%TYPE;

  l_tab    t_tab := t_tab();
BEGIN
  -- Fill the collection.
  FOR i IN 1 .. 100 LOOP
    l_tab.extend;
    l_tab(l_tab.last) := i;
  END LOOP;

  -- Cause a failure.
  l_tab(50) := NULL; 

  EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test';

  -- Perform a bulk operation.
  FORALL i IN l_tab.first .. l_tab.last
    INSERT INTO exception_test
    VALUES (l_tab(i));
END;
/

SET ECHO ON
SELECT COUNT(*)
FROM   exception_test;
SET ECHO OFF

The unhandled_exception.sql script first creates and populates a collection. Next it assigns the value of NULL to the 50th element, thereby forcing an error.  It then truncates the test table, attempts a bulk insert against it and displays the record count.  The output from this script is listed below.

SQL> @unhandled_exception.sql

DECLARE
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("TIM_HALL"."EXCEPTION_TEST"."ID")
ORA-06512: at line 18

SQL> SELECT COUNT(*)
  2  FROM   exception_test;


  COUNT(*)
----------
         0

1 row selected.

SQL> SET ECHO OFF

As expected an exception is raised when the bulk operation reaches the 50th element, resulting in the whole operation being rolled back.  The rollback is evident since the record count is zero.

The following section shows the way handled exceptions are treated during bulk operations.

Handled Exceptions

During a bulk operation a savepoint is created between each SQL execution.  In the event a handled exception is raised, the operation is rolled back to the previous savepoint instead of restarting the whole operation.  The handled_exception.sql script listed below demonstrates this behavior.

handled_exception.sql

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF exception_test.id%TYPE;

  l_tab    t_tab := t_tab();
BEGIN
  -- Fill the collection.
  FOR i IN 1 .. 100 LOOP
    l_tab.extend;
    l_tab(l_tab.last) := i;
  END LOOP;

  -- Cause a failure.
  l_tab(50) := NULL; 

  EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test';

  -- Perform a bulk operation.
  BEGIN
    FORALL i IN l_tab.first .. l_tab.last
      INSERT INTO exception_test
      VALUES (l_tab(i));
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.put_line(SQLERRM);
  END;
END;
/

SET ECHO ON
SELECT COUNT(*)
FROM   exception_test;
SET ECHO OFF

The handled_exception.sql script is a modified version of the unhandled_exception.sql script in that the bulk operation has been surrounded by an anonymous block containing an exception handler that displays the error message.  The output from this script is listed below.

SQL> @handled_exception.sql

ORA-01400: cannot insert NULL into ("TIM_HALL"."EXCEPTION_TEST"."ID")

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*)
  2  FROM   exception_test;

  COUNT(*)
----------
        49

1 row selected.

SQL> SET ECHO OFF

Once again the bulk operation results in an exception, but this time the exception is trapped so the rollback is restricted to the previous savepoint, as shown by the record count of 49.

That works well if the goal is to stop the operation at that point, but what if the goal is to proceed past any problem rows?  In order to achieve this, the SAVE EXCEPTIONS clause of the FORALL statement must be used; the subject of the next section.

Bulk Operations that Complete

Since Oracle 9i the FORALL statement includes an optional SAVE EXCEPTIONS clause that allows bulk operations to save exception information and continue processing.  Once the operation is complete, the exception information can be retrieved using the SQL%BULK_EXCEPTIONS attribute.  This is a collection of exceptions for the most recently executed FORALL statement, with the following two fields for each exception:

SQL%BULK_EXCEPTIONS(i).ERROR_INDEX Holds the iteration (not the subscript) of the original FORALL statement that raised the exception.  In sparsely populated collections, the exception row must be found by looping through the original collection the correct number of times.

SQL%BULK_EXCEPTIONS(i).ERROR_CODE Holds the exceptions error code.

The total number of exceptions can be returned using the collections COUNT method, which returns zero if no exceptions were raised.  The save_exceptions.sql script, a modified version of the handled_exception.sql script, demonstrates this functionality.

save_exceptions.sql

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF exception_test%ROWTYPE;

  l_tab          t_tab := t_tab();
  l_error_count  NUMBER; 

  ex_dml_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);
BEGIN
  -- Fill the collection.
  FOR i IN 1 .. 100 LOOP
    l_tab.extend;
    l_tab(l_tab.last).id := i;
  END LOOP;

  -- Cause a failure.
  l_tab(50).id := NULL;
  l_tab(51).id := NULL; 

  EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test';

  -- Perform a bulk operation.
  BEGIN
    FORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONS
      INSERT INTO exception_test
      VALUES l_tab(i);
  EXCEPTION
    WHEN ex_dml_errors THEN
      l_error_count := SQL%BULK_EXCEPTIONS.count;
      DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
      FOR i IN 1 .. l_error_count LOOP
        DBMS_OUTPUT.put_line('Error: ' || i ||
          ' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
          ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
      END LOOP;
  END;
END;
/

SET ECHO ON
SELECT COUNT(*)
FROM   exception_test;
SET ECHO OFF

The FORALL statement includes the SAVE EXCEPTIONS clause, and the exception handler displays the number of exceptions and their associated error messages.  The output from the save_exceptions.sql script is listed below.

SQL> @save_exceptions.sql

Number of failures: 2
Error: 1 Array Index: 50 Message: ORA-01400: cannot insert NULL into ()
Error: 2 Array Index: 51 Message: ORA-01400: cannot insert NULL into ()

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*)
  2  FROM   exception_test;

  COUNT(*)
----------
        98

1 row selected.

SQL> SET ECHO OFF

As expected the test table contains 98 of the 100 records, and the associated error message has been displayed by looping through the SQL%BULK_EXCEPTION collection.

If the SAVE EXCEPTIONS clause is omitted from the FORALL statement, execution of the bulk operation stops at the first exception and the SQL%BULK_EXCEPTIONS collection contains a single record.  The no_save_exceptions.sql script demonstrates this behavior.

no_save_exceptions.sql

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF exception_test%ROWTYPE;

  l_tab          t_tab := t_tab();
  l_error_count  NUMBER; 

  ex_dml_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(ex_dml_errors, -01400);
BEGIN
  -- Fill the collection.
  FOR i IN 1 .. 100 LOOP
    l_tab.extend;
    l_tab(l_tab.last).id := i;
  END LOOP;

  -- Cause a failure.
  l_tab(50).id := NULL;
  l_tab(51).id := NULL; 

  EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test';

  -- Perform a bulk operation.
  BEGIN
    FORALL i IN l_tab.first .. l_tab.last
      INSERT INTO exception_test
      VALUES l_tab(i);
  EXCEPTION
    WHEN ex_dml_errors THEN
      l_error_count := SQL%BULK_EXCEPTIONS.count;
      DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
      FOR i IN 1 .. l_error_count LOOP
        DBMS_OUTPUT.put_line('Error: ' || i ||
          ' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
          ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
     END LOOP;
  END;
END;
/

SET ECHO ON
SELECT COUNT(*)
FROM   exception_test;
SET ECHO OFF

Notice that in addition to the SAVE EXCEPTIONS clause being removed, the no_save_exceptions.sql script now traps a different error number.  The output from this script is listed below.

SQL> @no_save_exceptions.sql

Number of failures: 1
Error: 1 Array Index: 50 Message: ORA-01400: cannot insert NULL into
("TIM_HALL"."EXCEPTION_TEST"."ID")

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*)
  2  FROM   exception_test;

  COUNT(*)
----------
        49

1 row selected.

SQL> SET ECHO OFF

As expected there is only a single error in the SQL%BULK_EXCEPTIONS collection, and there are only 49 records in the test table as the operation has rolled back to the preceding implicit savepoint.

As shown from previous examples, a move from conventional operations to bulk operations will require a revision of your current exception handling or the desired results may not appear.

The use of bulk operations with dynamic SQL is explained in the next section.

 

This is an excerpt from the bestselling book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006.

You can buy the book for only $23.95 (30%-off) when you buy directly from the publisher, and you also get instant access to the code depot of PL/SQL tuning scripts:


 

 
��  
 
 

 
 
 
 
oracle dba poster
 

 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright 1996 -  2014

All rights reserved by Burleson

Oracle is the registered trademark of Oracle Corporation.