ORA-06502: PL/SQL: numeric or value error常见问题

巡检发现大量的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 ON
DECLARE
V_VAR1 VARCHAR2(10);
V_VAR2 NUMBER;
BEGIN
V_VAR2 := 'MIKE'; /* VALID ORA-6502 */
V_VAR1 := 'ABCDEFGHIKL'; /* VALID ORA-6502 */
END;
/SQL> 2 3 4 5 6 7 8
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-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 where
2 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) -----
DECLARE
V_VAR1 VARCHAR2(10);
V_VAR2 NUMBER;
BEGIN
V_VAR2 := 'MIKE'; /* VALID ORA-6502 */
V_VAR1 := 'ABCDEFGHIKL'; /* VALID ORA-6502 */
END;
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0x75fce090 5 anonymous block

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

V_VAR2 := 'MIKE'; <<<<<<这里将varchar类型数据赋给了number类型

######################ORA-06502的其他两个典型例子如下所示######################

SQL> SET SERVEROUTPUT ON
DECLARE
V_VAR1 VARCHAR2(3);
BEGIN
SELECT 'ABCD' INTO V_VAR1 FROM DUAL;
END;
/SQL> 2 3 4 5 6
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small <<<<<直接传数据给本地变量，缓存不够
ORA-06512: at line 4


SQL> CREATE OR REPLACE FUNCTION
TEST_6502_ERROR
RETURN VARCHAR2
AS
BEGIN
RETURN 'ASDF';
END;
/ 2 3 4 5 6 7 8

Function created.

SQL> SET SERVEROUTPUT ON
DECLARE
V_GET_VALUE VARCHAR2(3);
BEGIN
V_GET_VALUE := TEST_6502_ERROR;
END;
/SQL> 2 3 4 5 6
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small <<<<函数返回数据，定义变量缓存不够
ORA-06512: at line 4