I have a Java (Spring) app that uses Oracle 10 as DB. Some legacy code has wrong parametrization of PL/SQL procedures. For instance, some input parameters are declared as OracleTypes.VARCHAR in Java DAO layer, while in fact they're NUMBER in PL/SQL procedure. 99% times this works without a problem (of course, if the sent data is actually a number), but sometimes I get the ORA-06502: PL/SQL: numeric or value error: character to number conversion error.
I understand how to fix the problem, but I wonder why this occurs on some DB installations and on most it doesn't. This is of high importance for me because it's very difficult to inspect legacy code for wrong parameter types.
Best Answer
Interesting question - at first I would have said an overflow, but checking, that's
ORA-01426
and if it wasn't a number,ORA-01722
. Is it just datatypeNUMBER
which defaults toNUMBER(38)
?But this can be diagnosed with a trace to see what's exactly going on. If you can identify the session, or can modify the connection code, you can do:
Now looking in the tracefile:
That should at least get you started on root cause analysis. But be warned that these trace files can get very large, very quickly!