使用SQL TRACE 排查ORA-06502: PL/SQL: numeric or value error 一解

四川行0728的外管作业报错,提示ORACLE错误
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 1
一般这种错误都是由于数值型的字段插入非数值产生。排查当日数据,检查各数值字段中是否有非数值数据,或者空格。
耗费两个小时排查程序中所有的数值型字段的加工,却一无所获,所有数值型字段的数据都是有效数字。

排查无解,只能使用sql trace 跟踪程序的运行。

在PRO*c 程序中增加

execute immediate ‘alter session set events ”10046 trace name context forever,level 12”’;

(注意需要使用DBA用户授予ODSBDATA用户ALTER SESSION ‘的权限,否则会报权限不足)

重新编译后运行,等待程序报错后前往数据库主机取得trace文件。
(文件位置可以使用sql查询 select value from v$parameter where name=’user_dump_dest’ ,文件名为进程号)

检查trace文件 在FETCH #39游标时程序报了错误ERROR #1:err=6502,错误时的绑定变量值为‘6011’,
BINDS #39:
kkscoacd
Bind#0
  oacdty=01 mxl=32(05) mxlc=00 mal=00 scl=00 pre=00
  oacflg=13 fl2=206001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=800003f740020a00  bln=32  avl=04  flg=05
  value=”6011″
EXEC #39:c=0,e=120,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=19473437793620
WAIT #39: nam=’db file sequential read’ ela= 9689 file#=53 block#=340863 blocks=1 obj#=1133089 tim=19473437803598
FETCH #39:c=0,e=10066,p=1,cr=2,cu=0,mis=0,r=1,dep=1,og=1,tim=19473437803713
ERROR #1:err=6502 tim=1994080031

从错误点往上寻找CURSOR #39 。得到游标的语句。
PARSING IN CURSOR #39 len=85 dep=1 uid=68 oct=3 lid=68 tim=19473437608957 hv=2456379465 ad=’3d82aa78′
SELECT  C_TRANS_CODE, D_TRANS_CODE FROM BM_SAFE_SPE_DSCRP_COD_DIM WHERE DSCRP_COD=:1
END OF STMT
PARSE #39:c=0,e=397,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=19473437608950

程序中源码的程序为
EXECUTE IMMEDIATE   ‘SELECT  C_TRANS_CODE, D_TRANS_CODE FROM BM_SAFE_SPE_DSCRP_COD_DIM WHERE DSCRP_COD=:1 ‘
                              INTO  C_TRANS_CODE, D_TRANS_CODE
                              USING FDETAIL.SA_DSCRP_COD;

由于已知发生错误的绑定变量值为6011,手工执行sql

SELECT  C_TRANS_CODE, D_TRANS_CODE FROM BM_SAFE_SPE_DSCRP_COD_DIM WHERE DSCRP_COD=’6011′;

C_TRAN D_TRAN
—— ——
902010

返回值为902010,以及空值。根据trace文件的提示。游标的EXEC没有问题。错误发生在fetch过程中。即发生在将sql结果赋值给程序变量的过程中

检查程序中的变量定义。发现问题。定义长度为4位,而实际为6位。

  C_TRANS_CODE VARCHAR2(4);
  D_TRANS_CODE VARCHAR2(4);

修改长度后重新编译运行,作业成功。

询问四川行。原来昨日下午他们有应业务要求,将自动补录的配置表中的4位交易码改为6位。

ORA-06502: PL/SQL: numeric or value error

虽然numberic error 占大多数,但是像这个例子一样value error 也是其中的一部分。