Handling and Reporting Exceptions

Consider a program to insert the elements in a PL/SQL collection into a database table. It’s possible that some elements might fail and that the designer would regard this as a non-fatal error and want to continue to insert subsequent elements. The explicit row by row implementation would handle the exception, and probably record it for subsequent review thus…

declare /* relies on... create table t ( text varchar2(3) ) */
  type words_t is table of varchar2(10);
  words words_t :=
    words_t ( 'dog', 'fish', 'cat', 'ball', 'bat', 'spoke', 'pad' )
    /* 'ball' and 'spoke' will raise ORA-01401 */;
  n integer := 0;

  type error_indexes_t is table of integer index by binary_integer;
  error_indexes error_indexes_t;
  type error_codes_t is table of varchar2(255) index by binary_integer;
  error_codes error_codes_t;
begin
  for j in words.first..words.last
    loop

      begin
        insert into t ( text ) values ( words(j) );
      exception when others then
      n := n+1; error_indexes(n) := j; error_codes(n) := SQLERRM;
      end;
    end loop;

  for j in 1..n
    loop

      Dbms_Output.Put_Line ( error_indexes(j) || ': ' || error_codes(j) );
    end loop;
end;

Pre-Oracle9i there was no way to continue after a row-wise exception in the bulk binding approach…

forall j in words.first..words.last
  insert into t ( text ) values ( words(j) );

…and the effect of the ORA-01401 on [what would be] just some of the rows meant that no rows are inserted.

Oracle9i introduces the save exceptions syntax and the corresponding “ORA-24381: error(s) in array DML” exception. This allows the implied loop to continue after row-wise failure…

forall j in words.first..words.last
  save exceptions /* new at 9i */
  insert into t ( text ) values ( words(j) );

…resulting in the successful insert of 'dog', 'cat', 'bat', 'pad'.

To complement this construct, the sql%bulk_exceptions collection allows reporting of the erroring rows in the exception handler for ORA-24381 thus…

declare
  type words_t is table of varchar2(10);
  words words_t :=
    words_t ( 'dog', 'fish', 'cat', 'ball', 'bat', 'spoke', 'pad' );
  bulk_errors exception;
  pragma exception_init ( bulk_errors, -24381 );
begin
  forall j in words.first..words.last
    save exceptions
    insert into t ( text ) values ( words(j) );
exception when bulk_errors then

  for j in 1..sql%bulk_exceptions.count
  loop
    Dbms_Output.Put_Line (
    sql%bulk_exceptions(j).error_index || ', ' ||
    Sqlerrm(-sql%bulk_exceptions(j).error_code) );
  end loop;
end;

…which produces…

2: ORA-01401: inserted value too large for column
4: ORA-01401: inserted value too large for column
6: ORA-01401: inserted value too large for column

The construct is also supported in native dynamic SQL thus…

forall j in words.first..words.last
  save exceptions
  execute immediate 'insert into t ( text ) values ( :the_word )'
  using words(j);