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.

Answer 1

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