Using of BULK COLLECT and FORALL for better performance

My experience, findings and thoughts in my daily work with Oracle products

Using of BULK COLLECT and FORALL for better performance

Published Tuesday, August 16, 2005 by Radoslav Rusinov | E-mail this post

Lets see some quotes from the Oracle documentation:

---
PL/SQL engine executes procedural statements but sends SQL statements to the SQL engine, which executes the SQL statements and, in some cases, returns data to the PL/SQL engine.
Too many context switches between the PL/SQL and SQL engines can harm performance. That can happen when a loop executes a separate SQL statement for each element of a collection, specifying the collection element as a bind variable. For example, the following DELETE statement is sent to the SQL engine with each iteration of the FOR loop:

DECLARE
TYPE NumList IS VARRAY(20) OF NUMBER;
depts NumList := NumList(10, 30, 70); -- department numbers
BEGIN
…
FOR i IN depts.FIRST..depts.LAST LOOP
DELETE FROM emp WHERE deptno = depts(i);
END LOOP;
…
END;

Using the FORALL Statement
The keyword FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine. Although the FORALL statement contains an iteration scheme, it is not a FOR loop. Its syntax follows:

FORALL index IN lower_bound..upper_bound
sql_statement;

FORALL does not require a LOOP command.
---
Usually, using of the BULK COLLECT and FORALL statements can drastically improve the performance. For example, I have rewritten a PL/SQL procedure used in a batch job and in result the execution time has been reduced from 12 minutes to 16 seconds – only by using BULK COLLECT and FORALL.
In few words, you must always try to avoid this:

Copyrighted from Steven Feuerstein's book - Oracle PL/SQL Programming, Third Edition

If you are one of these who already are using this feature or you have a PL/SQL procedures that are suffering from slow performance because “row-by-row” processing then may be you will be interested in the following post.
The record types can be defined as based on cursor:

…
CURSOR c_dept_data IS
SELECT dept_id, dept_name
FROM departments
WHERE dept_id in (8,10);

TYPE rt_dept IS
TABLE of c_dept_data%ROWTYPE INDEX BY BINARY_INTEGER;
vrt_dept rt_dept;
…

or based on table:

…
TYPE rt_dept IS
TABLE of departments%ROWTYPE INDEX BY BINARY_INTEGER;
vrt_dept rt_dept;
…

Table DEPARTMENTS have four columns: dept_id,dept_name,dept_mgr_id,dept_description
Lets try to use the BULK COLLECT:

...
SELECT dept_id,dept_name,dept_mgr_id,dept_description BULK COLLECT INTO vrt_dept FROM departments;
...

And to insert the collected data into some temporary table with FORALL:

...
FORALL i IN vrt_dept.FIRST .. vrt_dept.LAST
INSERT INTO departments_tmp VALUES vrt_dept (i);
...

It is working and everything seems okay.
Lets try to do something little bit complicated - to insert only some of the collected columns by changing only the FORALL statement:

...
FORALL i IN vrt_dept.FIRST .. vrt_dept.LAST
INSERT INTO departments_tmp (dept_id,dept_description) VALUES (vrt_dept(i).dept_id,vrt_dept(i).dept_description);
...

In result the following compilation error is returned:

PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records

That is because bulk bind cannot use table of composite types. It works fine with a table of native types, but not with a table of objects or records. The compiler does not support that reference to bulk binds fields. For more information see this Metalink note:
Binding a Collection With FORALL Fails With ORA-01767or PLS-00103
But this is probably the most common case when someone will want to use the BULK COLLECT and FORALL statements (for example - cursor with parameters and joins of three or more tables). The workaround of this problem is to be used tables of native types:

…
TYPE rt_dept_id IS TABLE OF departments.dept_id%TYPE INDEX BY BINARY_INTEGER;
TYPE rt_dept_description IS TABLE OF departments.dept_description%TYPE INDEX BY BINARY_INTEGER;

vrt_dept_id rt_dept_id;
vrt_dept_description rt_dept_description;
…

Then:

…
SELECT dept_id,dept_description BULK COLLECT INTO vrt_dept_id, vrt_dept_description FROM departments;
…

And then:

