Thanks for the question, James.

Asked: July 09, 2007 - 1:46 pm UTC

Last updated: December 15, 2010 - 11:45 am UTC

Version: 10.1.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I've got a question I was going to submit to the OTN forums, but thought I may as well ask you since I have the opportunity.

I have a package that contains one public procedure that's executing a number of bulk-bind DML statements.

I would like to add a private procedure to my package that will handle any exceptions that occur during one particular bulk update. I don't particularly NEED to do this, but for reasons of code readability, I'd quite like to.

In order for this to work, I need to be able to pass the SQL%BULK_EXCEPTIONS collection as a parameter to my private procedure.

The main problem I have is that SQL%BULK_EXCEPTIONS is based on an Oracle-defined collection type, and every user-defined type I've tried to match it with has resulted in a type-mismatch compile-time error in SQL.

I've traweled the docs, and can't find anything related to this. Am I wishing for the impossible, or can this be done?

Thanks in advance for any help/opinion you can offer/have.

James

and Tom said...

Funny thing that - you don't need to pass it.

http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/tuning.htm#sthref2201

...
The values stored by %BULK_EXCEPTIONS always refer to the most recently executed FORALL statement. The number of exceptions is saved in %BULK_EXCEPTIONS.COUNT. Its subscripts range from 1 to COUNT.
....

ops$tkyte%ORA10GR2> create table emp ( sal int );

Table created.

ops$tkyte%ORA10GR2> insert into emp values ( 1000 );

1 row created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure dump_bulk_exceptions
  2  as
  3  begin
  4      dbms_output.put_line('Number of errors is ' || sql%bulk_exceptions.count );
  5          for i in 1 .. sql%bulk_exceptions.count
  6          loop
  7                  dbms_output.put_line
  8                  ('Error ' || i || ' occurred during '||
  9           'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
 10          dbms_output.put_line('Oracle error is ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
 11          end loop;
 12  end;
 13  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> DECLARE
  2     TYPE NumList IS TABLE OF NUMBER;
  3     num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1);
  4     errors NUMBER;
  5     dml_errors EXCEPTION;
  6     PRAGMA exception_init(dml_errors, -24381);
  7  BEGIN
  8     FORALL i IN num_tab.FIRST..num_tab.LAST SAVE EXCEPTIONS
  9        DELETE FROM emp WHERE sal > 500000/num_tab(i);
 10  EXCEPTION
 11     WHEN dml_errors THEN
 12                  dump_bulk_exceptions;
 13  end;
 14  /
Number of errors is 3
Error 1 occurred during iteration 2
Oracle error is ORA-01476: divisor is equal to zero
Error 2 occurred during iteration 6
Oracle error is ORA-01476: divisor is equal to zero
Error 3 occurred during iteration 10
Oracle error is ORA-01476: divisor is equal to zero

PL/SQL procedure successfully completed.

just have your routine copy out the sql%bulk_exceptions data before it uses FORALL itself!

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Great, thanks

James, July 10, 2007 - 1:47 pm UTC

Nicely illustrated response Tom. So in a nutshell, the SQL%BULK_EXCEPTIONS collection is globally available within the executing Session.

July 10, 2007 - 8:03 pm UTC

correct!

Limit to BULK_EXCEPTIONS

Suren, July 09, 2008 - 11:18 am UTC

It seems there is a limit of 65536 for collecting records with DML errors in BULK_EXCEPTIONS. I tried with 65537 rows violating primary key to be inserted in a table using FORALL .. SAVE EXCEPTIONS hoping that all will be collected in SQL%BULK_EXCEPTIONS however found that only last one was collected as if the earlier lot of 65536 records was overwritten. Then I tried with only 65536 records and found that all are collected in SQL%BULK_EXCEPTIONS. Is there any way to increase this limit? Actully I want to insert around 300000 records all of which could violate Primary Key and subsequently needs to be updated.

Regards.

July 09, 2008 - 2:13 pm UTC

there is no way you want to have 300,000 records in an array - do you understand the amount of pga you are using?

one hundred - great.
five hundred - maybe ok.
one thousand - really rare.

300,000 at once - I would not allow your code into production.

ops$tkyte%ORA10GR2> @mystat "pga memory max"
ops$tkyte%ORA10GR2> set echo off

NAME                                               VALUE
--------------------------------------------- ----------
session pga memory max                           1044052

ops$tkyte%ORA10GR2> declare
  2          type array is table of big_table.big_table%rowtype;
  3          l_data array;
  4  begin
  5          select * bulk collect into l_data from big_table.big_table where rownum <= 300000;
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> @mystat2
ops$tkyte%ORA10GR2> set echo off

NAME                                               VALUE DIFF
--------------------------------------------- ---------- ------------------
session pga memory max                         174190164      173,146,112

big table is of average width there, a copy of all objects over and over with one extra primary key column (number).

I would not permt a program to eat up 170mb in an array - that is just wasteful - and it is really hard to manage a data structure that big - you get diminishing marginal returns.

Please - be a little more "reasonable" in your data structures there. I'm saying this in general - in all of your code.

short of that, use dml error logging if you expect 10's of thousands of errors like that - the data will be logged into a table, you can "fix it" and then bulk insert it from there.

COMMIT in Forall with exception.

Vikram, May 07, 2010 - 7:38 am UTC

Hi Tom,
The top article explained a lot about the usage, thanks.

Suppose if you would have given a commit after delete how would that work, i.e. I tried the below in 10g but the session hangs:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1);
errors NUMBER;
dml_errors EXCEPTION;
PRAGMA exception_init(dml_errors, -24381);
BEGIN
FORALL i IN num_tab.FIRST..num_tab.LAST SAVE EXCEPTIONS
DELETE FROM emp WHERE sal > 500000/num_tab(i);
COMMIT;
EXCEPTION
WHEN dml_errors THEN
dump_bulk_exceptions;
end;
/
----
Number of errors is 4
Error 1 occurred during iteration 2
Oracle error is ORA-01476: divisor is equal to zero
Error 2 occurred during iteration 6
Oracle error is ORA-01476: divisor is equal to zero
Error 3 occurred during iteration 8
Oracle error is ORA-01013: user requested cancel of current operation
Error 4 occurred during iteration 10
Oracle error is ORA-01476: divisor is equal to zero
----

