FORALL INSERT: Exception Handling in Bulk DML

February 6, 2018

in Miscellaneous

FORALL INSERT Bulk DML

Oracle PL/SQL gives you the ability to perform DML operations in bulk instead of via the regular row-by-row FOR loop. This article shows you how to use bulk DML and handle exceptions along the way.

Why Bulk DML at all?

You may well ask, when we *can* do DML via tried-and-tested, universally understood, row-by-row FOR loop, why have another way to do it?

The answer lies in performance optimization. PL/SQL Language Reference tells us:

FORALL statement runs one DML statement multiple times, with different values in the VALUES and WHERE clauses.

The different values come from existing, populated collections or host arrays. The FORALL statement is usually much faster than an equivalent FOR LOOP statement.

So if you insert N rows using a row-by-row FOR LOOP, it’ll execute the INSERT statement N times. If you go for bulk DML with FORALL instead, it’ll bind the same INSERT statement to N values. This reduces the overhead of context switching between SQL and PL/SQL.

Bulk DML: Sample Data

In this example, we will use two tables:

  • PEOPLE – Souce table from which data is to be read
  • CUSTOMER – Target table to which data is to be inserted

Both PEOPLE and CUSTOMER have two columns each: ID and NAME.

Table CUSTOMER has a unique constraint on columm ID.

Table PEOPLE has no unique constraint, and some data may have IDs repeated.

The DDLs:

create table customer
( 
  id    number not null
, name  varchar2(20)
);

alter table customer add constraint customer_uk unique (id);

create table people
( 
  id    number not null
, name  varchar2(20)
);

Before we begin:

  • table CUSTOMER is empty
  • table PEOPLE has 7 rows, of which 2 rows have non-unique IDs
SQL> select * from customer;

no rows selected

SQL> select * from people;

  ID NAME
---- ----------
   1 Jon
   2 Cersei
   2 Khal
   3 Gregor
   3 Sansa
   4 Arya
   5 Eddard

7 rows selected.

The requirement is to copy data from PEOPLE to CUSTOMER, discarding any errors (e.g. rows with duplicate IDs) if they occur. The entire insert should not fail because a few rows have errors.

We can achieve this easily and efficiently using FORALL INSERT.

Bulk DML: Building Blocks

Key components of bulk DML with exception handling:

  1. A collection variable to hold the data
  2. The FORALL construct which iterates through values in the collection variable
  3. The SAVE EXCEPTIONS clause which tracks the exceptions being raised during bulk DML
  4. A single DML statement within the FORALL construct, which refers to the collection variable using the iterator value
  5. A BEGIN-END wrapper for the FORALL construct, containing an EXCEPTION block which handles errors tracked by SAVE EXCEPTIONS

With the building blocks above, here’s a PL/SQL script that copies data from table PEOPLE to CUSTOMER, discards errors if they occur, and handles the errors.

Bulk DML: Script

declare

  cursor c_people is
    select id, name
    from people;
   
  type t_people is table of c_people%rowtype 
  index by binary_integer;
    
  l_people          t_people;
  l_err_msg         varchar2(1000);
  l_err_count       number;
  bulk_errors       exception;
    
  pragma exception_init(bulk_errors, -24381);    
  
begin

  open c_people;
  fetch c_people bulk collect into l_people;
  close c_people;
  
  dbms_output.put_line ('Before FORALL');  
  dbms_output.put_line ('Total Count = '||l_people.count);   
  
  begin
  
  -- bulk insert and save exceptions
  forall i in 1..l_people.count save exceptions
    
    insert into customer
    (
      id, name
    )
    values
    (
      l_people(i).id, l_people(i).name
    );    
         
  exception
    when bulk_errors then
      l_err_count := sql%bulk_exceptions.count;
      
      dbms_output.put_line ('Error Count = '||l_err_count); 
      
      for i in 1 .. l_err_count loop
        -- Print out details of each error during bulk insert
        l_err_msg  :=  'Error#: ' || i 
              || '; Array index: ' 
              || sql%bulk_exceptions(i).error_index ||  ': ' 
              || sqlerrm(-sql%bulk_exceptions(i).error_code);
                       
        dbms_output.put_line (l_err_msg);
      end loop;
  end;
  
  dbms_output.put_line ('After FORALL');
