如何判断ORA-6502报错的原因

刘泽宇

SOLUTION

What is "ORA-06502 PL/SQL: numeric or value error"?

This is a very generic error and there can be any number of reasons for this error. In most cases, the error is due to a PL/SQL code issue, like mismatch in variable data type and the value being assigned to it or assigning more character value to a VARCHAR or CHAR variable than it can hold etc. The root cause is often specific to what the code is doing at the time of the error and in majority of the cases the problem is due to the PL/SQL code.

How to troubleshoot the "ORA-06502 PL/SQL: numeric or value error"?

The first steps in troubleshooting the ORA-06502 error is to identify the failing PL/SQL statement. Oracle provides an event tracing facility that can be used to identify the failing PL/SQL statement.

Enable the trace as below:

ALTER SYSTEM SET EVENTS '6502 TRACE NAME ERRORSTACK LEVEL 3';

This event trace can also be enabled at the session level. Enable the trace at the session level if the error can be reproduced by running the failing PL/SQL code from a SQL*Plus session.When enabling the trace at session level, you can also give a naming pattern for the trace to identify the trace file easily, this is done using:

ALTER SESSION SET TRACEFILE_IDENTIFIER='6502_TRACE';

This will generate a trace file with name "<SID>_ora_<PID>_6502_TRACE.trc" so that the trace can be identified easily.

Once the event tracing is enabled, run the failing PL/SQL code to generate a trace file. Once the trace is generated successfully, disable the trace as below:

ALTER SYSTEM SET EVENTS '6502 TRACE NAME CONTEXT OFF';

Review the trace file to identify the line of the PL/SQL code that fails with ORA-06502 error.For example, the below code when run will fail with ORA-0652 trace.

SET SERVEROUTPUT ON
DECLARE
  v_testvar VARCHAR2(10);
BEGIN
  v_testvar := 'ABCDEFGHIJKL';
END;
/

And the trace file will show below call stack.

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
----- Current SQL Statement for this session (sql_id=163u4nvy76u8r) -----
DECLARE
  v_testvar VARCHAR2(10);
BEGIN
  v_testvar := 'ABCDEFGHIJKL';
END;
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object          line  object
  handle   number  name
0x64308080     4   anonymous block

The call stack shows that there is a problem in line 4 of the anonymous block.  

Examine the line of the code that the call stack is showing and analyze what the code is trying to do at the time of the error. Based on this further debugging may be required.

The most common root cause is, when there is a mismatch between the data type of the variable and the value that is being assigned to it.

For Example:

• Assigning character value to a numeric data type.
• Assigning more value to a variable than it can hold.
  

It could be either a direct value assignment (E.g.) var_name := <value> or while fetching data from table to the variable or while getting the data as result of another PL/SQL call through parameters etc.

Mismatch between the client side NLS_LANG and NLS_CHARACTERSET of the database.

The issue also can occur when there is a mismatch between client side character set NLS_LANG and the database character set NLS_CHARACTERSET. When working with UNICODE database (UTF8/AL32UTF8), make sure that:

• The NLS_LANG is not set at the client side. When NLS_LANG is not set, it will use database character set.
• If NLS_LANG is set, then make sure PL/SQL bind variables is declared with enough buffer size so that the data conversion does not fail.