Re: 06502 - char string buffer too small error

On 27 Jul 2004 20:14:37 -0700, dn_perl_at_hotmail.com (dn_perl_at_hotmail.com) wrote:

>Today, I tried to run a piece of code which had run
>before but got an error.
>
>Contents of myfile.sql are :
>---------------------------------
>declare
>my_str char(2) ;
>my_number integer ;
>begin
>my_number := 15 ;
>my_str := to_char(my_number , '00') ;
>end ;
>/
>---------------------------------
>
>
>When I run @myfile.sql from sqlplus, I get the following error :
>ORA-06502: PL/SQL: numeric or value error: character string buffer too
>small
>ORA-06512: at line 6
>
>The script runs properly if I declare my_str as char(3) instead of
>char(2). But I know it used to run with my_str as char(2) only. What
>could be the matter? Do I have to check whether character-set or some
>such thing of the environment I am working in has changed?
>
>
>Thanks in advance.

to_char(...,'fm00') to avoid the extra space in front of the number. Try
select to_char(66,'00') from dual and compare it to select to_char(66,'fm00') and you will see in the former case you have a leading space.

--
Sybrand Bakker, Senior Oracle DBA