Capture the column causing ORA-01438: value larger than specified precision allowed for this column

Question

I am using Oracle 12C. If my insert statement fails with ORA-01438 how do I capture the column causing the error. For example see below code -

create table T1  ( c1 number(3,2) , c2 number(4,2), c3 number(5,2) );

insert into T1 ( c1 ,c2, c3)  values ( 1, 22,333); --good 

commit;

insert into T1 ( c1 ,c2, c3)  values ( 1, 22,3330); --ORA-01438 for c1

insert into T1 ( c1 ,c2, c3)  values ( 1, 222,3330); ---ORA-01438 for c1, c2

insert into T1 ( c1 ,c2, c3)  values ( 11, 222,3330); --ORA-01438 for c1, c2, c3

I even tried DBMS_ERRLOG.CREATE_ERROR_LOG but I cannot identify the column causing bad data. Please see below code -

EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('T1', 'T1_bad');

insert into T1 ( c1 ,c2, c3)   values ( 11, 222,3330);

log errors into T1_bad;

SELECT  * FROM T1_bad;

Answer 1

You can set the event to trace the culprit:

ALTER SESSION SET EVENTS='1438 TRACE NAME ERRORSTACK FOREVER, LEVEL 12';

Following code is showing th demo for the same:

SQL> create table test (name varchar2(1), name1 varchar2(1));

Table created.

SQL> ALTER SESSION SET EVENTS='1438 TRACE NAME ERRORSTACK FOREVER, LEVEL 12';

Session altered.

SQL> insert into test values('ad','a');
insert into test values('ad','a')
                        *
ERROR at line 1:
ORA-12899: value too large for column "TEJASH"."TEST"."NAME" (actual: 2, maximum:
1)


SQL> insert into test values('d','ad');
insert into test values('d','ad')
                            *
ERROR at line 1:
ORA-12899: value too large for column "TEJASH"."TEST"."NAME1" (actual: 2, maximum:
1)


SQL>

Cheers!!

Answer 2

One solution I found is the usage of dynamic sql (How can I get position of an error in Oracle SQL query?) and then DBMS_SQL.LAST_ERROR_POSITION to get the error position:

DECLARE
  c   INTEGER := DBMS_SQL.open_cursor ();
  erg INTEGER;
  l_sql VARCHAR2(4000) := 'INSERT INTO test(id, description) VALUES (3333, ''Test'')';
BEGIN
  DBMS_SQL.parse (c, l_sql, DBMS_SQL.native);
  erg := DBMS_SQL.EXECUTE(c);
  DBMS_SQL.close_cursor (c);
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(sqlerrm);
    dbms_output.put_line(l_sql);
    dbms_output.put_line(LPAD('*', DBMS_SQL.LAST_ERROR_POSITION+1));
    DBMS_SQL.close_cursor (c);
END;

Output:

ORA-01438: ...
INSERT INTO test(id, description) VALUES (3333, 'Test')
                                          *