Ora 06502 pl sql ошибка числа или значения

I’m trying to run the following insert command in Oracle SQL Developer:

insert into work_comp_rates (company_id, work_comp_rt)
values ('101', 0.11);

Which gives me this error: «ORA-06502: PL/SQL: numeric or value error: number precision too large»

There is a trigger attached:

create or replace 
TRIGGER APPS.work_codes_trig
before insert or update ON APPS.WORK_COMP_RATES for each row
begin
  if inserting then
    if :NEW.RID is null then
      :NEW.RID := it_api.gen_pk;
    end if;
    :NEW.CREATED_ON := sysdate;
  end if;
  if updating then
    :NEW.MODIFIED_ON := sysdate;
  end if;
end;

If I replace

:NEW.RID := it_api.gen_pk; 

with

:NEW.RID := 599;

the insert statement works.

IT_API Body:

create or replace
package body it_api
as
-- generates and returns unique number used for primary key values
function gen_pk
return number
is
l_pk number := 0;
begin
for c1 in (
select to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') pk
from dual )
loop
l_pk := c1.pk;
exit;
end loop;
return l_pk;
end gen_pk;
end it_api;

I don’t know Oracle very well and that script was written by somebody else. So any help is appreciated!

I tried the following code different ways, like by taking out the while or the if, but when I put both together (if and while), I always get the error at the end…

undefine numero
set serveroutput on
accept numero prompt 'Type # between 100 and 999: '
declare
   i number:=1;
   a char(25);
   b char(1);
   c varchar2(10);
   d number;
begin
   c := №
   d := length(c);
   b := substr(c, i, 1);
   while i <= d loop
     if b = '1' then
       a:= a||'one ';
     end if;
     i := i+1;
   end loop;
   dbms_output.put_line('The number is '||a);
end;
/

ERROR:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 13
06502. 00000 -  "PL/SQL: numeric or value error%s"

FIXED by changing how I declared the variable «a» to:

a varchar2(2000);

*Notice that here, the significant change is to use VARCHAR2 instead of CHAR (not the bigger length). According to @user272735 ‘s answer, that’s the key.

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

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

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

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.

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

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.

ORA-06502: PL/SQL: numeric or value error: character string buffer too small error occurs when the length of the character string exceeds the length of the declared character type variable,. The value cannot be assigned to the variable if the size of the value passed in the database exceeds the size of the variable declared. The error ORA-06502: PL/SQL: numeric or value error: character string buffer too small would be thrown by the oracle. The error occurs because the output value saved in that variable is longer than it was declared.

The length of the string should not exceed the size of the data type declared in the variable. The string can be stored in the variable in this case. If the length of the character string exceeds the declared variable size, the character string cannot be saved. If the character is attempted to be assigned to the attribute, an exception would be thrown.

Exception

The error will be described as follows. The line number identifies the location of the error. The variable data size is larger than the value size. The following error has been thrown.

declare
    empid varchar2(3);
begin
    empid := 'A101';
end;
Error report -
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4
06502. 00000 -  "PL/SQL: numeric or value error%s"

Two ORA errors can be seen in the error stack trace. The first error code is shown alongside the error message. The second error code indicates which line the error happened on. The error indicates that the declared string variable’s size is insufficient in comparison to the value assigned to it.

Problem

The character string cannot be allocated if the length of the string exceeds the size of the declared data type variable. The error can be repeated in this scenario. The database is attempting to assign the variable a string. The error would be thrown since the string is longer than the variable’s length.

In the example below, the value has four characters. The variable is declared to be three characters long. The length of the string value exceeds the length of the declared variable. The error ORA-06502:PL/SQL: numeric or value error: character string buffer too small would be thrown if the value is assigned to a variable that is smaller in size.

declare
    empid varchar2(3);
begin
    empid := 'A101';
end;

Output

declare
    empid varchar2(3);
begin
    empid := 'A101';
end;
Error report -
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4
06502. 00000 -  "PL/SQL: numeric or value error%s"

Cause

An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).

Action

Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.

Solution 1

The size of the value passed in Oracle PS./SQL exceeds the declared character data type size. To accommodate the value, the variable data type should be modified. The character data type’s size should be increased. If the size of the character data type is reached to maximum size of the data type, the different data type should be used to accommodate the larger value.

declare
    empid varchar2(4);
begin
    empid := 'A101';
end;

Output

PL/SQL procedure successfully completed.

Solution 2

It’s essential to double-check the PL/SQL value. It’s possible that the value was passed to the variable inappropriately or that there was an error in the method. The value will be stored in the variable if it is corrected.

declare
    empid varchar2(4);
begin
    empid := '101';
end;

Output

PL/SQL procedure successfully completed.

Solution 3

In most instances, the value assigned would be within the declared data type’s range. The length of the value sometimes reaches the declared data type size. We can’t adjust the data type size in this situation. The exception should be handled and taken action in the PL/SQL code.

declare
    empid varchar2(3);
begin
    empid := 'A101';
exception
    WHEN OTHERS THEN
        empid :=0;
end;

Output

PL/SQL procedure successfully completed.