Skip to content
Home » Oracle » How to Resolve ORA-06502: PL/SQL: numeric or value error: character to number conversion error

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

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

A. 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.

B. Numeric Operator Precedence

To better understand 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.

A very similar error that you might see in your statements is ORA-01722: invalid number, which is also related to conversion issues of numeric values.

6 thoughts on “How to Resolve ORA-06502: PL/SQL: numeric or value error: character to number conversion error”

  1. Hi,
    I’m getting ORA-06502: PL/SQL: numeric or value error: character to number conversion error

    on this command below…as_pre_payment_check_number is varchar(10)
    any thought?

    select lpad(to_char(prepaid_sequence.nextval),10,0)
    — into as_pre_payment_check_number
    from dual;

    1. I did a test for you and I see no problem by running the following block of codes in Oracle database 19c.

      SQL> conn hr/hr
      Connected.
      SQLL> set serveroutput on;
      SQL> declare
        2  v_dep_no varchar(10);
        3  begin
        4  select lpad(to_char(departments_seq.nextval),10,0) into v_dep_no from dual;
        5  dbms_output.put_line('The next department number is: ' || v_dep_no);
        6  end;
        7  /
      The next department number is: 0000000310

      PL/SQL procedure successfully completed.

  2. Ed,

    I’m getting this error when trying to use the following SQL to define a LOV for a column of an interactive grid where the first part is (supposedly) the display value and the second item is the return value. Whenever I try to change the value in the interactive grid and save it I get the 6502 error (it’s trying to save to a number field in the DB). Can you help?

    Select NAME||’, ‘||INFO_ID, INFO_ID from TABLE where END_DATE is null or END_DATE > SYSDATE
    order by INFO_ID asc

    It errors even if I make the display and return values just “INFO_ID”.

    ORACLE apex 21.2

    1. This could be a browser (tool) specific problem, please try to exit the GUI, then re-enter it. Or you may use another browser (tool) to operate.

Leave a Reply

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