Oracle Import - Character Set Problem

All,

I am having a problem with Oracle Import that has me pulling my hair out. Here’s the situation.

I have a client providing me an Oracle Export file (pertinent portion of the export log is below) 8i is running on NT 4 SP6a.

*****************
Connected to: Oracle8i Release 8.1.6.0.0 - Production
JServer Release 8.1.6.0.0 - Production
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
Note: grants on tables/views/sequences/roles will not be exported
Note: indexes on tables will not be exported
Note: constraints on tables will not be exported

About to export specified tables via Conventional Path ...
Current user changed to WEBSITE
. . exporting table BLA_BLA 99 rows exported
Export terminated successfully without warnings.
**************************

I am attempting import this file to a 9i server that has the character set WE8MSWIN1252. Before I run my import I set the following environmental variables:
SET NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
SET NLS_NCHAR_CHARACTERSET=WE8ISO8859P1

I run my import and some characters are converted to ¿. (pertinent portion of the import log is below)

**********************
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Export file created by EXPORT:V08.01.06 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
export server uses WE8ISO8859P1 NCHAR character set (possible ncharset conversion)
. importing SYSTEM's objects into SYSTEM
. importing WEBSITE's objects into WEBSITE
. . importing table "BLA_BLA" 99 rows imported
Import terminated successfully without warnings.
*********************

I suspect I am having the character conversion issue because the import thinks its using AL16UTF16 as the import NCHAR character set but I am setting it to WE8ISO8859P1 before I do the import. I am sure that the problem is not the export because I have viewed the export dump file and the ¿ character is not there.

Anyone have any ideas on how to resolve this?

Thanks,

Kevin Donovan
[email protected]

May be you have some non ASCII characters in dump file. Did you try setting

NLS_LANG=AMERICAN_AMERICA.UTF8

UTF8 is superset of ASCII.

Sam,

Just tried what you suggested and no luck, same results.
I do know that some of the characters in the dump are extended ASCII characters (i.e. > ASC(127)) But I do know they exist in the character set I am using because I can enter data and retrieve data using the same characters.

I even went as far as recreating the database using WE8ISO8859P1 as the character set and ended up with the same results.

I'm not sure if it makes a difference or not but I am see this behavior in columns typed as CLOBS.

Thanks,

KD

Are you trying from the same machine where DB is running or doing from other box. Is the version is same (Exp/imp) when it got exported.

Since I got the same problem before when I am trying to import using export done by 8.1.7 client . And I am using 9.0.1 client to import from remote machine.

Raghu,

The export is done using Oracle8i Release 8.1.6.0.0 and the import
using Oracle9i Enterprise Edition Release 9.2.0.1.0. I do not have any IP connection to the 8i machine to run the export using the 9i client.

KD

Did you get the chance to check oracle matrix . For what version of DB you can use different version clients. Since ORACLE 9i by default uses different character set than 8i. Myself I got lot of problems to import as well as loading data using direct path between 9i clients vs 8i Database .

Excerpt from Metalink Note #15095.1:

Regarding the National Characterset:
------------------------------------

NCLOBs are exported/imported in UCS-2/AL16UTF16.

NCHAR/NVARCHAR2s are exported in the server's national character set.

So this is something you can't influence.

Sam,

Does this mean I pretty much stuck and theres nothing I can do with respect to an import?

KD

I'm not sure, unless you could be able to get to the server and see possible options with different NLS settings for Export.

Otherwise, You can try importing this if you have 8i installtions and play with it exporting with different NLS settings and see what helps.Here all I'm saying to you is trial and error if you can't seek help from Oracle on this issue.

Whichever is faster, read few more docs from metalink to figure out the issue OR T&E to figure out yourself.

Always big head ache with this NLS settings and character set conversions and lobs :(

Hi,
In import log I see that your operating system is Windows since server is using WE8MSWIN1252 character set. Only NCHAR column datatype will use Unicode character set so are you getting converted characters in columns with NCHAR (or NVARCHAR etc.) data type?
Then you are pretty much stuck I think. If not, I would do import with chacater set which your server is using i.e. WE8MSWIN1252 and set language both in the register and on the session level before import starts:
in the Registery NLS_LANG=American_America.WE8MSWIN1252 and in DOS set NLS_LANG=American_America.WE8MSWIN1252.
Alla