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

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.

CodePudding user response：

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.

CodePudding user response：

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;