PL/SQL CLOB Comparison Rules

Like VARCHAR2s, when a CLOB is compared with another CLOB or compared with a VARCHAR2, a set of rules determines the comparison. The rules are usually called a "collating sequence". In Oracle, CHARs and VARCHAR2s have slightly different sequences due to the blank padding of CHARs.

CLOBS Follow the VARCHAR2 Collating Sequence

As a rule, CLOBs follow the same collating sequence as VARCHAR2s. That is, when a CLOB is compared, the result is consistent with if the CLOB data content is retrieved into a VARCHAR2 buffer and the VARCHAR2 is compared. The rule applies to all cases including comparisons between CLOB and CLOB, CLOB and VARCHAR2, and CLOB and CHAR.

Varying-Width Character Sets: VARCHAR2s and CLOBs

When the database character set is varying-width, VARCHAR2s and CLOBs differ as follows:

• For VARCHAR2s, the varying-width characters are stored in the table

• For LOBs, the varying-width characters are converted to UCS2 characters to be stored in the database

How to Compare CLOBs and VARCHAR2s in a Varying-Width Character Set

To compare CLOB and VARCHAR2s in a varying-width database follow this procedure:

• To compare a CLOB with another CLOB, compare their UCS2 values

• To compare a CLOB with a VARCHAR2, convert the VARCHAR2 data to UCS2 first, then compare the UCS value with that of the CLOB

  Note: When a CLOB is compared with a CHAR string, it is always the character data of the CLOB being compared with the string. Likewise, when two CLOBs are compared, the data content of the two CLOBs are compared, not their LOB locators.

It makes no sense to compare CLOBs with non-character data, or with BLOBs. An error is returned in these cases.