Re: CLOB's and Character Sets

Hi

First thing that strikes me as odd ... us7ascii does not really have special characters - so what specific characters do you mean ?

I can think of 3 things that might cause this : 1) the source data was wrong to begin with - a terminal/client program with for example an 8bit (or mutlipbytes) codepage (like WE8ISO8859Px or unicode) was used to insert into a 7bit us7ascii database. As long as you query the data with the same settings this *might* go ok ... but once you migrate to another codepage the data is wrong. (and it stays wrong) I have seen this only once a very long time ago.

2) the data is ok - but maybe your settings are wrong on the terminal you query the data. Remember, the NLS settings of your oracle client must match the codepage of your client environment/terminal. Typical example is windows, with a NLS_LANG set to WE8MSWIN1252 - when you insert a special character in the graphical sqlplus windows client and you then open a dos cmd box and query the same data with command line sqlplus, you'll get a '?' question mark. This is because dos has another codepage (us8pc850) - so you need to check, what do i use to query the data and with is its code page and set oracle nls accordingly.

3) Something might have gone wrong with the export import - so source db is us7ascii - let's say I export the data with client nls set to us7ascii. No character set conversion was done, because db nls is us7ascii, and the export client is us7ascii. If i'm not mistaken then you need to set the nls character of the import client on the new environment also to us7ascii when doing the import. That way, characterset conversion between the client us7ascii and the database AL32UTF8 occurs. If you set the nls (AL32UTF8) of the client for import, the same as the nls of the database (AL32UTF8), then no characterset conversion occurs.

Hope this helps,
Kind regards,
Kurt

Van: "Scott Canaan" <srcdco_at_rit.edu> Aan: "oracle-l" <oracle-l_at_freelists.org> Verzonden: Woensdag 13 september 2023 16:42:47 Onderwerp: 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_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.

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