You Asked

Hi TOM,

I would be really thank full to you if you provide a clear and detailed working mechanism of save exceptions in oracle.

Thank you

and Chris said...

Save exceptions allows Oracle to continue processing if one element of an array throws an error.

Without this processing stops immediately and everything you've added so far is lost:

create table t (
  x varchar2(10)
);

declare
  type tp is table of varchar2(100) index by pls_integer;
  arr  tp;
begin
  arr(1) := 'a';
  arr(2) := 'b';
  arr(3) := 'this is waaaaaay tooo loooooong';
  arr(4) := 'c';

  forall i in arr.first .. arr.last 
    insert into t values (arr(i));
end;
/

ORA-12899: value too large for column "CHRIS"."T"."X" (actual: 31, maximum: 10)

select count(*) from t;

COUNT(*)  
0

Add the save exceptions clause and you'll still get an exception. But the valid rows remain in the table:

declare
  type tp is table of varchar2(100) index by pls_integer;
  arr  tp;
begin
  arr(1) := 'a';
  arr(2) := 'b';
  arr(3) := 'this is waaaaaay tooo loooooong';
  arr(4) := 'c';

  forall i in arr.first .. arr.last save exceptions
    insert into t values (arr(i));
end;
/

ORA-24381: error(s) in array DML

select count(*) from t;

COUNT(*)  
3

Unfortunately the exception gives no clues to the problem. To figure out what went wrong and for which row, you need to inspect sql%bulk_exceptions. This stores:

- A count of the errors (sql%bulk_exceptions.count)
- An array of records for the invalid elements
- sql%bulk_exceptions(n).error_index is the element that threw the exception
- sql%bulk_exceptions(n).error_code is the ORA error code

So to see what went wrong, you need to:

- Trap ORA-24381
- Loop through the exceptions, getting their details:

declare
  type tp is table of varchar2(100) index by pls_integer;
  arr  tp;
  
  forall_err exception;
  pragma exception_init(forall_err, -24381);  
begin
  arr(1) := 'a';
  arr(2) := 'b';
  arr(3) := 'this is waaaaaay tooo loooooong';
  arr(4) := 'c';

  forall i in arr.first .. arr.last save exceptions
    insert into t values (arr(i));

exception
  when forall_err then
    dbms_output.put_line(sql%bulk_exceptions.count || ' errors');
    for i in 1 .. sql%bulk_exceptions.count loop
      dbms_output.put_line (
        'Array item: ' || sql%bulk_exceptions(i).error_index || chr(10) ||
        ' with value: ' || arr(sql%bulk_exceptions(i).error_index) || chr(10) ||
        'threw: ' || sqlerrm(-sql%bulk_exceptions(i).error_code)
      );
    end loop;
end;
/

PL/SQL procedure successfully completed.
1 errors
Array item: 3
 with value: this is waaaaaay tooo loooooong
threw: ORA-12899: value too large for column  (actual: , maximum: )

Note in the call to sqlerrm to get the error message text you need to negate the error code!

For further reading on this, see:

http://docs.oracle.com/database/122/LNPLS/plsql-optimization-and-tuning.htm#GUID-DAF46F06-EF3F-4B1A-A518-5238B80C69FA
http://www.oracle-developer.net/display.php?id=419
http://www.oracle.com/technetwork/issue-archive/2009/09-mar/o29plsql-085126.html

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