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.

1 Answer 1


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 datatype NUMBER which defaults to NUMBER(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:

SQL> alter session set events='6502 trace name errorstack level 12';

Session altered.

SQL> create or replace function fn_add(p1 number, p2 number) return number
  2  as
  3  v1 number(1);
  4  begin
  5  v1 := p1 + p2;
  6  return v1;
  7  end;
  8  /

Function created.

SQL> select fn_add('10', '1') from dual;
select fn_add('10', '1') from dual
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large

Now looking in the tracefile:

---- Error Stack Dump -----
ORA-06502: PL/SQL: numeric or value error: number precision too large
----- Current SQL Statement for this session (sql_id=a36psfjj50bpv) -----
select fn_add('10', '1') from dual

That should at least get you started on root cause analysis. But be warned that these trace files can get very large, very quickly!

  • I've edited my question to reflect the actual error, it says that I'm converting character to number which is essentially true (because in Java type is declared as VARCHAR and in PL it's declared as NUMBER) but I'm actgually sending a number all the time.
    – eagerMoose
    Commented Apr 16, 2011 at 20:11
  • In my example I am passing a VARCHAR in - but I am exceeding the permitted range of one of the NUMBER variables inside the SP. The conversion between types is still happening correctly.
    – Gaius
    Commented Apr 17, 2011 at 15:18
  • The thing is, using exact same parameters on two Oracle instances causes this exception to occur on one of the installations and doesn't on most others. What I want to know is whether this is caused by a difference in DB configuration.
    – eagerMoose
    Commented Apr 18, 2011 at 8:11
  • Exact same versions of the SP? 64 vs 32-bit?
    – Gaius
    Commented Apr 18, 2011 at 10:51
  • I'm afraid I don't know the details, but the two DB setups should be the same, including configuration parameters. Is there a setting in Oracle which could enable or disable implicit type conversion?
    – eagerMoose
    Commented Apr 18, 2011 at 11:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.