ORA-01438 Processing Method

ORA-01438 Processing Method
Solution: (1) Track and execute the inserted SQL/stored procedure, locate 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, we need to track errors. 1. Create a table first.

SQL> create table t (many number(4,2));Table created

 

If the precision is 4 and the scale is 2, it means that the number of digits of an integer is 2 at most, and the number of decimal places occupies 2 places.

SQL> insert into t values(1000000);

 

Error: ORA-01438: value larger than specified precision allowed for this column error 3. Make system settings change for TRACE

alter system set events='1438 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, in background System

[oracle@localhost udump]$ pwd/home/oracle/admin/ORCL/udump[oracle@localhost udump]$ lsORCL_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') a,        (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 traces first. If there are many traces, use ls-lrt to sort and find the most recent TRC file. 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 (100000000) successfully found the statement. The last one can be used if you want to DISABLE

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

 

Attachment: Table 2-2 Storage of Scale and Precision Actual Data Specified As Stored As 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 (123.89) exceeds precision 100 NUMBER (6,-2. 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 (0.00012) 0.00001 1.2e-5 NUMBER ()