• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 594
  • Last Modified:

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)
0
jrcooperjr
Asked:
jrcooperjr
  • 3
  • 2
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
I believe Integers are signed but cannot verify that from the docs right now since I an on mobile.
0
 
sdstuberCommented:
to_char is trying to put a space in front of your digits  use "fm"



 vfour:= to_char(iunique,'fm0009') ;
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now