Re: VARCHAR2 vs. CHAR

In article <348F49FB.7F7_at_mindspring.com> Doogie <jvivona_at_mindspring.com> wrote:

> No... I have eperience where Oracle though it was violating a unique
> index becuase the table columns where defined as CHAR instead of
> VARCHAR2.
>
> One of the other things is that VARCHAR2 does not exist in Oracle 6, it
> was a new type in Oracle 7.
>
> Watch out for the data conversion. Either export the data, change the
> columns, truncate the table and re-import the data or you'll need to
> write a script to trunacte the data in each column (Oracle does not
> automatically trim the data just becuase you changed the data type - I
> got burnt the hard way on this)....
>

 I have been burned more than once on the CHAR datatype and it's padding of hardcoded spaces to fill up the length. When you query you have to include the hard coded spaces or you have to do a function to trim off the spaces. In the latter if the function is in your where statement, it can not then uses indexes. I hate CHAR so much that even if I am creating a a character with a width of 1, like a y or n field, I will still use VARCHAR(2).

John Jones
DB Basics, Inc.
johnj_at_dbbasics.com
http://www.dbbasics.com Received on Thu Dec 11 1997 - 00:00:00 CST