Oracle Import - Character Set Problem
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Oracle Import - Character Set Problem

  1. #1
    Join Date
    Apr 2003
    Location
    Rhode Island
    Posts
    4

    Question 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
    kdonovan@edgewater.com

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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.
    Reddy,Sam

  3. #3
    Join Date
    Apr 2003
    Location
    Rhode Island
    Posts
    4
    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

  4. #4
    Join Date
    Aug 2001
    Posts
    267
    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.
    Raghu

  5. #5
    Join Date
    Aug 2001
    Posts
    267
    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

  6. #6
    Join Date
    Apr 2003
    Location
    Rhode Island
    Posts
    4
    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

  7. #7
    Join Date
    Aug 2001
    Posts
    267
    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 .
    Raghu

  8. #8
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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.
    Reddy,Sam

  9. #9
    Join Date
    Apr 2003
    Location
    Rhode Island
    Posts
    4
    Sam,

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

    KD

  10. #10
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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 :(
    Reddy,Sam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width