Problem with To_Char string overflow (6502) error

Hi,
I've condensed my problem into a very simple procedure.  Here is the procedure:

CREATE OR REPLACE PROCEDURE test IS
    iunique         integer;
    vfour           varchar2(4);
    vfive           varchar2(5);
BEGIN
   iunique := 1330    ;
   vfive := to_char(iunique,'0009') ;
   vfour:= to_char(iunique,'0009') ;
END test;


When I execute this procedure, I get a 6502 error on the line using the to_char function with vfour.  The to_char line with vfive gives no error.

As I interpret things, I am converting a four-digit integer value to characters.  This should give me a four-character result.  

Shouldn't the four-character result fit in a four-character field?  

Any explanations as to why I'm getting the 6502 error when I'm trying to put the result in a four-character field, but no error when I put the result in a five character field?

(We're using Oracle 11g by the way)
LVL 1
jrcooperjrAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
to_char is trying to put a space in front of your digits  use "fm"



 vfour:= to_char(iunique,'fm0009') ;
0
 
slightwv (䄆 Netminder) Commented:
I believe Integers are signed but cannot verify that from the docs right now since I an on mobile.
0
 
sdstuberCommented:
and yes,  the space is so there is room to put "-" if the number was negative
 
and if it was negative, it would still fail because '-0009'  won't fit into 4 characters
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
jrcooperjrAuthor Commented:
Argghhh.. thanks for reminding me to remember the space for signs.... I'd also forgotten that FM in a mask suppresses things not just in date strings...
0
 
sdstuberCommented:
a split is probably in order, slightwv did mention signs first
0
 
slightwv (䄆 Netminder) Commented:
I do tend to agree about a split.

Just let us know if you agree.
0
 
jrcooperjrAuthor Commented:
I disagree about the split.... the key for me was the mention of "FM" to suppress the space... just knowing that there was a space for the sign would not --- in and of itself --- resolved my problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.