How to pass a NUMBER with decimal values as a parameter to a FUNCTION or PROCEDURE

Question

I'm creating some procedures and functions to make easier the tasks of inserting, deleting or updating rows. My question could sound silly because sure there's something I'm missing.

Any time I try to pass a decimal value as a parameter to a procedure or function I get an error.

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

The next code isn't functional but a way to show the problem.

CREATE OR REPLACE FUNCTION test1(num1 NUMBER)
    RETURN NUMBER IS
        BEGIN
        RETURN num1;
        END;
/
SET SERVEROUTPUT ON;
DECLARE
    numVar NUMBER;
BEGIN
numVar:= 2.1;
DBMS_OUTPUT.PUT_LINE('Raw Number: '||numVar);

                      /***********Calling test1*************/
            DBMS_OUTPUT.PUT_LINE('Function Number: '|| test1(2.1));

END;
/

Some of the output is in spanish but most of it is in english.

Function TEST1 compilado

Raw Number: 2,1


Error que empieza en la línea: 8 del comando :
DECLARE
    numVar NUMBER;
BEGIN
numVar:= 2.1;
DBMS_OUTPUT.PUT_LINE('Raw Number: '||numVar);
    DBMS_OUTPUT.PUT_LINE('Function Number: '|| test1(2.1));

END;
Informe de error -
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 6
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

When I call the function, without decimal parameters, I get no troubles.

          /***********Calling test1*************/
DBMS_OUTPUT.PUT_LINE('Function Number: '|| test1(2));

Output

Function TEST1 compilado

Raw Number: 2,1
Function Number: 2

If I pass a variable instead of a raw decimal number, I don't get any trouble either and I actually can use a decimal number but it makes less sense for me creating a procedure to insert data that requires defining a variable to pass it as a parameter

         /***********Calling test1*************/
DBMS_OUTPUT.PUT_LINE('Function Number: '|| test1(numVar));

Output

Function TEST1 compilado
Raw Number: 2,1
Function Number: 2,1

I would like to understand why I can´t pass a raw decimal number as a parameter, maybe I don't understand well the way Oracle casts data before introducing it as a parameter.

Answer 1

This problem seems to be specific to Oracle SQL Developer.

I have Oracle 11.2.0.3.0 64bit, and when I tested it in TOAD 12.0.0.61 everything worked perfectly, but not in Oracle SQL Developer 17.2.0.188.

How did I test it:

First, create procedure:

CREATE OR REPLACE FUNCTION test_function(x$n in NUMBER)
RETURN NUMBER IS
BEGIN  
    RETURN x$n;
END;

---

Testing in TOAD

Test #1, with .:

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,';

DECLARE
    numVar$n NUMBER;
BEGIN
    numVar$n:= 2.1;
    DBMS_OUTPUT.PUT_LINE('Raw Number: '||numVar$n);
    DBMS_OUTPUT.PUT_LINE('Function Number: '||test_function(2.1));
END;

Output:

Raw Number: 2.1
Function Number: 2.1

Test #2, with ,:

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.';

DECLARE
    numVar$n NUMBER;
BEGIN
    numVar$n:= 2.1;
    DBMS_OUTPUT.PUT_LINE('Raw Number: '||numVar$n);
    DBMS_OUTPUT.PUT_LINE('Function Number: '||test_function(2.1));
END;

Output:

Raw Number: 2,1
Function Number: 2,1

---

Testing in Oracle SQL Developer

In Oracle SQL Developer Test #1 works fine, but Test #2 raises ORA-06502 - this is your case.

Solution #1: use to_number('2,1') instead of 2.1:

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.';

DECLARE
    numVar$n NUMBER;
BEGIN
    numVar$n:= 2.1;
    DBMS_OUTPUT.PUT_LINE('Raw Number: '||numVar$n);
    DBMS_OUTPUT.PUT_LINE('Function Number: '||test_function(to_number('2,1')));
END;

Output:

Raw Number: 2,1
Function Number: 2,1

Solution #2: change NLS_NUMERIC_CHARACTERS during execution:

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.';

DECLARE
    numVar$n NUMBER;
    nnc$c VARCHAR2(255);
BEGIN
    numVar$n:= 2.1;

    SELECT value
    INTO   nnc$c
    FROM   nls_session_parameters
    WHERE  parameter = 'NLS_NUMERIC_CHARACTERS';

    EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS=''.,''';

    DBMS_OUTPUT.PUT_LINE('Raw Number: '||numVar$n);
    DBMS_OUTPUT.PUT_LINE('Function Number: '||test_function(2.1));

    EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS='''||nnc$c||'''';
END;

Output:

Raw Number: 2.1
Function Number: 2.1