ORA-01438 Processing Method

Source: Internet
Author: User

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 ()

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.