OraFAQ Forum: SQL & PL/SQL » Exception: ORA-01438: value larger than specified precision allowed for this column

Home » SQL & PL/SQL » SQL & PL/SQL » Exception: ORA-01438: value larger than specified precision allowed for this column

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Exception: ORA-01438: value larger than specified precision allowed for this column [message #580168]

Thu, 21 March 2013 01:51

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"]

Attachment: Anonymus_Block.sql
(Size: 13.80KB, Downloaded 1111 times)

[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

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

Report message to a moderator

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

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

Report message to a moderator

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

Littlefoot
Messages: 21826
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.

Report message to a moderator

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

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

Report message to a moderator

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

cookiemonster
Messages: 13967
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.

Report message to a moderator

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

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

Report message to a moderator

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

cookiemonster
Messages: 13967
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.

Report message to a moderator

Previous Topic:	retrieving data from pl sql table
Next Topic:	PLS-00103 Error while compiling a package

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Aug 22 07:52:25 CDT 2025