* CHAR offers no advantage at all over VARCHAR2, and it *does* have the length byte as VARCHAR2 (can be verified with a block dump) http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:123212348063 "A CHAR datatype and VARCHAR2 datatype are stored identically (eg: the word 'WORD' stored in a CHAR(4) and a varchar2(4) consume exactly the same amount of space on disk, both have leading byte counts). ... The only time I personally use a CHAR type is for CHAR(1). And that is only because its faster to type char(1) then varchar2(1) -- it offers no advantages." * And it "offers" annoying disadvantages. Test in 11.2.0.1.0: SQL> create table t (c char(2)); Table created. SQL> insert into t values ('x'); 1 row created. SQL> select vsize(c), c || '*' from t; VSIZE(C) C|| ---------- --- 2 x * SQL> select * from t where c = 'x'; <-- no need to say ='x ' or like 'x%' C -- x SQL> select * from t where c = 'x '; <-- one space to make up to 2 chars C -- x SQL> select * from t where c = 'x '; <-- surprise! 3 chars in total, still finds the row C -- x SQL> select * from t where c = 'x '; <-- or more chars, doesn't matter C -- x SQL> select * from t where c like 'x % '; <-- but not when there's a wild char no rows selected SQL> select * from t where c like 'x %'; no rows selected SQL> select * from t where c like 'x%'; C -- x SQL> select * from t where c like 'x %'; C -- x SQL> select * from t where c like 'x %'; <-- wild char seems to wake up the length check no rows selected SQL> select * from t where rtrim(c) = 'x'; C -- x SQL> select * from t where rtrim(c) = 'x '; <-- rtrim works as expected, no trailing space allowed, even for char type no rows selected SQL> update t set c = rtrim(c); 1 row updated. SQL> select c || '*' from t where rtrim(c) = 'x'; <-- rtrim() above won't change the nature of char C|| --- x * SQL> update t set c = ' '; <-- set to a space 1 row updated. SQL> select c || '*' from t where c = ' '; C|| --- * SQL> alter table t modify c not null; Table altered. SQL> update t set c = rtrim(c); <-- rtrim() would change the space (2 spaces actually) to null update t set c = rtrim(c) * ERROR at line 1: ORA-01407: cannot update ("YHUANG"."T"."C") to NULL SQL> alter table t modify c null; Table altered. SQL> update t set c = rtrim(c); 1 row updated. SQL> select c || '*' from t; <-- char(2) contains either two chars or null value C|| --- * * Some applications such as Lawson *can* have tables built using varchar2 instead of the stupid default, char. http://www.lawsonguru.com/Forums/tabid/96/aft/6097/Default.aspx "06/05/2009 11:29 AM This [building Lawson tables with varchar2 type] is covered in detail in the book 'Lawson Administration: Data Access Using Oracle'". * Is it possible to alter char to varchar2 after data is loaded into the table? Change char to same-length varchar2 and update the row: SQL> create table testchar (x char(10)); Table created. SQL> insert into testchar values ('hello'); 1 row created. SQL> select length(x) from testchar; LENGTH(X) ---------- 10 SQL> alter table testchar modify x char(5); alter table testchar modify x char(5) * ERROR at line 1: ORA-01441: cannot decrease column length because some value is too big SQL> alter table testchar modify x varchar2(5); alter table testchar modify x varchar2(5) * ERROR at line 1: ORA-01441: cannot decrease column length because some value is too big SQL> alter table testchar modify x varchar2(10); Table altered. SQL> select length(x) from testchar; LENGTH(X) ---------- 10 SQL> update testchar set x = regexp_replace(x,' +$'); <-- remove all trailing spaces 1 row updated. SQL> select length(x) from testchar; LENGTH(X) ---------- 5 SQL> select x from testchar; X ---------- hello * Conclusion: Always use varchar2, except for saving a little typing when you're absolutely sure the string length is fixed never ending with space(s). I wonder how CHAR data type came into existence?