Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

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!

share|improve this answer
    
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 Apr 16 '11 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 Apr 17 '11 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 Apr 18 '11 at 8:11
    
Exact same versions of the SP? 64 vs 32-bit? –  Gaius Apr 18 '11 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 Apr 18 '11 at 11:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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