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

  • by
ORA-06502 due to Character Type Conversion

ORA-06502

ORA-06502 means that PL/SQL engine cannot convert a character-typed string into a number or a subset of arithmetic for overall evaluation. Mostly, it’s because of the following problems:

  1. Numeric Type Conversion
  2. Numeric Operator Precedence

ORA-06502 Due to Numeric 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 value, the result of evaluation, no ORA-06502. 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 implicit type conversion if possible.

ORA-06502 Due to Numeric Operator Precedence

To better know ORA-06502, 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

ORA-06502 was thrown eventually. Since || (concatenation) and + (addition) operators are at 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 value “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 numeric evaluation 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 for our PL/SQL block of codes.

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 which is 2 + 2 numeric value inside the parentheses to the rest according to their operator precedence defined in Oracle. This is how we escape from ORA-06502.

In PL/SQL, 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 *