Home » Developer & Programmer » Forms » ORA-06502 bugs me always
ORA-06502 bugs me always [message #80480] Mon, 30 September 2002 00:09 Go to next message
asad
Messages: 7
Registered: August 2002
Junior Member
hello everyone,
I was trying to execute this simple PL/SQL block.

1 declare
2 code number(4) default 0;
3 code_str varchar2(4);
4 begin
5 for cnt in 1..9 loop
6 code := code + 1;
7 code_str := to_char(code, '0999');
8 /* code_str := '000' || code; */
9 dbms_output.put_line(code_str);
10 end loop;
11* end;

But it's always bugging me:
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 7

I wonder why varchar2(4) is insufficient for '0001'...
Surprisingly when i didn't use to_char function
and use simply '000'||code (as in line 8),
varchar2(4) sufficed for code_str!!!!

can anyone khow what is the bug?
Re: ORA-06502 bugs me always [message #80484 is a reply to message #80480] Mon, 30 September 2002 03:37 Go to previous messageGo to next message
Venu
Messages: 44
Registered: September 1999
Member
increase the size from varchar2(4) to varchar2(5)
Re: ORA-06502 bugs me always [message #80493 is a reply to message #80484] Tue, 01 October 2002 17:22 Go to previous message
asad
Messages: 7
Registered: August 2002
Junior Member
Thanks for reply.
But the problem is that
I can't increase the size from varchar2(4) to varchar2(5). Because, i have to insert a varchar2(4) instance in a table.
note:
It seemed to me that TO_CHAR function was doing something what i did not expect.
TO_CHAR(1, '0999') returns a string whose length was 5!
That means that TO_CHAR inserted a whitespace before the formated string (it returns ' 0001').
When i use LTRIM(TO_CHAR(1, '0999')), it works! Too strange characteristics of TO_CHAR!!!
(ref: my Oracle is 9.2.0.1.0 - 64bit Production on Solaris8)
Previous Topic: installation of d2k on linux
Next Topic: Pro*C core dump when using EXEC SQL CONNECT
Goto Forum:
  


Current Time: Thu Feb 29 07:56:34 CST 2024