ORA-06502: PL/SQL: numeric or value error: character string buffer too small

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.