Datatypes - Expert Oracle Database Architecture - page 517

So here, the search for the VARCHAR2 string worked, but the CHAR column did not. The VARCHAR2 bind variable will
not be promoted to a CHAR(20) in the same way as a character string literal. At this point, many programmers form
the opinion that “bind variables don't work; we have to use literals.” That would be a very bad decision indeed.
The solution is to bind using a CHAR type:

EODA@ORA12CR1> variable char_bv char(20)
EODA@ORA12CR1> exec :char_bv := 'Hello World';
PL/SQL procedure successfully completed.
EODA@ORA12CR1>
EODA@ORA12CR1> select * from t where char_column = :char_bv;
CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
Hello World Hello World
EODA@ORA12CR1> select * from t where varchar2_column = :char_bv;
no rows selected

However, if you mix and match VARCHAR2 and CHAR , you'll be running into this issue constantly. Not only that, but
the developer is now having to consider the field width in her applications. If the developer opts for the RPAD() trick
to convert the bind variable into something that will be comparable to the CHAR field (it is preferable, of course, to pad
out the bind variable, rather than TRIM the database column, as applying the function TRIM to the column could easily
make it impossible to use existing indexes on that column), she would have to be concerned with column length
changes over time. If the size of the field changes, then the application is impacted, as it must change its field width.
It is for these reasons—the fixed-width storage, which tends to make the tables and related indexes much larger
than normal, coupled with the bind variable issue—that I avoid the CHAR type in all circumstances. I cannot even
make an argument for it in the case of the one-character field, because in that case it is really of no material difference.
The VARCHAR2(1) and CHAR(1) are identical in all aspects. There is no compelling reason to use the CHAR type in that
case, and to avoid any confusion, I “just say no,” even for the CHAR(1) field.

Character String Syntax
The syntax for the four basic string types is straightforward, as described in Table 12-1 .

Table 12-1. Four Basic String Types

String Type <SIZE>

VARCHAR2( <SIZE> <BYTE|CHAR> )

A number between 1 and 4,000 for up to 4,000 bytes of storage. In the
following section, we'll examine in detail the differences and nuances of the
BYTE versus CHAR modifier in that clause. Starting with 12 c , you can configure
a VARCHAR2 to store up to 32,767 bytes of information.

CHAR( <SIZE> <BYTE|CHAR> ) A number between 1 and 2,000 for up to 2,000 bytes of storage.

NVARCHAR2( <SIZE> )

A number greater than 0 whose upper bound is dictated by your national
character set. Starting with 12 c , you can configure a NVARCHAR2 to store up to
32,767 bytes of information.

NCHAR( <SIZE> ) A number greater than 0 whose upper bound is dictated by your national

character set.