Varying-Width Character Data

• You can create the following LOB tables:
  • Containing CLOB/NCLOB columns even if you use a varying-width CHAR/NCHAR database character set

  • Containing a type that has a CLOB attribute irrespective of whether you use a varying-width CHAR database character set

• You cannot create the following tables:
  • With NCLOBs as attributes of object types

CLOB, NCLOB Values are Stored Using 2 Byte Unicode for Varying-Width Character Sets

CLOB/NCLOB values are stored in the database using the fixed width 2 byte Unicode character set if the database CHAR/NCHAR character set is varying-width.

• Inserting Data. When you insert data into CLOBs, the data input can be in a varying-width character set. This varying-width character data is implicitly converted into Unicode before data is stored in the database.

• Reading the LOB. Conversely, when reading the LOB value, the stored Unicode value is translated to the (possibly varying-width) character set that you request on either the client or server.

  Note: All translations to and from Unicode are implicitly performed by Oracle.

NCLOBs store fixed-width data.

You can perform all LOB operations on CLOBs (read, write, trim, erase, compare,...) All programmatic environments that provide access to CLOBs work on CLOBs in databases where the CHAR/NCHAR character set is of varying-width. This includes SQL, PL/SQL, OCI, PRO*C, DBMS_LOB, and so on.

For varying-width CLOB data you need to also consider whether the parameters are specified in characters or bytes.

• If the database CHAR character set is varying-width then the CLOB is stored in ucs2 (utf-16).

• If the database NCHAR character set is varying-width then the NCLOB is stored as ucs2 (utf-16). Otherwise, the CLOB/NCLOB is stored in the database char/nchar character set respectively.

UTF-16 has same encoding as UCS2, but UTF-16 treats a surrogate pair as one character.

To summarize how CLOB and NCLOB values are stored:

• CLOB values are stored:
  • Using the database character set in single-byte character set DB

  • Using the two-byte Unicode in multi-byte character set DB

• NCLOB values are stored:
  • Using the NCHAR character set in single-byte or fixed-width NCHAR DB

  • Using the two-byte Unicode in multi-byte NCHAR DB

Using DBMS_LOB.LOADFROMFILE and Functions that Access OCI

In using the OCI, or any of the programmatic environments that access OCI functionality, character set conversions are implicitly performed when translating from one character set to another. However, no implicit translation is ever performed from binary data to a character set. When you use the loadfromfile operation to populate a CLOB or NCLOB, you are populating the LOB with binary data from the BFILE. In that case, you will need to perform character set conversions on the BFILE data before executing loadfromfile.

Note that if the character set is varying-width, UTF-8 for example, we store the lob value in the fixed-width UCS2 format.

Therefore, the data in the BFILE should be in the UCS2 character set instead of the UTF-8 character set if you're using dbms_lob.loadfromfile().

However, we recommend that you use the sql*loader instead of loadfromfile to load data into a clob/nclob because the sql*loader will take care of all necessary character set conversions.

Converting Between Client Character Set and UCS-2

There are APIs in cartridge service that can convert between client character set and UCS-2:

• OCIUnicodeToCharSet()

• OCICharSetToUnicode()

  See Also: Chapter 3, "LOB Programmatic Environments": "Offset and Amount Parameters: Fixed-Width Versus Varying-Width, Character or Byte For DBMS_LOB Package" "Offset and Amount Parameters: Fixed-Width Versus Varying-Width, Character or Byte With OCI"