Solution:
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;
/