Re: Oracle unicode export/import

On May 8, 8:01 am, Andrew <a..._at_hotmail.com> wrote:
> I'm trying to migrate data from Oracle to SQL-Server. My client's
> existing supplier has given them an Orcale extract. I've imported it
> into my 10g instance but see ¿ instead of any extended ASCII accented
> characters.
>
> My instance was set up with US7ASCII character set and UTF8 national
> character set.
> (wizard didn't have an option for US7ASCII or similar just UTF16).
>
> Import process gives this output:
> ---------------------------------------------------------------------------­---------------------------------------------------
> Connected to: Oracle Database 10g Enterprise Edition Release
> 10.1.0.2.0 - Production
> With the Partitioning, OLAP and Data Mining options
> Export file created by EXPORT:V08.01.07 via conventional path
>
> Warning: the objects were exported by XXX, not by you
> import done in WE8MSWIN1252 character set and UTF8 NCHAR character set
> import server uses US7ASCII character set (possible charset
> conversion)
>
> export client uses US7ASCII character set (possible charset
> conversion)
> export server uses US7ASCII NCHAR character set (possible ncharset
> conversion)
> . importing XXX's objects into XXX
> . . importing table "TABLE" 1000 rows imported
>
> About to enable constraints...
> Import terminated successfully without warnings.
> ---------------------------------------------------------------------------­---------------------------------------------------
>
> Can anyone tell me where I'm going wrong please? I have looked at the
> existing application, powered by the database that has been exported,
> and it shows accented characters fine.
>
> Is it possible for me to import this or do I need to ask them to
> export it differently?
>
> Thanks,
> Andrew

The 'problem' stems from using a subset of WE8MSWIN1252 in your destination database (US7ASCII); any characters which correctly display in US7ASCII will also display properly in WE8MSWIN1252, however the converse is not true as you've discovered. You need to change the characterset for your destination database to WE8MSWIN1252 to allow all characters stored in your source database to be viewable in your destination. As WE8MSWIN1252 is a superset of US7ASCII (as I explained earlier) you should have no conversion issues when you change charactersets.

As you're using some release of 10g you should read this link:

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14225/ch2charset.htm#i1007203

It discusses how to change a character set after a database has been created.

David Fitzjarrell Received on Tue May 08 2007 - 09:22:23 CDT