Re: Oracle error: 06502 pLSQL : VALUE OR NUMERIC ERROR

From: Daniel Morgan <dmorgan_at_exesolutions.com>
Date: Fri, 10 May 2002 20:35:40 GMT
Message-ID: <3CDC2F10.8AD102BD@exesolutions.com>

AVK wrote:

> I support a vb application that uses a package to pass data into Oracle
> tables. The application is used by 40 users and has been
>
> in production for a year. Suddenly since last month, all users of the
> application get the following error when trying to upload data into Oracle.
>
> ORACLE ERROR: 06502 PLSQL: Numeric or Value Error
>
> Our Oracle DBA keeps saying it's an application error and that I should
> check the data going into the package. But the problem stays for 2 days and
> mysteriously disappears. I checked the data to make sure and it was valid.
>
> I have the error again this month, and really desperate for some help !!!!
>
> Did anyone have the same problem ???

Prepare to be disappointed. The following is from metalink and shortened for brevity.

Purpose: The purpose of this document is to provide guidance in debugging ORA-6502 errors.

Scope & Application

This document is intended for programmers and DBAs attempting to determine the cause of this error in their application. It is written in a PL/SQL context, but the concept applies to other programmatic interfaces such as Oracle Reports and Forms, JDBC, OCI, and Precompilers.

How to Determine the Cause of ORA-6502 Errors

The error description is very generic, but we attempt to further define its meaning and some potential causes in this article. We also show you how to debug and further narrow down the cause of this error.

This is not the type of error for which you can contact Oracle Support and obtain an immediate solution. The causes are unique to every customer's application and you are in a better position to debug this than we. In the majority of cases, you will find that the problem lies within your application code, data source, or table definitions.

The error message is:

06502, 00000, "PL/SQL: numeric or value error%s" // *Cause:
// *Action:

The first step when troubleshooting this problem is to identify each offending SQL statement and determine which application or program unit generates this statement. Oracle provides an event tracing facility that can be used to identify these offending SQL statements. Modify your init.ora file by setting the following event and restart your database.

event="6502 trace name errorstack level 12"

When you rerun your application, the ora-6502 will be captured. By examining this trace file, you are then able to identify the offending SQL statement, and with your knowledge of your application, you can relate this statement to the exact block of code within PL/SQL (or other programmatic interface) used to generate this statement.

Once you isolate the offending SQL statement, you are ready to set SQL tracing to capture the bind values. This can be done at the instance level or at the session level.

SQL> alter system set timed_statistics = true; SQL> alter system set sql_trace=true;

If you narrow this down to one isolated program unit, it may be more appropriate to set this at the session level within the PL/SQL block.

create or replace
begin

     execute_immediate('alter session set timed_statistics = true');
     execute_immediate('alter session set sql_trace = true');
     ...
     -- execute your PLSQL block
     -- execute the target SQL statement

end;

After the program finishes, review the raw SQL trace file. The raw trace file shows you the SQL statement plus the value of bind variables being bound to the placeholders in the statement.

Locate your SQL statement. The following is an extract of a trace file. Pay attention to the SQL statement, cursor number, bind number, and bind value. In this example, the cursor number is 19 and is associated with parse number 19 and bind number 19. You can clearly see the value being bound to the place holder is 'MIKE'.

PARSING IN CURSOR #19 ...
SELECT ENAME FROM EMP WHERE ENAME = :1 ... END OF STATEMENT
PARSE #19
BINDS #19
BIND 0:
VALUE=MIKE
EXEC
WAIT
WAIT

To summarize our progress thus far, we determined the SQL statement in question and the value being bound when the error occurs.

Review your PL/SQL code for invalid datatype assignments. These could come in the form of an incorrect size or type assignment. Compare the size of the PL/SQL variable you declared for this placeholder to the size of the value you have assigned it.

If the size of the string is larger than the size you have declared for the variable, then increase the size of the variable to accommodate larger values.

Next, compare your defined column sizes to the defined variable sizes that are part of your query. If the size of the value being fetched is larger than your defined variable size, then either make your variable size larger or decrease the size of your column.

If your error cannot be isolated to a SQL statement but seems to occur at package initialization, then review your initialization code to see if you have a mismatch between the declared variable size or type and what is being assigned.

If you use any built-in SQL character or number conversion functions, then be careful about assigning spaces to a number.

Other Causes

If you have a case where the same data set bound to the same SQL statement reproduces an intermittent ora-6502 error, then you may need to contact Oracle Support. Provide the tracing you have done so far.

Daniel Morgan Received on Fri May 10 2002 - 15:35:40 CDT