Home » SQL & PL/SQL » SQL & PL/SQL » Exception: ORA-01438: value larger than specified precision allowed for this column
Exception: ORA-01438: value larger than specified precision allowed for this column [message #580168] Thu, 21 March 2013 01:51 Go to next message
abhinavbhasker
Messages: 3
Registered: September 2012
Location: Bangalore
Junior Member
Hello Guys,

I am trying to do bulk insert into a table. Attached is the script I am running:

But when I run this script I get exception : 'ORA-01438: value larger than specified precision allowed for this column'.

I have checked in my soucre table as well as in inserting table, everything looks fine to me.

Could some one please help me know how to handle such exception, which could actually tell me for what column and what data it is throwing exception.

Thanks in advance.

Regards
Abhinav

[EDITED by LF: fixed topic title typo; was "Excepyion"]

[Updated on: Thu, 21 March 2013 03:04] by Moderator

Report message to a moderator

Re: Excepyion: ORA-01438: value larger than specified precision allowed for this column [message #580170 is a reply to message #580168] Thu, 21 March 2013 02:21 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Welcome to the Orafaq!

From the error message you posted, it seems that the error is coming from numeric column.

You select the MAX values from all the numeric values you are inserting and in this way you can find the problem.

regards,
Delna
Re: Excepyion: ORA-01438: value larger than specified precision allowed for this column [message #580171 is a reply to message #580168] Thu, 21 March 2013 02:25 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
whenever you post any issue, provide proper test case along with create table, insert into and the required output in proper format.

regards,
Delna
Re: Excepyion: ORA-01438: value larger than specified precision allowed for this column [message #580180 is a reply to message #580171] Thu, 21 March 2013 03:06 Go to previous messageGo to next message
Littlefoot
Messages: 19889
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You might benefit from removing WHEN OTHERS exception handler because, in your case, it does nothing smart.
Re: Excepyion: ORA-01438: value larger than specified precision allowed for this column [message #580181 is a reply to message #580171] Thu, 21 March 2013 03:07 Go to previous messageGo to next message
abhinavbhasker
Messages: 3
Registered: September 2012
Location: Bangalore
Junior Member
Thanks for reply. I found the issue. It was with my scalar type definition only.

But, My question is still the technique to handle such exception, which could actually tell me for what column and what data it is throwing exception. I know there is something like SAVE EXCEPTION, but i am not sure how that will work in this case.

Regards
Abhinav
Re: Excepyion: ORA-01438: value larger than specified precision allowed for this column [message #580186 is a reply to message #580181] Thu, 21 March 2013 03:35 Go to previous messageGo to next message
cookiemonster
Messages: 11285
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you wrote that procedure as a straight insert select, like it should be, you wouldn't have to worry about getting that error.
Re: Excepyion: ORA-01438: value larger than specified precision allowed for this column [message #580189 is a reply to message #580186] Thu, 21 March 2013 03:44 Go to previous messageGo to next message
abhinavbhasker
Messages: 3
Registered: September 2012
Location: Bangalore
Junior Member
Hi Cookiemonster,

The main idea for such insert was to reduce lock time on Table and also my direct INSERT using select was taking longer time. But with this approach time taken is less.
I was just thinking if there is a way to handle EXCEPTION during bulk insert which can tell me at what column and for what data, what the error\exception is. I know there is SAVE EXCEPTION, but i donot know how to use the same for my case.

Regards
Abhinav
Re: Excepyion: ORA-01438: value larger than specified precision allowed for this column [message #580196 is a reply to message #580189] Thu, 21 March 2013 03:55 Go to previous message
cookiemonster
Messages: 11285
Registered: September 2008
Location: Rainy Manchester
Senior Member
Lock time on the table? I think you've got oracle confused with sqlserver.
What locking do you think is happening?

All things being equal a direct insert should be noticabely faster, if it's not I'd look at tuning the insert, not going to bulk collect.

As for the error, if the error message doesn't say then all you can do is test each column individually, and that's way more hassle than it's worth.

Save exceptions will at least tell you which rows had problems.
Previous Topic: retrieving data from pl sql table
Next Topic: PLS-00103 Error while compiling a package
Goto Forum:
  


Current Time: Fri Dec 19 14:36:55 CST 2014

Total time taken to generate the page: 0.15468 seconds