ORA 06502 Error PL/SQL

Question

I am trying to execute a simple statement and i got an error while executing.

begin
dbms_output.put_line('Addition: '||4+2);
end;

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

But when i executed with * operator, it worked fine.

begin
dbms_output.put_line('Addition: '||4*2);
end;

Does anyone know the reason behind it?

Answer 1

It is due the Operator Precedence.

Multiplication has higher precedence than concatenation. So, 'Addition: '||4*2 evaluates to 'Addition: '||8 and to 'Addition: 8'.

Addition has same precedence as concatenation and operators with equal precedence are evaluated from left to right. So, 'Addition: '||4+2 evaluates to 'Addition: 4' + 2, which subsequently fails as you cannot add number to characters.

In such cases, you should always use brackets to explicitly specify the order of evaluation, like this 'Addition: '|| (4+2)

Answer 2

In my opinion, the actual problems is that this code is relying on implicit data type conversion by the Oracle kernel. Always use explicit data type conversion. For instance:

begin dbms_output.put_line('Addition: ' || to_char(4+2)); end;

There are many other cases where you will run into unexpected errors due to implicit data type conversion. Like in equal joining a varchar with number. As long as the varchar contains only numeric values, it works fine (although maybe slow due to index not being used). But as soon as you insert one row with non numeric data, you will run into an error when that row is being hit. Making the data type conversion explicit ensures that Oracle does not by accident chose the wrong side of an equal join to convert.