…
FORALL i IN vrt_dept.FIRST .. vrt_dept.LAST
INSERT INTO departments_tmp (dept_id,dept_description) VALUES (vrt_dept_id(i),vrt_dept_description(i));
…

Now the procedure is compiled and works correctly after testing.
If you are sure about the used types instead declaring the tables by this way:

…
TYPE rt_dept_id IS TABLE OF departments.dept_id%TYPE INDEX BY BINARY_INTEGER;
…

You can define a collection variables in the following way (without defining types, just collection variables):

…
vrt_dept_id dbms_sql.NUMBER_TABLE;
vrt_dept_description dbms_sql.VARCHAR2_TABLE;
…

For full reference of Bulk SQL types, use this link: Constants, Types, and Exceptions for DBMS_SQL
If there is a possibility the returned record set to be empty then there are few important rules that should be followed.
Lets try some example with a cursor:

…
CURSOR c_dept_data IS
SELECT dept_id, dept_name
FROM departments
WHERE dept_id in (8,10);

TYPE rt_dept_id IS TABLE OF departments.dept_id%TYPE INDEX BY BINARY_INTEGER;
TYPE rt_dept_name IS TABLE OF departments.dept_name%TYPE INDEX BY BINARY_INTEGER;

vrt_dept_id rt_dept_id;
vrt_dept_name rt_dept_name;
…

BEGIN
…
OPEN c_dept_data;
FETCH c_dept_data BULK COLLECT INTO vrt_dept_id,vrt_dept_name;
CLOSE c_dept_data;

FORALL i IN vrt_dept_id.FIRST .. vrt_dept_id.LAST
UPDATE departments_tmp
SET dept_name = vrt_dept_name(i)
WHERE dept_id = vrt_dept_id(i);
…

In this case the procedure is compiled but it returns a runtime error when c_dept_date cursor returns no rows:

ORA-06502: PL/SQL: numeric or value error

This problem can be solved by using of one of the collection methods available: COUNT. The COUNT method returns the number of elements defined in an array:

…
BEGIN
…
OPEN c_dept_data;
LOOP
FETCH c_dept_data BULK COLLECT INTO vrt_dept_id,vrt_dept_name;

FORALL i IN 1 .. vrt_dept_id.COUNT
UPDATE departments_tmp
SET dept_name = vrt_dept_name(i)
WHERE dept_id = vrt_dept_id(i);

EXIT WHEN c_dept_data%NOTFOUND;
END LOOP;
CLOSE c_dept_data;
…

In the above case the FIRST and LAST collection methods are replaced by the COUNT collection method, a LOOP block is added plus EXIT condition.
The other workaround is the following:

…
BEGIN
…
OPEN c_dept_data;
FETCH c_dept_data BULK COLLECT INTO vrt_dept_id,vrt_dept_name;

CLOSE c_dept_data;
IF vrt_dept_id.COUNT > 0 THEN
-- do FORALL
ELSE
-- do something else
END IF;
…

Rollback behavior
If one of the bulk DML statements fails, the individual statement is rolled back and previous DML statements within the FORALL series of statements are not rolled back and the FORALL statement stops executing.
For example if you are using FORALL to insert 100 rows and an error appears on 78th row the execution will stop and rows from 78 to 100 will not be inserted indeed. If you want to overcome this behavior and to insert all rows you can use the SAVE EXCEPTIONS clause.
With the SAVE EXCEPTIONS the cursor attribute SQL%BULK_EXCEPTIONS is used. It is an array that records two elements ERROR_INDEX and ERROR_CODE:

...
v_bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT (v_bulk_errors, -24381);
…
BEGIN -- outer BEGIN
…
BEGIN –- inner BEGIN clause
OPEN c_dept_data;
LOOP
FETCH c_dept_data BULK COLLECT INTO vrt_dept_id,vrt_dept_name;

FORALL i IN 1 .. vrt_dept_id.COUNT
SAVE EXCEPTIONS
UPDATE departments_tmp
SET dept_name = vrt_dept_name(i)
WHERE dept_id = vrt_dept_id(i);

EXIT WHEN c_dept_data%NOTFOUND;
END LOOP;
CLOSE c_dept_data;

