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.

On 27 Jul 2004 20:14:37 -0700, dn_perl@hotmail.com
(dn_perl@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

Sybrand Bakker <sybrandb@hccnet.nl> 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 ;
> >/
> >---------------------------------
> >
>
> 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.
>

If I run : 'select to_char(66,'00') from dual', I do
get a leading space. But when I run it with fm00,
the results screen's first row shows "TO_" (that is
3 characters), which suggests
to me that this time there is a trailing space. Is
my guess correct or not?

Of course one difference is that :
my_str char(2) ;
my_str = to_char(15,'format') ; works only with format = 'fm00'
and not with '00' format.
So my guess about trailing space looks shaky to me.
But then why does the first row show "TO_" instead of "TO" .