Error: ORA-06502: PL/SQL: numeric or value error: character to number conversion error

Question

I don't understand why I get this error Message: "ORA-06502: PL/SQL: numeric or value error: character to number conversion error"

This happens when I try something like this (just an easy example to show my problem):

create or replace PACKAGE test_package AS 
  FUNCTION fun(x float) return float; 
END test_package; 
/

create or replace PACKAGE BODY test_package AS 

  FUNCTION fun(x float) return float
  IS
  BEGIN    
    return x; 
  END fun;

END;
/

dbms_output.put_line(test_package.fun(0.25)) --ERROR
dbms_output.put_line(test_package.fun(1))    --NO ERROR

Would be nice if someone knows why. Thanks.

Depending on your culture settings (e.g. German) you might have to write `0,25` instead of `0.25` — Olivier Jacot-Descombes, Aug 02 '22 at 12:21
It is because the settings for NLS_NUMERIC_CHARACTERS on your client do not have the character "." as a decimal character. Read the answer in my other comment for complete details. — Koen Lostrie, Aug 02 '22 at 12:26
@KoenLostrie The function expects `float` as input and both input values are [valid number *literals*](https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Literals.html#GUID-083FEFEA-B33F-436B-AEBF-9101A49EF189). No NLS stuff is used here to process input. Can you please clarify how it may be used here? — astentx, Aug 02 '22 at 13:41
@astentx it failed for me with same error if I had comma as decimal character in NLS_NUMERIC_CHARACTERS. Changing it to ".," solved it. — Koen Lostrie, Aug 02 '22 at 14:00
@KoenLostrie That should only happen if you provide a string that is auto-converted to number, i.e. if you call ```test_package.fun('0.25')``` instead of ```test_package.fun(0.25)``` — Frank Schmitt, Aug 02 '22 at 14:01
@FrankSchmitt I did an ```alter session set NLS_NUMERIC_CHARACTERS = ',.';``` and the statement (without quotes just like in the question, but wrapped in BEGIN/END) errors out with ORA-06502. I assumed the OP had the same issue. — Koen Lostrie, Aug 02 '22 at 15:15
@KoenLostrie It works even for extraordinary numeric characters: [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_21&fiddle=4158d67f9cb8e62cb672e39b95ea8756) — astentx, Aug 02 '22 at 15:53
@astentx looks like I had it wrong learned something new today :) — Koen Lostrie, Aug 02 '22 at 19:50
@Guest Please post the complete console ouput, including the script. Does the problem remain when you replace all 'FLOAT' keywords with 'NUMBER'? — diziaq, Aug 03 '22 at 12:05

score 0 · Answer 1 · answered Aug 02 '22 at 12:29

Make sure your dbms_output calls end with a semi-colon; otherwise your code works fine:

drop package test_package;

create or replace PACKAGE test_package AS 
  FUNCTION fun(x float) return float; 
END test_package; 
/

create or replace PACKAGE BODY test_package AS 

  FUNCTION fun(x float) return float
  IS
  BEGIN    
    return x; 
  END fun;

END;
/

begin
  dbms_output.enable;
  dbms_output.put_line(test_package.fun(0.25));
  dbms_output.put_line(test_package.fun(1));
end;
/

This returns the following:

Package TEST_PACKAGE dropped.


Package TEST_PACKAGE compiled


Package Body TEST_PACKAGE compiled

.25
1


PL/SQL procedure successfully completed.

score 0 · Answer 2 · answered Aug 02 '22 at 14:02

0

Your example works fine; here's a simplified version (as @astentx pointed out in their comment - 0.25 is a numeric literal and therefore not subject to NLS settings):

declare
  FUNCTION fun(x float) return float
  IS
  BEGIN    
    return x; 
  END fun;
begin
  dbms_output.put_line(fun(0.25));
  dbms_output.put_line(fun(1));
end;

That said, if you happen to call it with string literals instead, i.e.

begin
  dbms_output.put_line(fun('0.25'));
  dbms_output.put_line(fun('1'));
end;

then auto-conversion kicks in, and the result depends on your NLS settings. In that case, you need to take your NLS settings into account when converting it, e.g. by calling to_number() with explicit NLS_NUMERIC_CHARACTERS:

begin
  dbms_output.put_line(fun(to_number('0.25', '99D99', 'NLS_NUMERIC_CHARACTERS=''.,''')));
  dbms_output.put_line(fun(to_number('1')));
end;

answered Aug 02 '22 at 14:02

Frank Schmitt

30,195
12
73
107

No, my example and your simplified version don't work at mine. The same error occurs again. And I don't want to call it with string literals. I have used floats like that in many functions and these still work. That is what i don't understand. – Guest Aug 03 '22 at 07:22
@Guest What client are you using? And what are your locale settings (NLS_...) ? – Frank Schmitt Aug 03 '22 at 10:47
It's ok it works now, after I altered the session to NLS_NUMERIC_CHARACTERS = ',.'. Just strange, that I could use float before without altering. But, thank you. – Guest Aug 03 '22 at 11:04

Error: ORA-06502: PL/SQL: numeric or value error: character to number conversion error

2 Answers2