ORA 06502 Error PL/SQL

4
votes

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?

2
sqloracleplsqloracle11g
Try using brackets dbms_output.put_line('Addition: '||(4+2)); – Vishwanath Dalvi
hi @Vignesh has any of these answers been helpful to solve your problem? If not please add what was not successful to your question. When an answer helped you, can you accept it by clicking the hollow green check mark next to it? – Guido Leenders
the second answer (Operator Precedence) is correct. You should accept it – Shine

2 Answers

10
votes

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)

1
votes

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.