Thanks for the question, Shimmy.

Asked: September 16, 2016 - 8:10 pm UTC

Last updated: September 17, 2016 - 2:59 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

Hi,

Is there anyway(easy way) to find/save the column name that's causing ORA-01438 like we get for ORA-12899?

Thank you

SQL> 
SQL> SET LINESIZE 300
SQL> COLUMN ORA_ERR_NUMBER$ FORMAT 9999999
SQL> COLUMN ORA_ERR_MESG$ FORMAT A100
SQL> COLUMN ORA_ERR_ROWID$ FORMAT A15
SQL> COLUMN ORA_ERR_TAG$ FORMAT A15
SQL> COLUMN ID FORMAT A15
SQL> COLUMN VAL FORMAT A15
SQL> 
SQL> SELECT BANNER FROM V$VERSION;

BANNER                                                                                                                                                                                                                                                                                                      
--------------------------------------------------------------------------------                                                                                                                                                                                                                            
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production                                                                                                                                                                                                                                
PL/SQL Release 12.1.0.2.0 - Production                                                                                                                                                                                                                                                                      
CORE 12.1.0.2.0 Production                                                                                                                                                                                                                                                                                  
TNS for Linux: Version 12.1.0.2.0 - Production                                                                                                                                                                                                                                                              
NLSRTL Version 12.1.0.2.0 - Production                                                                                                                                                                                                                                                                      

SQL> 
SQL> DROP TABLE STK_TBL ;

Table dropped.

SQL> 
SQL> DROP TABLE ERR$_STK_TBL ;

Table dropped.

SQL> 
SQL> CREATE TABLE STK_TBL
  2  (ID  NUMBER(5,1)      NOT NULL,
  3   VAL VARCHAR(10)      NOT NULL);

Table created.

SQL> 
SQL> EXEC DBMS_ERRLOG.CREATE_ERROR_LOG (DML_TABLE_NAME => 'STK_TBL');

PL/SQL procedure successfully completed.

SQL> 
SQL> INSERT INTO STK_TBL
  2  (ID, VAL)
  3  SELECT CASE WHEN LEVEL = 5 THEN
  4                 LEVEL * 1000000
  5              ELSE
  6                 LEVEL
  7         END ID,
  8         LPAD(LEVEL, 10, '0') VAL
  9  FROM DUAL
 10  CONNECT BY LEVEL <= 10
 11  LOG ERRORS INTO ERR$_STK_TBL
 12  REJECT LIMIT UNLIMITED;

9 rows created.

SQL> 
SQL> INSERT INTO STK_TBL
  2  (ID, VAL)
  3  SELECT LEVEL ID,
  4         CASE WHEN LEVEL = 5 THEN
  5                 LPAD(LEVEL, 11, '0')
  6              ELSE
  7                 LPAD(LEVEL, 10, '0')
  8         END VAL
  9  FROM DUAL
 10  CONNECT BY LEVEL <= 10
 11  LOG ERRORS INTO ERR$_STK_TBL
 12  REJECT LIMIT UNLIMITED;

9 rows created.

SQL> 
SQL> SELECT *
  2  FROM ERR$_STK_TBL;

ORA_ERR_MESG$                                                                                        ID              VAL                                                                                                                                 
---------------------------------------------------------------------------------------------------- --------------- ---------------                                                                                                                     
ORA-01438: value larger than specified precision allowed for this column                             5000000         0000000005                                                                                                                          
ORA-12899: value too large for column "SCOTT"."STK_TBL"."VAL" (actual: 11, maximum: 10)              5               00000000005

and Connor said...

Sorry, not that I know of. Generally there are various methods of capturing a failed SQL statement (auditing, event trace etc), but you ultimately still end up with a SQL-level result, not the columns within it.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Exception throw in cases of valid data.

John Carroll, January 25, 2017 - 7:27 pm UTC

We recently had a case using Entity Framework where the edmx was created from the database. A linq query which retrieved data from the database threw this exception. Since the data was stored in the database, clearly the data was valid for the specified precision and the exception was thrown in error. It was necessary to adjust the precision to a greater value than required to allow the query to work.

This worked for me

Peter Nosko, August 14, 2017 - 9:43 pm UTC

Problem:
1- INSERT INTO <table> <query>;
2- Get ORA-01438

Solution:
1- Create a view from <query>
2- DIFF the view and the <table>

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Exception throw in cases of valid data.

This worked for me

More to Explore

PL/SQL demos

PL/SQL docs

ORA-01438 Finding Column Name...

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Exception throw in cases of valid data.

This worked for me

More to Explore

PL/SQL demos

PL/SQL docs