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

Question

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 := &numero;
   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.

try DBMS_OUTPUT.ENABLE(500000); to increase the size of the console buffer. — UIlrvnd
– UIlrvnd, Commented Sep 11, 2013 at 2:52

L Petre · Accepted Answer · 2014-10-07 13:07:55Z

75

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

is due to 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 exceeds what you declared.

for example:

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

will fire such an error

answered Oct 7, 2014 at 13:07

L Petre

1,1178 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:18:20Z

19

CHAR is a fixed-length data type that uses as much space as possible. So a:= a||'one '; will require more space than is available. Your problem can be reduced 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 rationale check the following question (read also the links):

Oracle datatype: Should I use VARCHAR2 or CHAR

edited May 23, 2017 at 12:18

CommunityBot

11 silver badge

answered Sep 11, 2013 at 4:52

user272735

10.6k9 gold badges72 silver badges100 bronze badges

Comments

David Abraham · Accepted Answer · 2020-07-02 17:56:46Z

0

This may also happen if you have a faulty or accidental equation in your csv file. i.e - One of the cells in your csv file starts with an equals sign (=) (An excel equation) which will, in turn throw an error. If you fix, or remove this equation by getting rid of the equals sign, it should solve the ORA-06502 error.

answered Jul 2, 2020 at 17:56

David Abraham

1

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related