Does it commit on every iteration and check exception, or it allows all the iterations and then commit.

Regards,
Vikram

May 07, 2010 - 8:46 am UTC

that commit doesn't happen in your example, if you hit an exception, it jumps over the commit to the exception block.

Not sure why you think it "hangs", you got output - obviously it did not hang.

your code is:

begin
statement 1: forall;
statement 2: commit;
exception handler for statement 1 and statement 2:
when dml_errors....

if statement 1 fails, then the exception block happens, but not statement 2.

forall is the statement, the commit is NOT 'inside' the forall, it is AFTER the forall.

Exception handling for bulk operation using FORALL in INDICES of

Siby, December 15, 2010 - 7:20 am UTC

Hi Tom,

In the following example, the error_index tells me that error occurred in the 2nd, 4th and 5th iteration (starting from 1) of tblnum. That is fine. Is there a way to determine, the actual index values where the error occurred (in my case - 5,8 and 10)?

DECLARE
TYPE tnum IS TABLE OF NUMBER index by binary_integer;
tblnum tnum;
bulk_errors exception;
PRAGMA EXCEPTION_INIT(bulk_errors, -24381);
BEGIN
tblnum (1) := 1;
tblnum (5) := 0;
tblnum (7) := 4;
tblnum (8) := 0;
tblnum (10) := 0;
begin
FORALL I IN INDICES OF tblnum save exceptions
INSERT INTO table
VALUES (12 / tblnum (I) );
COMMIT;
exception when bulk_errors then
FOR i in 1..sql%bulk_exceptions.count
loop
DBMS_OUTPUT.PUT_LINE ( sql%bulk_exceptions(i).error_index );
end loop;
END;
END;

December 15, 2010 - 11:45 am UTC

ops$tkyte%ORA11GR2> DECLARE
  2     TYPE tnum IS TABLE OF NUMBER index by binary_integer;
  3     tblnum   tnum;
  4     bulk_errors exception;
  5     PRAGMA EXCEPTION_INIT(bulk_errors, -24381);
  6  <b>
  7     l_indices  tnum;
  8     l_curr     binary_integer;</b>
  9   BEGIN
 10     tblnum (1) := 1;
 11     tblnum (5) := 0;
 12     tblnum (7) := 4;
 13     tblnum (8) := 0;
 14     tblnum (10)    := 0;
 15     begin
 16         FORALL I IN INDICES OF tblnum save exceptions
 17                  INSERT INTO t
 18                    VALUES (12 / tblnum (I) );
 19         COMMIT;
 20     exception when bulk_errors then
 21         <b>l_curr := tblnum.first;
 22         while (l_curr is not null)
 23         loop
 24            l_indices(l_indices.count+1) := l_curr;
 25            l_curr := tblnum.next(l_curr);
 26         end loop;
 27  </b>
 28         FOR i in 1..sql%bulk_exceptions.count
 29         loop
 30             DBMS_OUTPUT.PUT_LINE
 31             ( sql%bulk_exceptions(i).error_index || ', ' ||
 32    <b>           l_indices(sql%bulk_exceptions(i).error_index) );
 33     </b>    end loop;
 34     END;
 35  END;
 36  /
2, 5
4, 8
5, 10

PL/SQL procedure successfully completed.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Great, thanks

Limit to BULK_EXCEPTIONS

COMMIT in Forall with exception.

Exception handling for bulk operation using FORALL in INDICES of

More to Explore

PL/SQL demos

PL/SQL docs

Passing the SQL%BULK_EXCEPTIONS Collection to a Procedure

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Great, thanks

Limit to BULK_EXCEPTIONS

COMMIT in Forall with exception.

Exception handling for bulk operation using FORALL in INDICES of

More to Explore

PL/SQL demos

PL/SQL docs