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     amp numero     d    length c      b    substr c  i  1      while i  lt   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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to oracle

Examples related to if-statement

Examples related to plsql

Examples related to while-loop