column precision exceeded. ORA-01438 is so unmeaningful

Question

I am not sure if this is a question to StackExchange DB Admin group. please let me know if its not.

Let's suppose you have a Java batched prepared statement writing data to database. If one of the columns has a value larger than specified precision it throws ORA-01438 exception. Is there any way to find out which column is that?

Answer 1

It would indeed be nice if Oracle provided more information for this error. Until then here is an idea. You could call a package to do the insert and the package could verify each of the values separately as follows:

drop table t1;
create table t1 (c1 number(3), c2 number(2), c3 number(1));


CREATE OR REPLACE PACKAGE p1 AS

   Procedure InsertData(iValue1 In Number, iValue2 In Number, iValue3 In Number);

END;
/

CREATE OR REPLACE PACKAGE BODY p1 AS

Procedure InsertData(iValue1 In Number, iValue2 In Number, iValue3 In Number) Is
   vValue1 T1.C1%Type;
   vValue2 T1.C2%Type;
   vValue3 T1.C3%Type;
   ePrecisionExceeded Exception;
   PRAGMA EXCEPTION_INIT(ePrecisionExceeded, -1438);
Begin
   INSERT INTO T1 VALUES (iValue1, iValue2, iValue3);
Exception
   When ePrecisionExceeded Then
      vValue1 := iValue1;
      vValue2 := iValue2;
      vValue3 := iValue3;
End;

END;
/

execute P1.InsertData(100,102,103);

The check is only done when the exception is raised, so the only overhead is the variable declaration. You will know which value caused the exception based on the line number. If this were the only reason to move logic into a package it might not be worth it, but since there are many other benefits, it is worth considering.