ORA-01438错误排查-优快云博客

日志记录的信息如下：

Sun Jul 12 04:03:06 2009
Errors in file /u01/admin/bpdb/bdump/bpdb2_j000_451260.trc:
ORA-12012: error on auto execute of job 22
ORA-01438: value larger than specified precision allowed for this column
ORA-06512: at "BANPING.PROC_UPDATE_OVERDUE", line 48
ORA-06512: at line 1

日志并没有记录出错的SQL及要写入的值等信息，而存储过程里的处理异常的语句并没有记录下这个错误：

EXCEPTION
WHEN OTHERS THEN
    iErrNo := SQLCODE;
    sErr   := '存储过程执行出错。出错原因：';
    sErr   := sErr || NVL(SQLERRM, '未知错误');
    INSERT INTO TERRORLOG
      (errno, errinfo, errtime)
    VALUES
      (iErrNo, sErr, SYSDATE);
    raise;

查询TERRORLOG表并没有该错误信息。

没别的办法了，通过ErrorStack可进行后台错误跟踪，然后通过查看生成的trace文件获得更详细的信息：

SQL> alter system set events='1438 trace name Errorstack forever,level 10';

System altered

然后新开一个会话执行存储过程：

SQL> set serveroutput on
SQL> exec PROC_UPDATE_OVERDUE;

begin PROC_UPDATE_OVERDUE; end;

ORA-01438: value larger than specified precision allowed for this column
ORA-06512: at "BANPING.PROC_UPDATE_OVERDUE", line 48
ORA-06512: at line 1

这时查看udump目录下已经生成了跟踪文件，然后停止跟踪：

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

System altered

此过程中数据库记录的日志如下：

OS Pid: 541528 executed alter system set events '1438 trace name Errorstack off'
Errors in file /u01/admin/bpdb/udump/bpdb1_ora_267056.trc:
ORA-01438: value larger than specified precision allowed for this column
Trace dumping is performing id=[cdmp_20090712235608]
OS Pid: 541528 executed alter system set events '1438 trace name Errorstack off'

然后我们查看trace文件就可以找到对应的SQL语句：

ksedmp: internal or fatal error
ORA-01438: value larger than specified precision allowed for this column
Current SQL statement for this session:
INSERT INTO PAYMENT(RMB,MYID,......) SELECT :B2 RMB,(MYID+100) MYID......FROM PAYMENT WHERE IHCODE=:B1 AND
IDX=(SELECT MAX(IDX) FROM PAYMENT WHERE IHCODE=:B1 ) AND ROWNUM=1

这个SQL就是问题所在了，那么我们再分析一下这个SQL以定位问题：

我列出来的RMB和MYID是2个可疑的对象，其定义分别为number(18,2)和number(5),我首先在执行这个SQL语句之前用dbms_output.put_line函数打印出:B1和:B2这两个参数的值，发现赋值给RMB的:B2是很多.0043、-.004、.0015之类的小数字。而这些数字在insert到number(18,2)类型的列时是会自动被截断的，也就是说这里并不会导致ORA-01438错误。

然后查看MYID果然发现了问题，这里的MYID是取PAYMENT表本身且IHCODE相同的记录的MYID加上100作为新的MYID的值，而根据:B1参数对应的IHCODE查看，在PAYMENT表共有对应的记录999条记录，最大的MYID是99900，也就是说，在插入第1000条记录的时候，MYID是100000，超过了定义的number(5)长度，所以产生了前面的ORA-01438错误。

找到问题的原因后再解决就很简单了，那么当初为什么会有这样的设计呢？原来，按理说这里是不应该产生999条这么多记录的，因为RMB字段的值会和另外一个地方的值进行比较，正常是能够抵消掉从而这些记录会被清除的，而就是因为前面展示的这些小数被截断，产生了微小的误差，导致失去了应有的平衡，从而导致这个错误隐藏了999天（该存储过程每天执行一次）。看来，RMB列这个number(18,2)的定义精度也是有问题的。

------------------------------------------------

ORA-01438: 值大于此列指定的允许精确度

比如定义为number(4,2)，却要插入一个值200.12的话，就会出错啊，原因是number（p，s）的问题。

number（p，s），其中p表示该number的总长度，s为小数位。
如果s为负数，则会取相应位数的取整。
例如，如果number（4，-3），则数字1234的存储值为1000；如果number（4，-2），则数字1234的存储值为1200。

在对数据库表中的字段设定类型时，要注意：
NUMBER(10,6) Double 带有小数
NUMBER(4) Long 为整数

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22123669/viewspace-675600/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22123669/viewspace-675600/