RE: CLOB's and Character Sets

From: Scott Canaan <srcdco_at_rit.edu>
Date: Wed, 13 Sep 2023 15:16:29 +0000
Message-ID: <MN2PR16MB297389C1E4A88A65BAACD9D4C5F0A_at_MN2PR16MB2973.namprd16.prod.outlook.com>

Special characters are those in languages other than English with accents, umlauts, ect. The customer says they show up fine in the “old” system, but are replaced by “?” in the “new” system.

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: Kim Berg Hansen <kibeha_at_gmail.com> Sent: Wednesday, September 13, 2023 11:13 AM To: Scott Canaan <srcdco_at_rit.edu>
Cc: Pierre Labrousse <Pierre.Labrousse_at_digora.com>; oracle-l_at_freelists.org Subject: Re: CLOB's and Character Sets

What special characters are going wrong? When I hear "special characters" I usually think of characters with ascii value > 127, but those should not exist in US7ASCII.

If there are characters with ascii value > 127 (8th bit set) and it is those characters that are going wrong, then the cause is that such characters should not exist in US7ASCII and the conversion that is happening from 7-bit ASCII to UTF cannot convert 8-bit characters (as they do not exist in US7ASCII.)

It's just a guess, but it's a possibility that they've somehow gotten 8-bit ASCII characters even though such ASCII values do not exist in US7ASCII. It can happen if clientside NLS_LANG is incorrectly setup, similar to this case: https://www.kibeha.dk/2018/05/corrupting-characters-how-to-get.html

Cheerio
/Kim

Regards

Kim Berg Hansen
Oracle ACE Director

Author of Practical Oracle SQL<https://www.apress.com/gp/book/9781484256169> http://www.kibeha.dk
kibeha_at_kibeha.dk<mailto:kibeha_at_kibeha.dk> _at_kibeha
<http://twitter.com/kibeha>

On Wed, Sep 13, 2023 at 5:03 PM Scott Canaan <srcdco_at_rit.edu<mailto: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<mailto: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<mailto:Pierre.Labrousse_at_digora.com>> Sent: Wednesday, September 13, 2023 10:56 AM To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>; Scott Canaan <srcdco_at_rit.edu<mailto: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) Mobile +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<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> de la part de Scott Canaan <srcdco_at_rit.edu<mailto:srcdco_at_rit.edu>> Envoyé : mercredi 13 septembre 2023 16:42 À : oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> <oracle-l_at_freelists.org<mailto: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<mailto:srcdco_at_rit.edu> | c: (585) 339-8659

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

Received on Wed Sep 13 2023 - 17:16:29 CEST

This message: [ Message body ]
Next message: Kurt Van Meerbeeck: "Re: CLOB's and Character Sets"
Previous message: Kim Berg Hansen: "Re: CLOB's and Character Sets"
In reply to: Kim Berg Hansen: "Re: CLOB's and Character Sets"
Next in thread: Kurt Van Meerbeeck: "Re: CLOB's and Character Sets"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message