业务前台报错插入工单失败:ORA-01438
ORA-01438: 值大于此列指定的允许精确度
后来和业务人员确认是一个number(a,b)类型的字段,这个字段引用了一个sequence,而这个sequence的最大值已经超过了该字段的精度,后通过重置sequence,并设置了maxvalue。如果不能重置sequence,那就只能修改字段精度了。
1、ora-01438不记录到alert中
[code]sys@JYPUBLIC>create table a_test (id number(4,2));
Table created.
sys@JYPUBLIC>insert into a_test values(1111);
insert into a_test values(1111)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
SQL> insert into a_test values(111);
insert into a_test values(111)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
SQL> insert into a_test values(11);
1 row created.
SQL> insert into a_test values(11.11111);
1 row created.
sys@JYPUBLIC>drop table a_test purge;
Table dropped.
sys@JYPUBLIC>exit
oracle@jyggdb1:/oracle/app/oracle/diag/rdbms/trace$ cat alert_1.log
Tue Apr 01 22:09:48 2014
create table a_test (id number(4,2))
Tue Apr 01 22:10:17 2014
drop table a_test purge[/code]
2、查看数据库中所有number(a,b)类型字段
[code]select distinct owner,TABLE_NAME,COLUMN_NAME,DATA_PRECISION,DATA_SCALE from dba_tab_columns where DATA_TYPE='NUMBER' and DATA_PRECISION<>0 and DATA_SCALE<>0 order by 1,2;
sys@JYCRM>col MAX_VALUE for 999999999999999999999
sys@JYCRM>r
1* select * from dba_SEQUENCES where SEQUENCE_NAME='I_WK_OPER$SEQ'
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------------------- ------------ - - ---------- -----------
S01 I_WK_OPER$SEQ 1 999999999999 1 N N 200 1000298052[/code]
解决:a、扩展精度,b、调整sequence的last_number
「喜欢文章,快来给作者赞赏墨值吧」 赞赏
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。
评论
请登录后发表评论