Ora-06502: pl/sql: numeric or value error: character string buffer too small

Question

Problem :

I tried my Oracle code in different ways, like by taking out a while loop or a if loop, but when I put both loops together (if and while), I always face below error at the 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"

 

Answer 1

“PL/SQL: numeric or value error: character string buffer too small”

This error is due to a fact that you declare the string to be of the fixed length (say 30), and at some point in the code you assign it the value whose length exceeds what you have declared.

For example below code:

myOraString VARCHAR2(20);
myOraString :='abcdefghijklmnopqrstuvwxyz'; --length is 26

Will give the above error.

OR

CHAR is the always fixed-length data type that uses a as much space as possible. So a:= a||'one '; will require more space than it is available. So your problem can be fixed by the below example:

declare
  v_boo char(50);
begin
  v_boo := 'A';
  dbms_output.put_line('length of v_boo(A) = ' || length(v_boo));
  -- next line will raise your error:
  -- ORA-06502: PL/SQL: numeric or value error: character string buffer too small
  v_boo := v_boo || 'B';
  dbms_output.put_line('length of v_boo(AB) = ' || length(v_boo));  
end;
/

So please never use the char.

Answer 2

PL/SQL: numeric or value error: character string buffer too small because of the fact that you declare a string to be of a fixed length (say 20), and at some point in your code you assign it a value whose

length overstep what you declared.

for example:

myString VARCHAR2(20);
myString :='abcdefghijklmnopqrstuvwxyz'; --length 26

will fire such an error

CHAR is a fixed-length data type that uses as much space as feasible. Hence a:= a||'one '; will need more space than is available. Your issue can be diminished to the following example:

declare
  v_foo char(50);
begin
  v_foo := 'A';
  dbms_output.put_line('length of v_foo(A) = ' || length(v_foo));
  -- next line will raise:
  -- ORA-06502: PL/SQL: numeric or value error: character string buffer too small
  v_foo := v_foo || 'B';
  dbms_output.put_line('length of v_foo(AB) = ' || length(v_foo));  
end;
/

Never use char. For gradual check the following question (read also the links):

ariable v_field_A cannot hold value more than 100 characters

It is very much possible because you are concatenating the variable for each row in the CURSOR FOR LOOP.

For example:

SQL> DECLARE
  2    v_name VARCHAR2(50);
  3  BEGIN
  4    FOR i IN
  5    (SELECT ename FROM emp
  6    )
  7    LOOP
  8      v_name := v_name || i.ename;
  9    END LOOP;
 10  END;
 11  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 8

Employ DBMS_OUTPUT to view the present size of the variable and the new value being appended.

Let's debug

SQL> DECLARE
  2    v_name VARCHAR2(50);
  3  BEGIN
  4    FOR i IN
  5    (SELECT ename FROM emp
  6    )
  7    LOOP
  8      dbms_output.put_line('Length of new value = '||LENGTH(i.ename));
  9      v_name := v_name || i.ename;
 10      dbms_output.put_line('Length of variable = '||LENGTH(v_name));
 11    END LOOP;
 12  END;
 13  /
Length of new value = 5
Length of variable = 5
Length of new value = 5
Length of variable = 10
Length of new value = 4
Length of variable = 14
Length of new value = 5
Length of variable = 19
Length of new value = 6
Length of variable = 25
Length of new value = 5
Length of variable = 30
Length of new value = 5
Length of variable = 35
Length of new value = 5
Length of variable = 40
Length of new value = 4
Length of variable = 44
Length of new value = 6
Length of variable = 50
Length of new value = 5

Error

DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 9

It is pretty clear, we wanted to concatenate a string with length 5 to the variable declared as max size 50, at present occupying a value of size 50. Therefore, it throws the error ORA-06502: PL/SQL: numeric or value error: character string buffer too small.

Increment the length of the variable v_variable1.

Example:

Declare
v_variable1 varchar(6);
BEGIN
select '123456' into v_variable1  from dual;
END;
/

PL/SQL procedure successfully completed.

Increment the limit of VAR variable to 2000, it will fixed the problem.
Maximum limit of VARCHAR2 is 32767.

set serveroutput on
DECLARE
var varchar2(2000); -- Size is 2000 bytes
var1 number;
CURSOR cn IS SELECT object_name FROM dba_objects where rownum NULL);
FOR v_objectname IN cn
LOOP
var1 := 1+var1;
var := var || v_objectname.object_name;
dbms_output.put_line(var1);
--Dbms_output.put_line(var);
END LOOP;
END;
/