Re: CLOB's and Character Sets

CLOB is stored in UCS2 *if *the database is using a multi-byte characterset. When the database is using a single-byte characterset, CLOB is also single-byte.

(This is the reason when you convert a database with single-byte characterset to AL32UTF8 with Database Migration Assistant for Unicode, all CLOBs double in size.)

In this case, even if using datapump, the data is expected by the database to be in US7ASCII, i.e. only 7 bits - all 8-bit characters, the database considers to be invalid (which just is not a problem when reading them from a client with NLS_LANG setting of US7ASCII.)

Cheerio
/Kim

On Wed, Sep 13, 2023 at 8:55 PM Ilmar Kerm <ilmar.kerm_at_gmail.com> wrote:

> Since CLOB is always stored in UCS2, the source data is most likely
> correct.
> But LOBs do not work over database link, so trickery is needed - like
> coverting CLOB to VARCHAR on the source side, loosing non-ascii characters
> in the conversion. Try data pump instead (without database link).
>
> On Wed, 13 Sept 2023 at 17:03, Scott Canaan <srcdco_at_rit.edu> wrote:
>
>> The customer was trying to do an insert with a subselect over a database
>> link.
>>
>>
>>
>> *Scott Canaan ‘88*
>>
>> *Sr Database Administrator *Information & Technology Services
>> Finance & Administration
>>
>>
>> *Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
>>
>> *srcdco_at_rit.edu <srcdco_at_rit.edu>* | c: (585) 339-8659
>>
>> *CONFIDENTIALITY NOTE*: The information transmitted, including
>> attachments, is intended only for the person(s) or entity to which it is
>> addressed and may contain confidential and/or privileged material. Any
>> review, retransmission, dissemination or other use of, or taking of any
>> action in reliance upon this information by persons or entities other than
>> the intended recipient is prohibited. If you received this in error, please
>> contact the sender and destroy any copies of this information.
>>
>>
>>
>> *From:* Pierre Labrousse <Pierre.Labrousse_at_digora.com>
>> *Sent:* Wednesday, September 13, 2023 10:56 AM
>> *To:* oracle-l_at_freelists.org; Scott Canaan <srcdco_at_rit.edu>
>> *Subject:* RE: CLOB's and Character Sets
>>
>>
>>
>> Hello Scott,
>>
>>
>>
>> Since 8.1.5, CLOB columns are stored internally with AL16UTF16 charset,
>> no relation with NLS_CHARSET of the database (cf MOS doc : 257772.1).
>>
>>
>>
>> Wich tool do you used to do your transfert beween the two databases ?
>>
>>
>>
>> If you use expdp/impdp to transfert tables with CLOB column, it will work.
>>
>>
>>
>> Regards.
>>
>> Pierre
>>
>>
>>
>>
>>
>> *Pierre **LABROUSSE*
>> * Consultant DBA ORACLE (OCM 10g/11g/12c)*
>>
>> *M*obile +33 (0)7 56 05 27 38
>>
>> *pierre.labrousse**_at_**digora.co*
>> <https://owa.digora.com/owa/redir.aspx?C=6cU4SWG0HEauWwQa74AWhWf917AeKdIIQ087knyB2eMPFQyUFrMl2EZ1Rw0CEMwBfg1hQ0wI9Zc.&URL=mailto%3apierre.labrousse%40digora.com%2520%250d>
>> *m*
>> <https://owa.digora.com/owa/redir.aspx?C=6cU4SWG0HEauWwQa74AWhWf917AeKdIIQ087knyB2eMPFQyUFrMl2EZ1Rw0CEMwBfg1hQ0wI9Zc.&URL=mailto%3apierre.labrousse%40digora.com>
>>
>>
>> ------------------------------
>>
>> *De :* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> de
>> la part de Scott Canaan <srcdco_at_rit.edu>
>> *Envoyé :* mercredi 13 septembre 2023 16:42
>> *À :* oracle-l_at_freelists.org <oracle-l_at_freelists.org>
>> *Objet :* CLOB's and Character Sets
>>
>>
>>
>> I have a customer that is trying to migrate CLOB data from one database
>> to another. The source database was created using the US7ASCII
>> characterset. The destination database was created using the AL32UTF8
>> characterset. The issue is that special characters are not transferring
>> properly. In the destination database, all she sees is “?” in place of the
>> character.
>>
>>
>>
>> Other than rebuilding the new database with the US7ASCII characterset,
>> which will put the project way behind schedule, how can this be fixed? I
>> was under the impression that the AL32UTF8 characterset was a superset of
>> US7ASCII and should be able to handle the special characters.
>>
>>
>>
>> *Scott Canaan ‘88*
>>
>> *Sr Database Administrator *Information & Technology Services
>> Finance & Administration
>>
>>
>> *Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
>>
>> *srcdco_at_rit.edu <srcdco_at_rit.edu>* | c: (585) 339-8659
>>
>> *CONFIDENTIALITY NOTE*: The information transmitted, including
>> attachments, is intended only for the person(s) or entity to which it is
>> addressed and may contain confidential and/or privileged material. Any
>> review, retransmission, dissemination or other use of, or taking of any
>> action in reliance upon this information by persons or entities other than
>> the intended recipient is prohibited. If you received this in error, please
>> contact the sender and destroy any copies of this information.
>>
>>
>>
>
>
> --
> Ilmar Kerm
>

--
http://www.freelists.org/webpage/oracle-l