Re: Using CHAR instead of VARCHAR2 for small fields

Tim Witort wrote:
>
> Michael E. Moores wrote:
> >
> > I have been designing Oracle tables, using
> > CHAR data type for fields that contain a small domain,
> > rather than using VARCHAR2.
> > An example would be a field "STATUS CHAR(8)",
> > and it's legal values are
> > {'NEW','OPENED','ASSIGNED','COMPLETE','QA'}.
> > I also used a CHAR(2) for a postal state code, which
> > can only contain exactly two characters.
> >
> > I used the CHAR type becuase it seemed impractical to use
> > a variable length string (VARCHAR2) to store this type.
> > I'm not concerned about space usage, and it seems the updates
> > will be faster on a fixed size field.
> >
> > However, when i look at some Oracle views, I see
> > fields of type VARCHAR(1), VARCHAR2(2).
> >
> > Any advise on this matter??
>
> Oracle strongly suggests implementing all character columns
> as VARCHAR2 type. The benefits of this in saving unused
> space and being compatible with all of their other products
> far outweigh the minute overhead incurred. If there is ANY
> chance you will use Developer/2000 products on your database,
> then you had BETTER use VARCHAR2 instead of CHAR. CHAR type
> columns act in very wierd and undesireable ways in Forms 4.5.
>
> -- TRW
I'd also like to add that CHAR doesn't work well with Pro*C either. I had a couple of CHAR(1) columns in my database and was not able to access them from a Pro*C program and had to change them to VARCHAR2(1) in order for Pro*C to acces them.

Regards
Ken Denny
kendenny_at_bnr.ca Received on Fri Sep 05 1997 - 00:00:00 CDT