PL/SQL

How to Resolve ORA-06502: PL/SQL: numeric or value error: character to number conversion error

ORA-06502 Due to Type Conversion

ORA-06502 tells you that PL/SQL engine cannot convert a string into a number. Which means, an arithmetic, numeric, string, conversion, or constraint error occurred. Let’s see a normal case first.

SQL> set serveroutput on;
SQL> declare
  2    v_num number;
  3  begin
  4    v_num := 123;
  5    dbms_output.put_line('The number is ' || v_num);
  6  end;
  7  /
The number is 123

PL/SQL procedure successfully completed.

A number 123 is assigned to variable V_NUM which accept only NUMBER type. So there’s no conversion needed. But what if we assign a string to the variable?

SQL> declare
  2    v_num number;
  3  begin
  4    v_num := '123';
  5    dbms_output.put_line('The number is ' || v_num);
  6  end;
  7  /
The number is 123

PL/SQL procedure successfully completed.

As you can see, PL/SQL engine converted the string into a number, then assigned it into the variable.

Now, let’s try some basic arithmetic expressions.

SQL> declare
  2    v_num number;
  3  begin
  4    v_num := 2 + 2;
  5    dbms_output.put_line('The number is ' || v_num);
  6  end;
  7  /
The number is 4

PL/SQL procedure successfully completed.

OK, the variable accepts the result of evaluation. What if we use it as a string?

SQL> declare
  2    v_num number;
  3  begin
  4    v_num := '2 + 2';
  5    dbms_output.put_line('The number is ' || v_num);
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 4

PL/SQL engine tried to convert the string into a number, but it failed with ORA-06502. This time, V_NUM cannot accept the result.

The solution to this type of error is to avoid type conversion if possible.

ORA-06502 Due to Operator Precedence

Let’s see a more advanced topic about operator precedence in Oracle database. In the following example, we tried to output a string that concatenate an arithmetic.

SQL> begin
  2    dbms_output.put_line('The number is ' || 2 + 2);
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 2

Since || (concatenation) and + (addition) operators are in the same level of operator precedence, PL/SQL engine will evaluate them in the order of presence.

First, it concatenated “The number is ” and “2” into “The number is 2”, which was successful, but when it tried to add the last “2”, it failed to convert the former string into a number and threw ORA-06502.

Solutions

1. Rearrange the Output

We should make PL/SQL engine deal with the addition first, then the concatenation by rearranging the output.

SQL> begin
  2    dbms_output.put_line(2 + 2 || ' is the number.');
  3  end;
  4  /
4 is the number.

PL/SQL procedure successfully completed.

This time, the expression is good because the order of presence of operators has been changed.

2. Override Operator Precedence

Beside rearranging the order of presence, how can we make the latter take the precedence over the former to fix the problem? Here is the trick.

SQL> begin
  2    dbms_output.put_line('The number is ' || (2 + 2));
  3  end;
  4  /
The number is 4

PL/SQL procedure successfully completed.

As you can see, we used a parenthesis to override operator precedence. The evaluation will start from the highest precedence, 2 + 2 inside the parentheses to the rest according to their operator precedence defined in Oracle. This is how we escape from ORA-06502.

Please note that, if multiple parentheses are used in your expression, the evaluation will start from the inner to the outer.

Further reading: How to Use Bind Variable in Oracle

Leave a Reply

Your email address will not be published. Required fields are marked *