Hi Tom,

When migrating data from Oracle Rdb to Oracle 12c we have a large number of columns where the datatype in Rdb is char(n). The character set in Rdb is DEC_MCS and in 12c we will use AL32UTF8. The mapping of multibyte characters from DEC_MCS to AL32UTF8 works fine. The 12c database will be accessed by a legacy application that cannot handle variable length strings. Because of this, there are only two options for the string datatype in 12c:

VARCHAR2(n CHAR)

or

CHAR(n CHAR)


If we use VARCHAR2(n CHAR) then we will have to store all strings blank padded to their full length. There is no other option because the way the legacy application is designed. Changing this is not possible for political reasons.

The other option is to use CHAR(n CHAR) in which case the blank padding happens automatically.

We have found that if we use VARCHAR2 then predicates including those columns becomes very confusing as you would have to blank pad all strings to their exact length. If we for example have a CHAR(20 CHAR) column first_name and we look for all persons named Tom, the predicate has to be last_name = 'Tom '<--Tom is followed by exactly seven spaces in this string> If you don't put in exactly the right number of spaces (or use a PAD function) then the query will not return anything.

If we on the other hand use CHAR(n CHAR) then everything seems to work smoothly and intuitively because Oracle handles the padding automagically.

So, given the many times we have heard that there is never a good reason for using CHAR, is this an exception where it is better to use CHAR than VARCHAR2?

Would using VARCHAR2 give us any benefit vs. using CHAR? All the strings would be blank padded to their full length in both cases so from a space perspective it seems like a wash to me.