Re: CLOB's and Character Sets

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