Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
I think this is a good fit for DBA.SE. I'm not sure however what the java tag has to do with this - is the prepared statement a Java one? – dezso Nov 9 '12 at 16:17
Yes. Its in java. – Srujan Kumar Gulla Nov 9 '12 at 16:19
Please check my edit about that. You can always edit your own question, too. – dezso Nov 9 '12 at 16:22
sure. removed java tag. – Srujan Kumar Gulla Nov 9 '12 at 16:32
Wait, wait, wait! If it is a Java prep stmt, than the tag should remain. If not, it should go. – dezso Nov 9 '12 at 16:34

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


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



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);
   INSERT INTO T1 VALUES (iValue1, iValue2, iValue3);
   When ePrecisionExceeded Then
      vValue1 := iValue1;
      vValue2 := iValue2;
      vValue3 := iValue3;


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.

share|improve this answer
Neat trick to only have the assignments done on exception thus raising the exception on the value/column causing the problem. – Colin 't Hart Apr 9 '13 at 10:30

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.