Handling and Reporting Exceptions - Bulk Binding in Native Dynamic SQL

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);