CODEWITHSUNDEEP
sqloracle-databaseplsql
vikas gupta
vikas gupta

Reputation: 21

ORA-06502: PL/SQL: numeric or value error when concatenating

DECLARE
    a NUMBER;
    b NUMBER;
BEGIN
    a :=: a;
    b :=: b;
    DBMS_OUTPUT.PUT_LINE('sum = '|| a+b);
END;

I am getting error as numeric or value error

Upvotes: 0

Views: 2067

Answers (3)

Alex Poole
Alex Poole

Reputation: 191425

The problem is with operator precedence:

Oracle evaluates operators with equal precedence from left to right within an expression.

... and as the table shows + and || have equal precedence. So in this statement:

DBMS_OUTPUT.PUT_LINE('sum = '|| a+b);

this is interpreted as 'sum = '|| a which gives you a string, then <result string> + b, and string + number will cause the error you see if the string cannot be implicitly converted to a number - and 'sum = 1' can't be.

You can add parentheses to override the default precedence:

DBMS_OUTPUT.PUT_LINE('sum = '|| (a+b));

db<>fiddle

Upvotes: 2

Koen Lostrie
Koen Lostrie

Reputation: 18685

You're not seeing a sqldeveloper error, you're getting an error running a sql statement in sqldeveloper, that is something very different. For simplicity I'm declaring the variables in my example instead of using bind variables like you do:

DECLARE
    a NUMBER;
    b NUMBER;
BEGIN
    a := 1;  
    b := 2;    
    DBMS_OUTPUT.PUT_LINE('sum = '||a+b);
END;

Returns ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 7 The important information is the "line 7". Something is failing in that dbms_output statement and the cause is the operator precedence. Best is to tell the database engine that you want the number concatenated to the string, the error indicates that there is an implicit conversion happening.

The following works just fine:

DECLARE
    a NUMBER;
    b NUMBER;
BEGIN
    a := 1;  
    b := 2;    
    DBMS_OUTPUT.PUT_LINE('sum = '|| TO_NUMBER(a+b));
END;

One additional remark. This syntax

    a :=: a;

works but is very confusing. I would use a more descriptive name for your bind variable and separate the assignment operator := from the bind variable. Writing it like this is a lot more readable:

   a := :variablea;

Upvotes: 2

William Robertson
William Robertson

Reputation: 16001

A simplified version to clarify the issue:

begin
    dbms_output.put_line('Result ' || 2 + 2);
end;
/

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 2

This fails because the expression is built left to right, and you can't add 2 to the string 'Result: 2'.

It would work with multiplication or division, because arithmetic precedence causes those operations to be evaluated first.

begin
    dbms_output.put_line('Result ' || 2 / 2);
end;
/

Result 1

PL/SQL procedure successfully completed.

To allow this to work for any operation, you need to bracket the expression:

begin
    dbms_output.put_line('Result ' || (2 + 2));
end;
/

Result 4

PL/SQL procedure successfully completed.

Just to add, it is clearer to write host/bind variables without any space after :, for example :myvar rather than : myvar.

Upvotes: 2

Related Questions