ORA-01438 Solution

Solution:

(1) Track and execute the inserted SQL/stored procedure, find the row that causes the culprit, and insert the row again after modification.

(2) The simplest way is to increase the precision of the target field.

 

Because the specific table and specific fields cannot be located, You need to track errors.

1. Create a table first.

SQL> create table t (Limit number ));

Table created

If the precision is 4 and the scale is 2, the maximum number of digits in an integer is 2, and the number of decimal places occupies 2 places.

2. Insert the test

SQL> insert into t values (1000000 );

Error:

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

Error

3. Perform system settings change and TRACE

Alter system set events = '2014 trace name Errorstack forever, level 10 ';

4. Create a new session (I think this change only takes effect for the subsequent session and does not take effect for the session before setting.

SQL> insert into tony. t values (100000000 );

Error:

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

5. Background System

[Oracle @ localhost udump] $ pwd

/Home/oracle/admin/ORCL/udump

[Oracle @ localhost udump] $ ls

ORCL_ora_5035.trc

You can also use an SQL script to find the generated trace file:

SELECT a. VALUE | B. symbol | c. instance_name | '_ ora _' | d. spid | '. trc'
Trace_file
FROM (SELECT VALUE
FROM v $ parameter
WHERE name = 'user _ dump_dest '),
(Select substr (VALUE,-6, 1) symbol
FROM v $ parameter
WHERE name = 'user _ dump_dest ') B,
(SELECT instance_name FROM v $ instance) c,
(SELECT spid
FROM v $ session s, v $ process p, v $ mystat m
WHERE s. paddr = p. addr AND s. sid = m. sid AND m. statistic # = 0) d

Find the TRACE file. Of course, I cleared all the previous TRACE files first. If there are many TRACE files, you can use

Ls-lrt sorts the latest TRC files.

6. view the TRACE file

[Oracle @ localhost udump] $ more ORCL_ora_5035.trc

The file contains a lot of content. I only use the first few lines.

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

Current SQL statement for this session:

Insert into tony. t

Values (1, 100000000)

Find the statement.

Add one sentence if you want to DISABLE it.

SQL> alter system set events = '1438 trace name Errorstack off ';

System altered

 

Attachment: Table 2-2 Storage of Scale and Precision

Actual Data	Specified	Stored
123.89	NUMBER	123.89
123.89	NUMBER (3)	124
123.89	NUMBER (6, 2)	123.89
123.89	NUMBER (6, 1)	123.9
123.89	NUMBER (3)	Exceeds precision
123.89	NUMBER (4, 2)	Exceeds precision
123.89	NUMBER (6,-2)	100
. 01234	NUMBER (4, 5)	. 01234
. 00012	NUMBER (4, 5)	. 00012
. 000127	NUMBER (4, 5)	. 00013
. 0000012	NUMBER (2, 7)	. 0000012
. 00000123	NUMBER (2, 7)	. 0000012
1.2e-4	NUMBER (2, 5)	0.00012
1.2e-5	NUMBER (2, 5)	0.00001

SPFILE error causing database startup failure (ORA-01565)

ORA-01172, ORA-01151 error handling

ORA-00600 [2662] troubleshooting