Take the 2-minute tour ×
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. It's 100% free, no registration required.

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. –  lordlupine 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. –  lordlupine Nov 9 '12 at 16:32
1  
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

1 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.

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

 
discard

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.