Stored Proc hitting ORA-06502: PL/SQL: numeric or value error
I'm having a problem calling a stored procedure from OSB 11g thru JCA Adapter. The error I'm getting is:
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.
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.
Executing the stored procedure manually in production environment works, provides that I declare a big enough varchar size. I manage to simulate the ORA-06502 error by declaring a variable size lesser than the data length I'm passing in.
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)
When I point to production environment(the 1 having trouble), the tcpdump looks something like below:
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; END tag
.... {variable1}... {variable2}... {variable3})
What else could have gone wrong? Appreciate for any helps!
Answer
"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."
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.
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 :
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.
By any chance if you have an EXCEPTION block, then make sure you use dbms_utility.format_error_stack and dbms_utility.format_error_backtrace to log the error details. Else, the error line number will be never correct.