ORA

巡检发现大量的12012 6502以及1472错误，其中6502报错ORA-06502: PL/SQL: numeric or value error: character to number conversion error，而1472报错为single-row subquery returns more than one row.问题分析： ORA-06502往往是存储过程的代码自身问题，变量赋值类型问题或者变量定义缓存不够，1472是子查询返回多于1行，造成程序无法处理，设计者考虑问题不周，缺乏异常处理机制，导致程序无法继续运行。参考官方文档：How to Determine the Cause of ORA-6502 Errors (PL/SQL: numeric or value error) (Doc ID 139548.1)针对ORA-06502错误可以开启跟踪ALTER SYSTEM SET EVENTS '6502 TRACE NAME ERRORSTACK LEVEL 3';运行错误语句或存储过程ALTER SYSTEM SET EVENTS '6502 TRACE NAME CONTEXT OFF';分析跟踪文件下面我们模拟这个过程，从而更好理解错误现象。比如执行如下代码报错SQL> SET SERVEROUTPUT ONDECLAREV_VAR1 VARCHAR2(10);V_VAR2 NUMBER;BEGINV_VAR2 := 'MIKE'; /* VALID ORA-6502 */V_VAR1 := 'ABCDEFGHIKL'; /* VALID ORA-6502 */END;/SQL> 2 3 4 5 6 7 8DECLARE*ERROR at line 1:ORA-06502: PL/SQL: numeric or value error: character to number conversion errorORA-06512: at line 5

开启跟踪。ALTER SYSTEM SET EVENTS '6502 TRACE NAME ERRORSTACK LEVEL 3';执行代码关闭跟踪ALTER SYSTEM SET EVENTS '6502 TRACE NAME CONTEXT OFF';

查看跟踪文件SQL> select name,value from v$diag_info where2 name='Default Trace File';

NAME--------------------------------------------------------------------------------VALUE--------------------------------------------------------------------------------Default Trace File/oracle/base/diag/rdbms/prod/prod/trace/prod_ora_3553.trc

手游卖号跟踪文件部分内容ORA-06502: PL/SQL: numeric or value error: character to number conversion error----- Current SQL Statement for this session (sql_id=28qqmkw6nkh2h) -----DECLAREV_VAR1 VARCHAR2(10);V_VAR2 NUMBER;BEGINV_VAR2 := 'MIKE'; /* VALID ORA-6502 */V_VAR1 := 'ABCDEFGHIKL'; /* VALID ORA-6502 */END;----- PL/SQL Stack ---------- PL/SQL Call Stack -----object line objecthandle number name0x75fce090 5 anonymous block

说明：匿名块的第5行有问题

V_VAR2 := 'MIKE';