Re: CLOB's and Character Sets
- From: Kurt Van Meerbeeck <kurtvm@xxxxxxxxxx>
- To: srcdco <srcdco@xxxxxxx>
- Date: Wed, 13 Sep 2023 17:30:13 +0200 (CEST)
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.
bingo ... it think the source data is not correctly stored - those characters
can not exist in a 7bit codepage
Van: "Scott Canaan" <srcdco@xxxxxxx>
Aan: "Kim Berg Hansen" <kibeha@xxxxxxxxx>
Cc: "Pierre Labrousse" <Pierre.Labrousse@xxxxxxxxxx>, "oracle-l"
<oracle-l@xxxxxxxxxxxxx>
Verzonden: Woensdag 13 september 2023 17:16:29
Onderwerp: RE: CLOB's and Character Sets
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.
Scott Canaan ‘88
Sr Database Administrator
Information & Technology Services
Finance & Administration
Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520
[
mailto:srcdco@xxxxxxx ;| srcdco@xxxxxxx ] | 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: Kim Berg Hansen <kibeha@xxxxxxxxx>
Sent: Wednesday, September 13, 2023 11:13 AM
To: Scott Canaan <srcdco@xxxxxxx>
Cc: Pierre Labrousse <Pierre.Labrousse@xxxxxxxxxx>; oracle-l@xxxxxxxxxxxxx
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 ;|
https://www.kibeha.dk/2018/05/corrupting-characters-how-to-get.html ] ;
Cheerio
/Kim
Regards
Kim Berg Hansen
Oracle ACE Director
Author of [
https://www.apress.com/gp/book/9781484256169 ;| Practical Oracle SQL
]
[
http://www.kibeha.dk/ ;|
http://www.kibeha.dk ] ;
[
mailto:kibeha@xxxxxxxxx ;| kibeha@xxxxxxxxx ]
[
http://twitter.com/kibeha ;| @kibeha
]
On Wed, Sep 13, 2023 at 5:03 PM Scott Canaan < [
mailto:srcdco@xxxxxxx ;|
srcdco@xxxxxxx ] > 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
[
mailto:srcdco@xxxxxxx ;| srcdco@xxxxxxx ] | 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 < [
mailto:Pierre.Labrousse@xxxxxxxxxx ;|
Pierre.Labrousse@xxxxxxxxxx ] >
Sent: Wednesday, September 13, 2023 10:56 AM
To: [
mailto:oracle-l@xxxxxxxxxxxxx ;| oracle-l@xxxxxxxxxxxxx ] ; Scott Canaan <
[
mailto:srcdco@xxxxxxx ;| srcdco@xxxxxxx ] >
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
[
https://owa.digora.com/owa/redir.aspx?C=6cU4SWG0HEauWwQa74AWhWf917AeKdIIQ087knyB2eMPFQyUFrMl2EZ1Rw0CEMwBfg1hQ0wI9Zc.&URL=mailto%3apierre.labrousse%40digora.com%2520%250d
| pierre.labrousse@xxxxxxxxx ] [
https://owa.digora.com/owa/redir.aspx?C=6cU4SWG0HEauWwQa74AWhWf917AeKdIIQ087knyB2eMPFQyUFrMl2EZ1Rw0CEMwBfg1hQ0wI9Zc.&URL=mailto%3apierre.labrousse%40digora.com
| m ]
De : [
mailto:oracle-l-bounce@xxxxxxxxxxxxx ;| oracle-l-bounce@xxxxxxxxxxxxx ] <
[
mailto:oracle-l-bounce@xxxxxxxxxxxxx ;| oracle-l-bounce@xxxxxxxxxxxxx ] > de
la part de Scott Canaan < [
mailto:srcdco@xxxxxxx ;| srcdco@xxxxxxx ] >
Envoyé : mercredi 13 septembre 2023 16:42
À : [
mailto:oracle-l@xxxxxxxxxxxxx ;| oracle-l@xxxxxxxxxxxxx ] < [
mailto:oracle-l@xxxxxxxxxxxxx ;| oracle-l@xxxxxxxxxxxxx ] >
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
[
mailto:srcdco@xxxxxxx ;| srcdco@xxxxxxx ] | 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.
Other related posts:
