4 Replies Latest reply on Jun 26, 2014 7:06 AM by 1486982

    Issue with importing data , ?? chars instead of arabic chars. please help.

    d8eecf4e-5906-4615-bb5e-2dff8fab593f

      Hello

       

      I'm having an issue, I'm trying to import a .dmp file that was probably exported using the orignial export , not data pump as I tried data pump and it didn't work.

       

      anyway the issue is that the data has arabic characters that are appearing as ?? .

       

      the following is from the log file that came with the .dmp file:

       

      Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

      With the Partitioning, OLAP, Data Mining and Real Application Testing options

      Export done in AR8MSWIN1256 character set and AL16UTF16 NCHAR character set

      server uses WE8MSWIN1252 character set (possible charset conversion)

       

      when I first ran the import I got the following warnings:

       

      import done in US7ASCII character set and AL16UTF16 NCHAR character set

      import server uses AL32UTF8 character set (possible charset conversion)

      export client uses AR8MSWIN1256 character set (possible charset conversion)

       

      here is what I did:

      1- Converted the character set to AR8MSWIN1256 so it matched the export setings ( had to shutdown database ).

      2- changed the NLS_LANG to be ARABIC_UNITED ARAB EMIRATES.AR8MSWIN1256.

      3- ran the import again , it said that import is done in AR8MSWIN1256 character set and AL16UTF16 NCHAR character set. which matches the export settings.

      4- opened SQL Developer to check the data:  result is the same ! still seeing ?? instead of arabic chars.

       

      note that some columns are showing in arabic while others are not. this is the same before and after doing the changes to NLS_LANG.

       

      I'm running out of options here, pls guide.

      could it be "server uses WE8MSWIN1252 character set (possible charset conversion)" line that is causing this. if yes how can I change what the server is using , tried but all I found was the same NLS_Lang which I already changed.

       

      Thanks.

        • 1. Re: Issue with importing data , ?? chars instead of arabic chars. please help.
          Sergiusz Wolicki-Oracle

          "Server uses WE8MSWIN1252 character set (possible charset conversion)" pertains to the database character set of the source database so you cannot do anything about it, I guess, unless you have control over that database. If you are sure the source database contained Arabic data, then this data was stored incorrectly labeled as Western European, using pass-through configuration. If I remember correctly, Export 11.2.0.3 stores the dump file content in the source DB character set and not in the export NLS_LANG character set (Export done in AR8MSWIN1256). This would allow the data to be rescued. However, if I am mistaken and the export data is still stored in the NLS_LANG character set (like it used to be in older releases), then the Export already corrupted the data by converting it from WE8MSWIN1252 to AR8MSWIN1256 and nothing can be done.

           

          To rescue the data you need to:

           

          1. Create an empty temporary database with WE8MSWIN1252 character set.

          2. Import the dump file into this database, setting NLS_LANG=.WE8MSWIN1252.

          3. Change the database character set of the temporary database to AR8MSWIN1256 (assuming the original data is in AR8MSWIN1256 and not, for example, AR8ISO8859P6). This is achieved with csscan utility and csalter.plb script. There are MOS notes describing this process.

          4. Move the data from the temporary database to the target database with Data Pump.

           

          I am afraid there is no simpler way.

           

          Thanks,

          Sergiusz

          • 2. Re: Issue with importing data , ?? chars instead of arabic chars. please help.
            d8eecf4e-5906-4615-bb5e-2dff8fab593f

            Thanks for the very helpful reply.

             

            I'll perform those steps and get back to you.

             

            Thanks again.

            • 3. Re: Issue with importing data , ?? chars instead of arabic chars. please help.
              Tarek Salama

              Hi,

               

              Can you please log on to the database by SQLPLUS, if the character set in Arabic you will find the SQLPLUS log on screen in Arabic (this is from windows machine).

               

              make sure that the Arabic key-board is installed, then from the regedit serch for NLS_LANG and change all the found values to AMERICAN_AMERICA.AR8MSWIN1256, then test any select from sqqlus or SQL-Developer.

               

              I hope this help.

               

              Thanks.

               

              Tarek

              • 4. Re: Issue with importing data , ?? chars instead of arabic chars. please help.
                1486982

                Hi,

                 

                The first few sentences states...

                "" the issue is that the data has arabic characters that are appearing as ?? .the following is from the log file that came with the .dmp file:  ""

                 

                Implies.. concerns with export using exp..

                Ensure the appropriate CHAR & NCHAR settings at the Source. Advice, to re-take the export using expdp and validate again the export logfile.

                 

                Thanks and Regards

                Robertson

                Oracle Database Architect

                Riyadh - Kingdom of Saudi Arabia