ORA-06502: PL/SQL: numeric or value error: character string buffer too small
This error occurred while varchar character is overflow from its specified length. In this blog, we show you the example by generating error and fixed it by increasing the size of varchar character.
Error:
Generate error while fetching data which is concatenating in the VAR variable having 1000 bytes limit defined in DECLARE part.
set serveroutput on
DECLARE
--var varchar2(32767);
--var varchar2(20000);
var varchar2(1000); -- Size is 1000 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;
/
OUTPUT
1
2
....
....
119
120
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 14
ORA-06512: at line 14
Solution
Increase the limit of VAR variable to 2000, it will fixed the issue.
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;
/
OUTPUT:
1
2
...
...
150
151
PL/SQL procedure successfully completed.