end;
/

Bulk DML: Understanding SAVE EXCEPTIONS

When an error occurs during bulk DML, the SAVE EXCEPTIONS clause  instructs the PL/SQL engine to store this information in a pseudocollection SQL%BULK_EXCEPTIONS and proceed with the next value in the collection.

After iterating through the whole collection, error ORA-24381 is raised in case any errors one or more rows failed in the bulk DML.

ORA-24381: error(s) in array DML

ORA-24381 can be trapped in an EXCEPTION handler, and the contents of SQL%BULK_EXCEPTIONS can be extracted in a FOR lOOP. Each value in the pseudocollection can be referenced using:

SQL%BULK_EXCEPTIONS(i).ERROR_INDEX: the iteration# in the FORALL statement during which the exception was raised.

SQL%BULK_EXCEPTIONS(i).ERROR_CODE: the Oracle error code of the exception

In this example, EXCEPTION_INIT is used to link user-defined exception dml_errors with ORA-24381.

Bulk DML: Script when run

SQL> declare
  2
  3    cursor c_people is
  4      select id, name
  5      from people;
  6
  7    type t_people is table of c_people%rowtype
  8    index by binary_integer;
  9
 10    l_people          t_people;
 11    l_err_msg         varchar2(1000);
 12    l_err_count       number;
 13    bulk_errors       exception;
 14
 15    pragma exception_init(bulk_errors, -24381);
 16
 17  begin
 18
 19    open c_people;
 20    fetch c_people bulk collect into l_people;
 21    close c_people;
 22
 23    dbms_output.put_line ('Before FORALL');
 24    dbms_output.put_line ('Total Count = '||l_people.count);
 25
 26    begin
 27
 28    -- bulk insert and save exceptions
 29    forall i in 1..l_people.count save exceptions
 30
 31      insert into customer
 32      (
 33        id, name
 34      )
 35      values
 36      (
 37        l_people(i).id, l_people(i).name
 38      );
 39
 40    exception
 41      when bulk_errors then
 42        l_err_count := sql%bulk_exceptions.count;
 43
 44        dbms_output.put_line ('Error Count = '||l_err_count);
 45
 46        for i in 1 .. l_err_count loop
 47          -- Print out details of each error during bulk insert
 48          l_err_msg  :=  'Error#: ' || i
 49                || '; Array index: '
 50                || sql%bulk_exceptions(i).error_index ||  ': '
 51                || sqlerrm(-sql%bulk_exceptions(i).error_code);
 52
 53          dbms_output.put_line (l_err_msg);
 54        end loop;
 55    end;
 56
 57    dbms_output.put_line ('After FORALL');
 58  end;
 59  /
Before FORALL
Total Count = 7
Error Count = 2
Error#: 1; Array index: 3: ORA-00001: unique constraint (.) violated
Error#: 2; Array index: 5: ORA-00001: unique constraint (.) violated
After FORALL

PL/SQL procedure successfully completed.

Making sense of the results

We see that the script:

  • finished successfully
  • reported two unique constraint violation errors, along with sufficient details for debugging the error
  • inserted data as directed
  • did not exit on the first error, but iterated through the entire data set

Verifying the results

Let’s check the values in table CUSTOMER after the script is run:

SQL> select * from customer;

  ID NAME
---- ----------
   1 Jon
   2 Cersei
   3 Gregor
   4 Arya
   5 Eddard

Bingo – the data has been copied over from table PEOPLE to CUSTOMER, minus the duplicate IDs, as expected.

If you would like to reformat the repeating IDs instead of discarding them, take a look at: Replace Duplicates in ID Columns with Unique Values.

Summary

This article describes how to use the FORALL construct and handle exceptions during bulk DML in Oracle.

For Further Reading

{ 0 comments… add one now }

Leave a Comment

Previous post:

Next post: