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:
- A collection variable to hold the data
- The FORALL construct which iterates through values in the collection variable
- The SAVE EXCEPTIONS clause which tracks the exceptions being raised during bulk DML
- A single DML statement within the FORALL construct, which refers to the collection variable using the iterator value
- 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 another – often faster – solution for heavy DML with exception handling, read DML Error Logging.
