Re: NUMBER(p) vs. INTEGER data type and CHAR(x) vs. VARCHAR2(x) in Oracle 10g and 9i - advantages and disadvantages

On Jun 12, 4:43 pm, dana..._at_yahoo.com wrote:
> What are the main differences between NUMBER(p) and INTEGER for
> storing integer values? Is there any difference with space or
> performance?
>
> I understand using NUMBER(p) with a value for Precision only (no
> scale) gives you an integer, constrained by a max of p digits. Is
> INTEGER the same as NUMBER(38), since 38 is the max precision?
>
> I've also heard it said that Oracle Corp recommends, or once
> recommended, using the NUMBER type with no precision or scale
> specified; this way it accepts whatever type of number you throw at it
> exactly as it is. I can see how this would be bad, e.g. if you intend
> to hold only integers this would allow someone to enter floating point
> numbers. Is it only good to use NUMBER with no P + S when you expect
> floating point values, but don't know how many decimal places you
> might need?
>
> Also, these days, is there any real advantage to using something like
> CHAR(5) vs. VARCHAR2(5) in terms of storage and performance? Seems
> with CHAR, you'll always use 5 characters regardless of what you enter
> (zeros padded when < 5 chars). But with VARCHAR2(5), you'll only store
> as many characters as are entered.
>
> What I'm really after--is there any advantage these days to using the
> CHAR data type? Seems like VARCHAR2 offers more flexibility *if* the
> length of values in a column may increase or decrease in the future,
> where CHAR is best if one suspects there will always only be X
> characters for values in a field. So is it a stylistic thing or a
> domain thing in choosing one over the other?

See http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/datatypes.htm#sthref869

It goes beyond stylistic, CHAR is a pita. I was sooooo happy when the enterprise software I work on finally converted to varchar2, there were so many gotchas time after time after time...

jg

--
@home.com is bogus.
"...from Pasadena nudes... news about the porn trial..." - heard on
KNX news radio.