sql loader, CLOBS and character sets

From: lee <lee_at_jamtoday.com>
Date: Wed, 12 Dec 2001 13:18:24 -0500
Message-ID: <3C179F70.188FDAEC_at_jamtoday.com>

Strangeness.....I loaded a document into a CLOB using sql loader's LOBFILE
feature, and specified the characterset of the CLOB as UTF8 (which is the same as the NLS_CHARACTERSET parameter of the target database.)

If I try to read the CLOB back using sql plus (set long 10000 then select the_clob
from the_table) then all seems well.

But when I use dbms_lob.substr or dbms_lob.read to see portions of my document as "real readable characters", no joy. The dbms_lob functions seem to be returning
two byte characters which I dont know how to render "readable".

Clearly it can be done, because sql plus can do it, so no data has been lost, only transformed into some form from which it is a mystery to me how to get it back except for spooling the whole mess out again via sql plus.

Anybody have a clue? Received on Wed Dec 12 2001 - 19:18:24 CET