|Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 1 (9.0.1)
Part Number A88879-01
Basic LOB Components, 3 of 5
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.
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.
NCLOBs store fixed-width data.
You can perform all LOB operations on
compare,...) All programmatic environments that provide access to
CLOBs work on
CLOBs in databases where the
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.
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:
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.
There are APIs in cartridge service that can convert between client character set and UCS-2: