| ORA-06502 bugs me always [message #80480] |
Mon, 30 September 2002 00:09  |
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 #80493 is a reply to message #80484] |
Tue, 01 October 2002 17:22  |
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)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
