save exceptions is not capturing the complete error description for the ORA-01438

Question

The save exceptions is not capturing the complete error description for the ORA-01438: value larger than specified precision allowed for this column, it is throwing only the generic error, no column name and size captured. Any workaround can be integrated with this code for getting the complete column description associated with the error?

   create or replace procedure BULK_COLLECT_TEST_PROC
   as
   TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;

   l_tab t_bulk_collect_test_tab;
   l_error_count  NUMBER;
   ex_dml_errors EXCEPTION;
   PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);

   CURSOR c_data IS
   SELECT *
   FROM bulk_collect_test;

   BEGIN
   OPEN c_data;
   LOOP
   FETCH c_data
   BULK COLLECT INTO l_tab LIMIT 10000;
   EXIT WHEN l_tab.count = 0;    

   -- Perform a bulk operation.
   BEGIN
   FORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONS
   INSERT INTO exception_test
   VALUES l_tab(i);
   EXCEPTION
   WHEN ex_dml_errors THEN
   l_error_count := SQL%BULK_EXCEPTIONS.count;
   DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
   FOR i IN 1 .. l_error_count LOOP
     DBMS_OUTPUT.put_line('Error: ' || i || 
      ' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
      ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
   END LOOP;
   END;
   END LOOP;
   END;
   /    

   SET SERVEROUTPUT ON;
   EXEC BULK_COLLECT_TEST_PROC;

   Number of failures: 9001
   Error: 1 Array Index: 1000 Message: ORA-01438: value larger than specified precision allowed for 
   this column
   Error: 2 Array Index: 1001 Message: ORA-01438: value larger than specified precision allowed for 
   this column
   Error: 3 Array Index: 1002 Message: ORA-01438: value larger than specified precision allowed for 
   this column
   Error: 4 Array Index: 1003 Message: ORA-01438: value larger than specified precision allowed for 
   this column
   Error: 5 Array Index: 1004 Message: ORA-01438: value larger than specified precision allowed for 
   this column
   Error: 6 Array Index: 1005 Message: ORA-01438: value larger than specified precision allowed for 
   this column
   Error: 7 Array Index: 1006 Message: ORA-01438: value larger than specified precision allowed for 
   this column
   Error: 8 Array Index: 1007 Message: ORA-01438: value larger than specified precision allowed for 
   this column
   Error: 9 Array Index: 1008 Message: ORA-01438: value larger than specified precision allowed for 
   this column
   Error: 10 Array Index: 1009 Message: ORA-01438: value larger than specified precision allowed for 
   this column
   Error: 11 Array Index: 1010 Message: ORA-01438: value larger than specified precision allowed for 
   this column
   Error: 12 Array Index: 1011 Message: ORA-01438: value larger than specified precision allowed for 
   this column
   Error: 13 Array Index: 1012 Message: ORA-01438: value larger than specified precision allowed for 
   this column
   Error: 14 Array Index: 1013 Message: ORA-01438: value larger than specified precision allowed for 
   this column
   Error: 15 Array Index: 1014 Message: ORA-01438: value larger than specified precision allowed for 
   this column
   Error: 16 Array Index: 1015 Message: ORA-01438: value larger than specified precision allowed for 
   this column
   Error: 17 Array Index: 1016 Message: ORA-01438: value larger than specified precision allowed for 
   this column
   Error: 18 Array Index: 1017 Message: ORA-01438: value larger than specified precision allowed for 
   this column
   Error: 19 Array Index: 1018 Message: ORA-01438: value larger than specified precision allowed for 
   this column
   Error: 20 Array Index: 1019 Message: ORA-01438: value larger than specified precision allowed for 
   this column
   Error: 21 Array Index: 1020 Message: ORA-01438: value larger than specified precision allowed for 
   this column
   Error: 22 Array Index: 1021 Message: ORA-01438: value larger than specified precision allowed for 
   this column
   Error: 23 Array Index: 1022 Message: ORA-01438: value larger than specified precision allowed for 
   this column
   Error: 24 Array Index: 1023 Message: ORA-01438: value larger than specified precision allowed for 
   this column

Answer 1

As Oracle states

Add the SAVE EXCEPTIONS clause to your FORALL statement when you want the PL/SQL runtime engine to execute all DML statements generated by the FORALL, even if one or more than fail with an error. If you use INDICES OF, you will need to take some care to find your way back to the offending statement.

So, the SAVE EXCEPTIONS is doing what it is supposed to do, so running completely the whole FORALL without raising any issue until the end. Your issue here is the ORA-01438: value larger than specified precision allowed for this column. That exception won't ever inform the column affected. In order to capture the problematic column, you have some options.

SQL

In SQL, for example using sqlplus, you get an indication of which column is responsible as it is marked by a star, although the column is not named explicitly in the exception.

SQL> create table t ( c1 number(1) , c2 number(2) , c3 number(3) ) ;

Table created.

SQL> insert into t values ( 1 , 22, 3333 ) ;
insert into t values ( 1 , 22, 3333 )
                               *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

PL/SQL

In PL/SQL the exception is handled by the EXCEPTION module, which controls the SQLCODE and SQLERRM associated with the error. In this error there is no indication whatsoever about the column:

SQL> declare
  2  begin
  3  insert into t values ( 1 , 22, 3333 );
  4  commit;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
ORA-06512: at line 3

You might got help in this case but displaying the values in case of exception

SQL> declare
  2  v_1 number := 1;
  3  v_2 number := 22;
  4  v_3 number := 3333;
  5  begin
  6  insert into t values ( v_1 , v_2, v_3) ;
  7  commit;
  8  exception when others then
  9    dbms_output.put_line( v_1 || '-' || v_2 || '-' || v_3 );
 10    raise;
 11* end;
SQL> /
1-22-3333
declare
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
ORA-06512: at line 10
ORA-06512: at line 6

DML ERROR LOGGING

DML ERROR LOGGING is a feature that fits clearly in your scenario. You have to apply the clause LOG ERRORS INTO ERROR_TABLE after creating the error table.

Example

SQL> EXEC DBMS_ERRLOG.CREATE_ERROR_LOG (DML_TABLE_NAME => 'T' , err_log_table_name  => 'T_ERRORS' ) ;

PL/SQL procedure successfully completed.

 declare
   v_1 number := 1;
   v_2 number := 22;
   v_3 number := 3333;
   begin
   insert into t values ( v_1 , v_2, v_3) log errors into t_errors;
   commit;
   exception when others then
       raise;
  end;
  /

ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
ORA-06512: at line 10
ORA-06512: at line 6

SQL> col c1 for a10
SQL> col c2 for a10
SQL> col c3 for a10
SQL> select ORA_ERR_NUMBER$,ORA_ERR_MESG$,c1,c2,c3 from t_errors ;

ORA_ERR_NUMBER$
---------------
ORA_ERR_MESG$
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
C1         C2         C3
---------- ---------- ----------
           1438
ORA-01438: value larger than specified precision allowed for this column
1          22         3333

In your specific code, you must :

1.Create an ERROR TABLE for your exception_test table by using DBMS_ERRLOG.

EXEC DBMS_ERRLOG.CREATE_ERROR_LOG (DML_TABLE_NAME => 'EXCEPTION_TEST' , err_log_table_name  => 'T_ERR_EXCEPTION_TEST' );

2.Change the INSERT STATEMENT to add the clause LOG ERRORS INTO YOUR_ERROR_TABLE and remove the SAVE EXCEPTIONS part. Once you are using DML_ERROR_LOGGING all exceptions will be saved on the error log table

FORALL i IN l_tab.first .. l_tab.last 
   INSERT INTO exception_test
   VALUES l_tab(i) log errors into T_ERR_EXCEPTION_TEST;

UPDATE

A variant for your code

EXEC DBMS_ERRLOG.CREATE_ERROR_LOG (DML_TABLE_NAME => 'EXCEPTION_TEST' , err_log_table_name  => 'T_ERR_EXCEPTION_TEST' );

create or replace procedure BULK_COLLECT_TEST_PROC
   as
   TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;
   l_tab t_bulk_collect_test_tab;
   l_error_count  NUMBER;
   
   CURSOR c_data IS
   SELECT *
   FROM bulk_collect_test;

   BEGIN
   -- first truncate error table 
   execute immediate ' truncate table T_ERR_EXCEPTION_TEST drop storage ' ;
   -- collection and insert 
   OPEN c_data;
   LOOP
   FETCH c_data
   BULK COLLECT INTO l_tab LIMIT 10000;
   EXIT WHEN l_tab.count = 0;    

   -- Perform a bulk operation.
   BEGIN
   FORALL i IN l_tab.first .. l_tab.last 
   INSERT INTO exception_test VALUES l_tab(i) log errors into T_ERR_EXCEPTION_TEST ;
   END LOOP;
   -- count errors
   commit ;
   select count(*) into l_error_count from T_ERR_EXCEPTION_TEST;
   if l_error_count > 0 
   then 
       -- loop over the errors if you want to here by selecting the error 
       -- table
       null;
   end if;
   EXCEPTION 
   WHEN OTHERS THEN RAISE;
   END;
   END LOOP;
   END;
   /