DBMS_OUTPUT.PUT_LINE throws "PL/SQL: numeric or value error"

Question

I want to test one of my functions in oracle pl/sql using DBMS_OUTPUT.PUT_LINE. The problem I encounter is, that when I write a point number inside my function, I get the following error:

Error

ORA-06502: PL/SQL: numerischer oder Wertefehler: Fehler beim Konvertieren von Zeichen zu Zahl
ORA-06512: in Zeile 4 // line 4 is "v_net := pricecalculator(120.09)"
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.

This works

CREATE OR REPLACE FUNCTION Pricecalculator(net IN NUMBER, vat IN NUMBER DEFAULT 0.19)
    RETURN NUMBER
    IS
        Result NUMBER;
    BEGIN
        Result := net * (1 + vat);
        RETURN Result;
    END;
/

SET SERVEROUTPUT ON

DECLARE
    v_net NUMBER;
BEGIN
    v_net := Pricecalculator(120);
    DBMS_OUTPUT.PUT_LINE(v_net); // Prints (142.8)
END;
/

This does not work (see error)

SET SERVEROUTPUT ON

DECLARE
    v_net NUMBER;
BEGIN
    v_net := Pricecalculator(120.09);
    DBMS_OUTPUT.PUT_LINE(v_net); // When I delete this, v_net gets compiled but I wont see the result
END;
/

I am new to oracle pl/sql and have zero clue why the second approach does not work.

Seems to work OK...? Checked in SQL Developer too (20.4, against a 12cR1 DB). — Alex Poole
– Alex Poole, Commented May 25, 2021 at 11:28
Doesn't work inside my sql_developer 0.o (20.2). My DBMS_OUTPUT.PUT_LINE(v_net) is shown grey — Andrew
– Andrew, Commented May 25, 2021 at 11:29
My guess: Your locales, check your sql client NLS_NUMERIC_CHARACTERS. The database seems with dot decimal separator, i guess your client is going for comma decimal separator. Try v_net := Pricecalculator(120,09); in your PLSQL client bloc. — Zilog80
– Zilog80, Commented May 25, 2021 at 11:33
I can't write Pricecalculator(120, 09), because then I would just change the vat parameter to 9 and the output would be 1200 — Andrew
– Andrew, Commented May 25, 2021 at 11:35
@Zilog80 no; all of the arguments and variables are numbers, the NLS chars are only used for conversion to/from strings. Number literals always have to use a decimal point. Pricecalculator(120,09) will see that as two arguments, net of 120 and vat of 9. But you're right that it's that setting that's breaking it... in SQL Developer, at least. — Alex Poole
– Alex Poole, Commented May 25, 2021 at 11:35

Alex Poole · Accepted Answer · 2021-05-25 12:59:37Z

2

This seems to be a bug in SQL Developer. Looking at the statement log, your block is being converted to:

DECLARE SqlDevBind1Z_1 VARCHAR2(32767):=:SqlDevBind1ZInit1;  BEGIN DECLARE
    v_net NUMBER;
BEGIN
    v_net := Pricecalculator(TO_NUMBER( SqlDevBind1Z_1));
    DBMS_OUTPUT.PUT_LINE(v_net);
END;
 :AUXSQLDBIND1:=SqlDevBind1Z_1;  END;

and this part:

TO_NUMBER( SqlDevBind1Z_1)

is doing an implicit conversion. That will use your NLS_NUMERIC_CHARTACTERS setting, as @Zilog80 suggested, so it's only a problem when that is set to ',.' - i.e. with a comma as the decimal separator.

You can work around it by doing:

alter session set nls_numeric_characters = '.,'

but then your implicitly-converted output will be 142.9071 not 142,9071. If you want the comma separator in the output you would then need an explicit conversion:

DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_net, '99990.9999', 'nls_numeric_characters='',.'''));

... except SQL Developer is also borking that; which is why you showed periods in your question, presumably. Hopefully that is what you want to see...

edited May 25, 2021 at 12:59

answered May 25, 2021 at 11:54

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Zilog80 Over a year ago

If i remember well, it's possible to specify NLS_NUMERIC_CHARTACTERS directly with the TO_CHAR function, the syntax: TO_CHAR(number,format,'NLS_NUMERIC_CHARACTERS = '',.''').

Andrew Over a year ago

This did the trick, thank you. Don't know how to explain this solution to my professor but yeah. SQL Developer is borderline stupid

Alex Poole Over a year ago

@Zilog80 - thank you, yes, I'd started to show that and somehow posted with a bit of it missing.

Collectives™ on Stack Overflow

DBMS_OUTPUT.PUT_LINE throws "PL/SQL: numeric or value error"

Error

This works

This does not work (see error)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Error

This works

This does not work (see error)

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related