This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Oct 16, 2012 3:23 AM by Sergiusz Wolicki (Oracle) RSS

Export NLS_CHARACTERSET WE8PC850 & IMPORT TO AR8MSWIN1256

967630 Newbie
Currently Being Moderated
hi ,

I want to export data from below mentioned database

O.s : Linux
DB : 9i
NLS_CHARACTERSET=WE8PC850

And Import to below mentioned database.

O.S : Linux
DB: 11gr2
NLS_CHARACTERSET=AR8ISO8859P6 OR AR8MSWIN1256

I am having issue with arabic fonts after normal export and import.

I tried the following step

-created new database with WE8PC850 And Imported data. Junk arabic
-created new database with AR8ISO8859P6 And imported data , junk arabic

please guide steps need to do export and import with various NLS_CHARACTERSET

Thanks
  • 1. Re: Export NLS_CHARACTERSET WE8PC850 & IMPORT TO AR8MSWIN1256
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    what is your OS Version ?
  • 2. Re: Export NLS_CHARACTERSET WE8PC850 & IMPORT TO AR8MSWIN1256
    Justin Cave Oracle ACE
    Currently Being Moderated
    WE8PC850 is a Western European character set. It doesn't support Arabic data.

    Are you stating that you are (incorrectly) storing Arabic data in the WE8PC850 database? If so, what character set(s) is the WE8PC850 data actually stored in? Can you change the source database character set before doing the export?

    Justin
  • 3. Re: Export NLS_CHARACTERSET WE8PC850 & IMPORT TO AR8MSWIN1256
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Also Read
    Difference between WE8MSWIN1252 and WE8ISO8859P1 characterset [ID 341676.1]
    Export-Import iSR - How to Find the Database Characterset [ID 175630.1]
  • 4. Re: Export NLS_CHARACTERSET WE8PC850 & IMPORT TO AR8MSWIN1256
    967630 Newbie
    Currently Being Moderated
    hi Justin,

    we are using WE8PC850 characterset for 9i DB and stored arabic data correctly, Now for 11g we prefer to use AR8MSWIN1256 As per oracle recomendation. but while importing to new DB 11g . we are facing this issue.
    I didnt change any character set in source DB.

    Thanks for your immediate response.

    Ameer
  • 5. Re: Export NLS_CHARACTERSET WE8PC850 & IMPORT TO AR8MSWIN1256
    JohnWatson Guru
    Currently Being Moderated
    O.S : Linux
    DB: 11gr2
    NLS_CHARACTERSET=AR8ISO8859P6 OR AR8MSWIN1256>
    Are you sure about this? According to the docs, only unicode is upported for the NLS character set.
  • 6. Re: Export NLS_CHARACTERSET WE8PC850 & IMPORT TO AR8MSWIN1256
    967630 Newbie
    Currently Being Moderated
    Hi John,

    I am not sure about this , can u guide me which is the correct character set for arabic on DB 11g over Linux O.S

    My target is to acheive result in arabic without junk .Pls guide me

    Thnks

    Edited by: 964627 on Oct 15, 2012 6:05 AM
  • 7. Re: Export NLS_CHARACTERSET WE8PC850 & IMPORT TO AR8MSWIN1256
    JohnWatson Guru
    Currently Being Moderated
    Well, I think best practice is undoubtedly to use unicode. Either AL32UTF8 or, if you have a lot of multi-byte data, AL16UTF16.
  • 8. Re: Export NLS_CHARACTERSET WE8PC850 & IMPORT TO AR8MSWIN1256
    967630 Newbie
    Currently Being Moderated
    you mean .. i need to set NLS_NCHAR_CHARACTERSET to AL32UTF8
    (OR) NLS_CHARACTERSET.

    Please confirm , with this character set if I import data from WE8PC850 , it will show my arabic data without junk??

    Thanks for support
  • 9. Re: Export NLS_CHARACTERSET WE8PC850 & IMPORT TO AR8MSWIN1256
    JohnWatson Guru
    Currently Being Moderated
    I have to apologize, I mis-read your question.
    So, to start again, I would try using unicode for NLS characterset. That must be best practice. Theoretically, all your Arabic will go into it.
  • 10. Re: Export NLS_CHARACTERSET WE8PC850 & IMPORT TO AR8MSWIN1256
    Justin Cave Oracle ACE
    Currently Being Moderated
    964627 wrote:
    we are using WE8PC850 characterset for 9i DB and stored arabic data correctly,
    Unfortunately, the data was not stored correctly. The only way this could appear to work would be if all the applications that access the database were misconfigured to lie to Oracle in exactly the same way. If every client told the database that a particular data stream was PC-850 when it was, in fact, Windows-1256 and every client asked for PC-850 data from the database but actually treated it like Windows-1256, then things would appear to work. This is similar to the incorrect NLS_LANG setup from the NLS_LANG FAQ.

    The problem comes that as soon as you start trying to use an application like Oracle's export and import utilities that don't know how to lie to Oracle in exactly this way, particularly when you want character set conversion to happen, you have to face the reality that the source data is not stored correctly.
    Now for 11g we prefer to use AR8MSWIN1256 As per oracle recomendation. but while importing to new DB 11g . we are facing this issue.
    I didnt change any character set in source DB.
    What character set is the source data actually stored in?
    Can you change the character set of the source database before exporting?

    Justin
  • 11. Re: Export NLS_CHARACTERSET WE8PC850 & IMPORT TO AR8MSWIN1256
    967630 Newbie
    Currently Being Moderated
    Thanks for updates ,


    character set in source DB is WE8PC850 , I'M 100% sure abt that.

    If i change characterset of source DB before export as AR* , whether i will recive arabic data without junk???

    let me try and update you.

    Ameer
  • 12. Re: Export NLS_CHARACTERSET WE8PC850 & IMPORT TO AR8MSWIN1256
    Justin Cave Oracle ACE
    Currently Being Moderated
    964627 wrote:
    character set in source DB is WE8PC850 , I'M 100% sure abt that.
    I believe you that the NLS_CHARACTERSET of the source database is WE8PC850. If you are storing Arabic data in the database, however, the data must not actually be WE8PC850. The data must be encoded in some other character set and you have to have incorrect NLS_LANG settings on all the clients to hide that fact. The question is-- what character set is the data actually encoded in? And is it all encoded using one character set or do you have a mix of different character sets?
    If i change characterset of source DB before export as AR* , whether i will recive arabic data without junk???
    If you can change the character set of the source database (assuming it is done correctly-- this is probably going to be a non-trivial operation with an ancient 9i database), then you can use the export and import utilities to move the data. If you cannot change the character set of the source database, then you may be stuck writing the data out to flat files and using SQL*Loader to load it back in.

    Note also that there is a Globalization Support where you may want to move this question. Sergiusz Wolicki may well have some better ideas about how to move the data successfully (though he'll definitely need the questions I'm asking answered).

    Justin
  • 13. Re: Export NLS_CHARACTERSET WE8PC850 & IMPORT TO AR8MSWIN1256
    orafad Oracle ACE
    Currently Being Moderated
    964627 wrote:
    character set in source DB is WE8PC850 , I'M 100% sure abt that.
    WE8PC850 is Oracle's name for IBM code page 850. Also sometimes called "DOS Latin-1", I believe.

    What Arabic characters could you identify from its character repertoire? http://msdn.microsoft.com/en-us/goglobal/cc305160
    (or google for code page 850 ibm.com if you want to verify with a second source)


    If you say you export and import into another database with database character set WE8PC850 and get "junk" character data, I wonder what export parameters were used?

    Did you run csscan on the source db?
  • 14. Re: Export NLS_CHARACTERSET WE8PC850 & IMPORT TO AR8MSWIN1256
    967630 Newbie
    Currently Being Moderated
    I agrreed with you regarding WE8PC850 characterset ,

    I said I am importing data to new database with characterset AR*(11G) , exporting with WE8PC850 (9I) which was already existing source DB in production.

    - If I import data using the same characterset WE8PC850 in 11g test DB , Arabic appear clear when i set my regedit for NLS_LANG in client as WE8PC850.
    - For my 11gFMW forms and report server , if i set as NLS_LANG IN default.env as AR8mswin1256 , it appears junk.

    Hope i'm not confusing you ..

    thanks for support

    Ameer
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points