EXCEPTION WHEN bulk_errors THEN
FOR i in 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
Dbms_output.put_line(
'An error '|| i ||' was occured ' ||
SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
' during update of dept_id: '||
vrt_dept_id(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX)||
'. Oracle error: ' ||
SQLERRM(-1 * SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;

END; -- end inner BEGIN
…

Be aware of that you can use RETURNING clause with FORALL. This functionality can be used an array with affected rows to be populated and after that to be used for performing other actions with affected data.
Limiting of processed rows
If you want to process an limited count of rows you can use the LIMIT clause. For example, if you need to process a huge table then it is possible to overload your process memory (that will result with an ORA-04030 error) :

...
OPEN c_dept_data;
LOOP
FETCH c_dept_data BULK COLLECT INTO vrt_dept_id,vrt_dept_name LIMIT 1000;

FORALL i IN 1 .. vrt_dept_id.COUNT
UPDATE departments_tmp
SET dept_name = vrt_dept_name(i)
WHERE dept_id = vrt_dept_id(i);

EXIT WHEN c_dept_data%NOTFOUND;
END LOOP;
CLOSE c_dept_data;
...

In this case the collection variables will be populated on every 1000 rows, after that the FORALL will be executed for them and then will be fetched the next 1000. Be aware of that the previous values for the collection are overwritten.
The other workaround to cope with memory problems coming from large PL/SQL tables is to execute the following supplied stored procedure inside your code:

..
dbms_session.free_unused_user_memory;
..

It will free unused memory for your session after large portions of data was processed. For full details about solving of memory problems with this procedure, see: Procedure DBMS_SESSION.FREE_UNUSED_USER_MEMORY Specification
You can use the SQL%BULK_ROWCOUNT attribute, which is available with the FORALL statement. For each processed row in FORALL there is a corresponding row for this attribute. For example, if you want to know did the n-th processed row from FORALL have affected some rows then you must call SQL%BULK_ROWCOUNT(n). If no rows were affected, the n-th row will be zero.
For more examples and explanations about using the BULK COLLECT and FORALL you can refer to the documentation:
Reducing Loop Overhead for Collections with Bulk Binds
Additional specific information:
Array insert may report false constraint violations with row triggers
Bulk Operations Across a Database Link is not a Supported Feature in 8i

Labels: Performance

6 Responses to “Using of BULK COLLECT and FORALL for better performance”

Tarry Singh on 1:12 AM
Interesting article, Radoslav! Maybe next article can you illustrate some stuff like more realistic example/puzzle type pl/sql? Fro the 10g's sample schema's maybe.

Good job. Keep it up :-)
Radoslav Rusinov on 9:19 AM
Thank you, Tarry!

Yes, my examples cannot be reproduced because I have published only quotes. In the next posts I will try to put fully reproducable ones that will help to everyone to play around with the discussed subject.
Anonymous on 9:56 AM
Hi, Radoslav, I read your article for BULK COLLECT and FORALL , but I have some problems with this example:

CREATE TABLE fk_table(
fk_id NUMBER,
fk_descr VARCHAR2(255),
CONSTRAINT fk_table_pk PRIMARY KEY
( fk_id )
);

CREATE TABLE source_table(
u_id NUMBER NOT NULL,
dt DATE NOT NULL,
fk_id NUMBER NOT NULL,
notes VARCHAR2(200),
err_msg VARCHAR2(500) -- Column for error messages
);

CREATE TABLE target_table(
u_id NUMBER NOT NULL,
dt DATE NOT NULL,
fk_id NUMBER NOT NULL,
notes VARCHAR2(200),
CONSTRAINT target_table_pk PRIMARY KEY
( u_id, dt, fk_id )
)
PARTITION BY RANGE (dt)
(
PARTITION P_2005_01_01 VALUES LESS THAN ( to_date('2005-01-01','yyyy-mm-dd')),
PARTITION P_2005_02_01 VALUES LESS THAN ( to_date('2005-02-01','yyyy-mm-dd')),
partition RECORDS_MAX values less than (MAXVALUE)
)
;

ALTER TABLE target_table
ADD CONSTRAINT target_table_fk FOREIGN KEY( fk_id )
REFERENCES fk_table( fk_id );

INSERT INTO source_table( u_id, dt, fk_id, notes )
VALUES (1, to_date('2005-01-02','yyyy-mm-dd'), 1, 'aaaa');

INSERT INTO source_table( u_id, dt, fk_id, notes )
VALUES (2, to_date('2005-01-02','yyyy-mm-dd'), 4, 'bbbbb');

INSERT INTO source_table( u_id, dt, fk_id, notes )
VALUES (3, to_date('2005-01-02','yyyy-mm-dd'), 2, 'cccc');

INSERT INTO source_table( u_id, dt, fk_id, notes )
VALUES (1, to_date('2005-01-02','yyyy-mm-dd'), 1, 'fffff');

INSERT INTO source_table( u_id, dt, fk_id, notes )
VALUES (1, to_date('2005-01-02','yyyy-mm-dd'), 1, 'wwwww');

COMMIT;

set serveroutput on size 1000000

/***********************************************************************
* Some insert into TARGET_TABLE raises two errors, exactly 2 of them *
* First error is from primary key violation on 1,4,5 rows from source *
* table and second error is from all rows because FK_TABLE has no rows.*
* When you run script you will see that SQL%BULK_EXCEPTIONS *
* collection will show first and second errors from 4 and 5 row with *
* error code ORA-00001, and after them will show 3 errors from *
* 3,4,5 row with error code ORA-02291. The question is what hapenn *
* with errors from 1 and 2 row from source table ? And why for 4 and 5 *
* rows have 2 error messages in that coolection ? *
***********************************************************************/

DECLARE
TYPE u_id_tabtype IS TABLE OF source_table.u_Id%TYPE;
TYPE dt_tabtype IS TABLE OF source_table.dt%TYPE;
TYPE fk_id_tabtype IS TABLE OF source_table.fk_id%TYPE;
TYPE notes_tabtype IS TABLE OF source_table.notes%TYPE;

v_u_id u_id_tabtype;
v_dt dt_tabtype;
v_fk_id fk_id_tabtype;
v_notes notes_tabtype;

v_bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT (v_bulk_errors, -24381);
BEGIN
SELECT u_id,
dt,
fk_id,
notes
BULK COLLECT INTO
v_u_id,
v_dt,
v_fk_id,
v_notes
FROM source_table;

IF v_u_id.count > 0
THEN
BEGIN
FORALL ii IN v_u_id.FIRST .. v_u_id.LAST
SAVE EXCEPTIONS
INSERT
INTO target_table(
u_id,
dt,
fk_id,
notes
)
VALUES(
v_u_id(ii),
v_dt(ii),
v_fk_id(ii),
v_notes(ii)
);

EXCEPTION
WHEN v_bulk_errors THEN
FOR jj IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(
'An error '|| jj ||' was occured on ' ||
SQL%BULK_EXCEPTIONS(jj).ERROR_INDEX ||
' row. Oracle error: ' ||
SQLERRM(-1 * SQL%BULK_EXCEPTIONS(jj).ERROR_CODE));
END LOOP;
END;
END IF;

END;
/
Radoslav Rusinov on 12:39 PM
It seems that FORALL have not good interaction with the SQL engine.
By my opinion, the problem is coming because these rows:
INSERT INTO source_table(u_id, dt, fk_id, notes)
VALUES (1, to_date('2005-01-02','yyyy-mm-dd'), 1, 'aaaa');
INSERT INTO source_table( u_id, dt, fk_id, notes )
VALUES (1, to_date('2005-01-02','yyyy-mm-dd'), 1, 'fffff');
INSERT INTO source_table( u_id, dt, fk_id, notes )
VALUES (1, to_date('2005-01-02','yyyy-mm-dd'), 1, 'wwwww');
Two errors are possible to be returned: ORA-00001 and ORA-02291
If you try to insert standalone one of them:
INSERT INTO target_table(u_id,dt,fk_id,notes)
VALUES(1, to_date('2005-01-02','yyyy-mm-dd'), 1, 'wwwww');
then an ORA-02291 error will be returned but if you remove the foreign key then the ORA-00001 will be returned.
I have changed the data to be sure that only one error can be raised:
DELETE source_table WHERE u_id = 1;
INSERT INTO source_table(u_id, dt, fk_id, notes)
VALUES (1, to_date('2005-01-02','yyyy-mm-dd'), 1, 'aaaa');
INSERT INTO source_table( u_id, dt, fk_id, notes )
VALUES (1, to_date('2005-01-02','yyyy-mm-dd'), 2, 'fffff');
INSERT INTO source_table( u_id, dt, fk_id, notes )
VALUES (1, to_date('2005-01-02','yyyy-mm-dd'), 3, 'wwwww');
COMMIT;
I have changed into the PL/SQL code - BULK COLLECT to populate collection variables in some expected order:
SELECT u_id,dt,fk_id,notes
BULK COLLECT INTO v_u_id,
v_dt,
v_fk_id,
v_notes
FROM source_table
ORDER BY u_id;

In result:
An error 1 was occured during iteration 1. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
An error 2 was occured during iteration 2. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
An error 3 was occured during iteration 3. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
An error 4 was occured during iteration 4. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
An error 5 was occured during iteration 5. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
It gives the expected result.
Lets change the data again to be the same like in your example:
DELETE source_table WHERE u_id = 1;
INSERT INTO source_table(u_id, dt, fk_id, notes)
VALUES (1, to_date('2005-01-02','yyyy-mm-dd'), 1, 'aaaa');
INSERT INTO source_table( u_id, dt, fk_id, notes )
VALUES (1, to_date('2005-01-02','yyyy-mm-dd'), 1, 'fffff');
INSERT INTO source_table( u_id, dt, fk_id, notes )
VALUES (1, to_date('2005-01-02','yyyy-mm-dd'), 1, 'wwwww');
COMMIT;
Now, the result is:
An error 1 was occured during iteration 2. Oracle error: ORA-00001: unique constraint (.) violated
An error 2 was occured during iteration 3. Oracle error: ORA-00001: unique constraint (.) violated
An error 3 was occured during iteration 3. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
An error 4 was occured during iteration 4. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
An error 5 was occured during iteration 5. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
This is when the order for the inserted rows is ordered by u_id, which means that the (1,1,1,2,3) is the order of the returned rows.
Lets change the ordering to DESC:
SELECT u_id,dt,fk_id,notes
BULK COLLECT INTO v_u_id,
v_dt,
v_fk_id,
v_notes
FROM source_table
ORDER BY u_id DESC;
Then we will processing the rows in this order (3,2,1,1,1). In result:
An error 1 was occured during iteration 4. Oracle error: ORA-00001: unique constraint (.) violated
An error 2 was occured during iteration 5. Oracle error: ORA-00001: unique constraint (.) violated
An error 3 was occured during iteration 3. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
An error 4 was occured during iteration 4. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
An error 5 was occured during iteration 5. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
You can see from the above result that it is hard to be found some logic in the returned result from the BULK EXCEPTIONS array.
The only way to be sure that BULK EXCEPTIONS will be populated correctly is to avoid cases when two errors are possible to be raised and you don't know which one will choose Oracle to return to you.
My suggestions:
What will happens if two errors are returned for one inserted row. For example, may be Oracle probes two times or something like that.
Lets take first case with this order (1,1,1,2,3), then lets populate one imaginery array with all returned errors. Lets suppose that ORA-0001 will be writed first.
An error -2 was occured during iteration 1. Oracle error: ORA-00001: unique constraint (.) violated
An error -1 was occured during iteration 1. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
An error 0 was occured during iteration 2. Oracle error: ORA-00001: unique constraint (.) violated
An error 1 was occured during iteration 2. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
An error 2 was occured during iteration 3. Oracle error: ORA-00001: unique constraint (.) violated
An error 3 was occured during iteration 3. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
An error 4 was occured during iteration 4. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
An error 5 was occured during iteration 5. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
You see that the first three are out of range of the expected array size: 5. Lets left the last 5:
An error 1 was occured during iteration 2. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
An error 2 was occured during iteration 3. Oracle error: ORA-00001: unique constraint (.) violated
An error 3 was occured during iteration 3. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
An error 4 was occured during iteration 4. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
An error 5 was occured during iteration 5. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
The same result is similar like the above that we received during the testing. The difference is in the first line because I don't know how is populated the array with the returned two errors, I don't know which one was populated first and which second.
Lets take a look when we ordered u_id in descending order (3,2,1,1,1). Following the new logic, the array will be populated like this:
An error -2 was occured during iteration 1. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
An error -1 was occured during iteration 2. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
An error 0 was occured during iteration 3. Oracle error: ORA-00001: unique constraint (.) violated
An error 1 was occured during iteration 3. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
An error 2 was occured during iteration 4. Oracle error: ORA-00001: unique constraint (.) violated
An error 3 was occured during iteration 4. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
An error 4 was occured during iteration 5. Oracle error: ORA-00001: unique constraint (.) violated
An error 5 was occured during iteration 5. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
Lets cut again the last 5:
An error 1 was occured during iteration 3. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
An error 2 was occured during iteration 4. Oracle error: ORA-00001: unique constraint (.) violated
An error 3 was occured during iteration 4. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
An error 4 was occured during iteration 5. Oracle error: ORA-00001: unique constraint (.) violated
An error 5 was occured during iteration 5. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
You see that the result is again closer but only closer, there are the same rows but on different places.
Lets try onel ast thing, to left only two rows that can raise two errors at same time:
DELETE source_table WHERE u_id = 1;
INSERT INTO source_table(u_id, dt, fk_id, notes)
VALUES (1, to_date('2005-01-02','yyyy-mm-dd'), 1, 'aaaa');
INSERT INTO source_table( u_id, dt, fk_id, notes )
VALUES (1, to_date('2005-01-02','yyyy-mm-dd'), 1, 'fffff');
INSERT INTO source_table( u_id, dt, fk_id, notes )
VALUES (1, to_date('2005-01-02','yyyy-mm-dd'), 3, 'wwwww');
COMMIT;
and to change:
SELECT u_id,dt,fk_id,notes
BULK COLLECT INTO v_u_id,
v_dt,
v_fk_id,
v_notes
FROM source_table
ORDER BY u_id, fk_id;
Then the ordered result set will be (u_id,fk_id) -> (1,1),(1,1),(1,3),(2,4),(3,2).
The expected population of the BULK EXCEPTION array will be:
An error -1 was occured during iteration 1. Oracle error: ORA-00001: unique constraint (.) violated
An error 0 was occured during iteration 1. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
An error 1 was occured during iteration 2. Oracle error: ORA-00001: unique constraint (.) violated
An error 2 was occured during iteration 2. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
An error 3 was occured during iteration 3. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
An error 4 was occured during iteration 4. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
An error 5 was occured during iteration 5. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
After running of the procedure, the returned result is:
An error 1 was occured during iteration 4. Oracle error: ORA-00001: unique constraint (.) violated
An error 2 was occured during iteration 2. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
An error 3 was occured during iteration 3. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
An error 4 was occured during iteration 4. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
An error 5 was occured during iteration 5. Oracle error: ORA-02291: integrity constraint (.) violated - parent key not found
This time it is the expected result like if we just cut the last 5 rows (by following our imaginery logic).
This theory is too artificial but there is some logic that can explain partially this behavior. It seems that the SQL engine probes to insert sended rows from FORALL,
but it returns to PL/SQL engine unexpected number of errors, two for some of the inserted rows. In result, the BULK EXCEPTIONS is populated wrongly and in unexpected order.
I don't have better explanation.
Any more suggestion are welcome.
Anonymous on 2:21 PM
I create TAR for this problem and when have some resolution for my problem will post here it.

Thanks for help ;-)
Anonymous on 5:23 PM
Here what says Oracle Support:

So it looks like the ora-1s overwrite the first 2. Checking for "save exceptions" bugs...
some found, but no identical matches
checking if any data was actually loaded into the target table...no.

Creating new bug...done: 4568388

About me

» I'm Radoslav Rusinov
» From Sofia, Bulgaria
» Employer TechnoLogica Ltd.
» I am working as a Database Consultant in Sofia, Bulgaria. My main professional interests are in the database area and especially in the Oracle RDBMS, including database design, development, security and administration.
» The views expressed on this blog are my own and do not necessarily reflect the views of my employing company and its affiliates
» My profile