oracle 存储过程命中 ORA-06502：PL/SQL：数字或值错误

Stored Proc hitting ORA-06502: PL/SQL: numeric or value error

提问by You Qi

I'm having a problem calling a stored procedure from OSB 11g thru JCA Adapter. The error I'm getting is:

我在通过 JCA Adapter 从 OSB 11g 调用存储过程时遇到问题。我得到的错误是：

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 1

The error is as short as it is without any appends behind the error description. It only happens in production environment and there are no problems when I point the database to the UAT one. More surprisingly, it will work in production envrionement if one of the variables I pass in is less than 128 characters. (or else I will hit the error above). The stored procedure type and length are defined correctly. having the table column able to cope for 4000 characters.

错误很短，在错误描述后面没有任何附加。它只发生在生产环境中，当我将数据库指向 UAT 时没有问题。更令人惊讶的是，如果我传入的变量之一少于 128 个字符，它将在生产环境中工作。（否则我会遇到上面的错误）。正确定义了存储过程类型和长度。使表格列能够处理 4000 个字符。

Both Oracle 9i DB sharing the same major minor build revision. The stored proc is located in a package. I extracted out and compared the stored proc from both DB environments using winmerge and it shows they mirror each other.

两个 Oracle 9i DB 共享相同的主要次要构建版本。存储过程位于一个包中。我使用 winmerge 从两个数据库环境中提取并比较了存储过程，它显示它们彼此镜像。

Executing the stored procedure manually in production environment works, provides that I declare a big enough varchar size. I manage to simulate the ORA-06502error by declaring a variable size lesser than the data length I'm passing in.

在生产环境中手动执行存储过程是可行的，前提是我声明了足够大的 varchar 大小。我设法ORA-06502通过声明一个小于我传入的数据长度的变量大小来模拟错误。

I even went as far as capturing the network dump from both executions and found there are slightly differences. (Note: I'm reusing the same datasource and only change the ip and username in WLS and did a restart before each executions)

我什至从两次执行中捕获网络转储，发现存在细微差异。（注意：我正在重用相同的数据源，只更改 WLS 中的 ip 和用户名，并在每次执行前重新启动）

When I point to production environment(the 1 having trouble), the tcpdump looks something like below:

当我指向生产环境（有问题的 1）时，tcpdump 如下所示：

BEGIN packagename.stored_proc_name(V_value1=>:1 , v_value2=>:2, v_value3=>:3); END .... {variable1}... {variable2}... {variable3}) 

When I point to UAT environment(the successful 1), the dump looks shorter and without the BEGIN; ENDtag

当我指向 UAT 环境（成功的 1）时，转储看起来更短并且没有BEGIN; END标签

.... {variable1}... {variable2}... {variable3}) 

What else could have gone wrong? Appreciate for any helps!

还有什么地方可能出错？感谢任何帮助！

回答by Lalit Kumar B

"The stored proc is located in a package. I extracted out and compared the stored proc from both DB environments using winmerge and it shows they mirror each other."

“存储过程位于一个包中。我使用 winmerge 从两个数据库环境中提取并比较了存储过程，它显示它们相互镜像。“

It is not about the code, the issue is data specific. In UAT you might not have production like data and thus you aren't able to replicate the issue.

这与代码无关，问题是特定于数据的。在 UAT 中，您可能没有类似数据的生产，因此您无法复制该问题。

Your error message just says error at line 1, which is not quite helpful. Are you executing the code without formatting it? If you format the code and execute it, and remove all exception handling, you will correctly know the line number. For example :

您的错误消息只是在第 1 行说错误，这不是很有帮助。您是否在不格式化的情况下执行代码？如果您格式化代码并执行它，并删除所有异常处理，您将正确地知道行号。例如 ：

SQL> declare
  2    num number;
  3  begin
  4    num := 'a';
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 4

See, it clearly tells that error is at line 4.

看，它清楚地表明错误在第 4 行。

By any chance if you have an EXCEPTIONblock, then make sure you use dbms_utility.format_error_stackand dbms_utility.format_error_backtraceto log the error details. Else, the error line number will be never correct.

如果您有一个EXCEPTION块，请务必使用dbms_utility.format_error_stack并dbms_utility.format_error_backtrace记录错误详细信息。否则，错误行号永远不会正确。