ORA-01438 Which Column

ORA-01438: value larger than specified precision allowed for this column

You will receive this error while trying to insert big Value In Column with Specific Range , But How Could You know Which Column is it i will some hint that could help you to do that :

Hint #1 :

1-ORA-01438  : For Numeric Value
2-ORA-12899  : For Varchar2 Value

Hint #2 :

Enable Audit On know which One of these Column caused the error

SQL > Create table test as select * from scott.dept

SQL> desc dept
Name Null? Type
----------------------------------- -------- ------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
 
SQL> audit insert on Osama.test whenever not successful;
 
Audit succeeded.
 
SQL> insert into osama.test values(2000,'Osama','Osama');
insert into scott.dept values(2000,'osama','Osama')
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this
column
 
 
SQL> select sql_text,returncode from dba_audit_trail
2 where owner='OSAMA' and obj_name='TEST';
 
SQL_TEXT
----------------------------------------------------------------------
RETURNCODE
----------
insert into scott.dept values(2000,'Osama','Osama')
1438

 Hint #3 :

Enable Tracing Level 1438

SQL > conn osama/osama ;
SQL> create table test as select * from scott.dept ;
 
SQL> select * from test ;
 
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
 
 
SQL> alter system set events='1438 trace name Errorstack forever,level 10';
 
 
SQL> insert into test values (100000000000000000,'osama','JOR');
insert into test values (100000000000000000,'osama','JOR')
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
 
In Trace File :
 
ORA-01438: value larger than specified precision allowed for this column
Current SQL statement for this session:
insert into test values (100000000000000000,'osama','JOR')

Thank you
Osama Mustafa

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.