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

Question

I create some procedures and functions to simplify the task of inserting, deleting, or updating strings. My question may sound silly because I'm sure something is missing.

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

ORA-06502: PL / SQL: numeric or significant error: error converting character to number

The following code does not work, but may indicate a 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 are written 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 a function without decimal parameters, I get no problems .

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

Output

Function TEST1 compilado

Raw Number: 2,1
Function Number: 2

, , , ,

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

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

, , , , Oracle , .

Answer 1

, , Oracle SQL Developer.

Oracle 11.2.0.3.0 64bit, TOAD 12.0.0.61, , Oracle SQL Developer 17.2.0.188.

:

:

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

---

TOAD

№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(2.1));
END;

:

Raw Number: 2.1
Function Number: 2.1

№2 ,:

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;

:

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

---

Oracle SQL Developer

Oracle SQL Developer # 1 , №2 ORA-06502 - .

№1: to_number('2,1') 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;

:

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

№2: NLS_NUMERIC_CHARACTERS :

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;

:

Raw Number: 2.1
Function Number: 2.1

Answer 2

, NLS_NUMERIC_CHARACTERS.

Try

select value
    from nls_session_parameters
    where parameter = 'NLS_NUMERIC_CHARACTERS';

VALUE                                  
----------------------------------------
,. 

. ;

alter session set NLS_NUMERIC_CHARACTERS = '.,';

'.' "," . , .

:

alter session set NLS_NUMERIC_CHARACTERS = '.,';
DECLARE
    numVar NUMBER;
BEGIN
numVar:= 2.1;
DBMS_OUTPUT.PUT_LINE('Raw Number: '||numVar);
    DBMS_OUTPUT.PUT_LINE('Function Number: '|| test1(2.1));

Answer 3

As your conclusion shows, the decimal separator in your settings is a comma (Spanish Culture), not a period. So you should write TO_NUMBER('2,1')instead2.1