Handling the error ORA-01438: value larger than specified precision allowed for this column.
This error happens when inserting or updating records into Oracle tables. The numeric value that user / process tries to place is exceeding the precision defined for the column.
For example:
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as someschema
SQL> create table SOMETABLE (a number(5,2), b number(3,2), c number(5));
Table created
SQL> insert into SOMETABLE values (2.0, 53.34, 123456);
insert into SOMETABLE values (2.0, 53.34, 123456)
ORA-01438: value larger than specified precision allowed for this column
SQL>
When a user runs some stand alone PL/SQL queries it’s not difficult to identify the value that causes the error.
The issue seems more complex when the error comes from a process that inserts and/or updates a few hundreds / thousands of records in a table. How to know the exact record and/or value that leads to the problem?
The first approach is to create a copy of the original table, but with increased precision(s) for numeric columns. Trying to insert / update values from the “improved” changed table to the original one, combining the records could point to the problematic record(s) and/or value(s).
For example:
SQL> create table SOMETABLEPLUS as select * from SOMETABLE where 1 = 2;
Table created
SQL> alter table SOMETABLEPLUS modify b number(4, 2);
Table altered
SQL> alter table SOMETABLEPLUS modify c number(6);
Table altered
SQL> insert into SOMETABLEPLUS values (2.0, 53.34, 123456);
1 row inserted
SQL> commit;
Commit complete
This way is easy enough, transparent and useful sometimes. It can help to find out the reason for the ORA-01438 error. From the other hand, it’s a sort of some manual activity.
The second approach that usually comes to mind is to debug the application side, to place some prints, to check some values that come from application to understand the behavior leading to the error. The downside of this method is that you have to access and manipulate the application code. I think you’ll agree with me, it’s a sort of manual activity too. After all, we’re hi-tech .
And the Oracle has something more powerful to suggest.
The idea is to get trace of insert(s)/update(s) and to catch the error precisely. It’s possible to turn on the trace on the level of session or on the level of system.
Assuming we have a process with no any access inside. It runs kind of isolated thread that we can’t “incapsulate” or “inject” something internally. In addition, the thread runs so quickly that we can’t even catch its session number in Oracle. All we have – it fails with the Oracle error ORA-01438.
In such case DBA can assist to find the problematic record(s).
Here are the steps to do. (I’ll illustrate it based on Oracle 11g release 1. You can do the same with other Oracle versions).
1. Connect to the Oracle instance when the problem happens as sysdba.
2. Turn on the following trace.
ALTER SYSTEM SET EVENTS=’1438 TRACE NAME ERRORSTACK FOREVER, LEVEL 12?;
It should be done for a short period of time, when reconstructing the problem, because invoked trace files will occupy more and more disk space while this event trace is turned on.
3. Re-run the process that causes the Oracle error ORA-01438.
4. Be sure to turn the activated trace off. Failure to perform this step will cause Oracle server file system to be overflowed with huge trace files.
ALTER SYSTEM SET EVENTS=’1438 TRACE NAME ERRORSTACK OFF’;
5. Find out where the trace files for the Oracle server are placed on:
SQL> show parameter user_dump_dest
NAME TYPE VALUE
———————————— ———– ——————————
user_dump_dest string /u01/app/oracle/diag/rdbms/dev
11g/DEV11G/trace
SQL>
6. Get to the dump destination directory and search the specific trace that has the error logged:
[oracle@localhost trace]$ cd /u01/app/oracle/diag/rdbms/dev11g/DEV11G/trace/
[oracle@localhost trace]$ grep -l 1438 *
alert_DEV11G.log
DEV11G_ora_8771.trc
[oracle@localhost trace]$
7. Open the trace file(s) to get the brief information about the error.
Here we can find the exact timestamp the problem happens, exact session, service name, module name, action name that caused the error. More important you can see the exact SQL statement that caused the problem and its exact value(s). In addition, for more advanced Oracle users the trace contains call stack trace.
…
*** 2010-10-04 09:19:35.131
*** SESSION ID:(2077.13457) 2010-10-04 09:19:35.132
*** CLIENT ID:() 2010-10-04 09:19:35.132
*** SERVICE NAME:(DEV11G) 2010-10-04 09:19:35.132
*** MODULE NAME:(PL/SQL Developer) 2010-10-04 09:19:35.132
*** ACTION NAME:(SQL Window – New) 2010-10-04 09:19:35.132
—– Error Stack Dump —–
ORA-01438: value larger than specified precision allowed for this column
—– Current SQL Statement for this session (sql_id=6w3w8fyhg39a4) —–
insert into SOMETABLEPLUS values (2.0, 153.34, 12345)
—– Call Stack Trace —–
calling call entry argument values in hex
location type point (? means dubious value)
——————– ——– ——————– —————————-
skdstdst()+41 call kgdsdst() 000000000 ? 000000001 ?
7FFF488E3488 ? 7FFF488E1F70 ?
…
REFERENCE : http://dba-star.blogspot.com/2010/12/handling-error-ora-01438.html
