This discussion is archived
10 Replies Latest reply: Nov 13, 2012 12:44 PM by Sergiusz Wolicki (Oracle) RSS

Character set conversion in oracle

845109 Newbie
Currently Being Moderated
Hi All,
I am currently facing a strange issue while loading a csv into oracle BE table.
The data is coming as Técnicas but I need to store it as Técnicas . I think there is some character set issue for which data is coming as in distorted manner .

I am using oracle 10G enterprise edition .

Any help is greatly appreciated.
  • 1. Re: Character set conversion in oracle
    sb92075 Guru
    Currently Being Moderated
    842106 wrote:
    Hi All,
    I am currently facing a strange issue while loading a csv into oracle BE table.
    The data is coming as Técnicas but I need to store it as Técnicas . I think there is some character set issue for which data is coming as in distorted manner .

    I am using oracle 10G enterprise edition .

    Any help is greatly appreciated.
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ

    what do you observe when you inspect the actual content of the CSV file?
  • 2. Re: Character set conversion in oracle
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl post exact OS and database versions, along with the database characterset. How is this data being loaded ? What tool are you using to look at the data values in the file and the database ?

    HTH
    Srini
  • 3. Re: Character set conversion in oracle
    845109 Newbie
    Currently Being Moderated
    Thanks I will keep this in handy while posting to this forum .

    Data is coming as below

    music,Magic Sound Records ® - México,
    game,"Pokémon USA, Inc.",
    game,"The Pokémon Company International, Inc.",
    uncategorized,Prisma Découverte,
    uncategorized,Publicaciones Técnicas

    ie é should convert to é
  • 4. Re: Character set conversion in oracle
    845109 Newbie
    Currently Being Moderated
    Hi Srini,
    I am using database version as below

    Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining Scoring Engine and Real Application Testing options

    I have a unix box which is mounted to this oracle database . I am using Unix command to view contents of the file .
    Oracle database character set is UTF8.
  • 5. Re: Character set conversion in oracle
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    As requested, pl identify exact OS version, and the exact commands/tools used to view the data on the filesystem and in the database. What is the Unix locale setting ?

    http://en.wikipedia.org/wiki/Locale

    Pl identify if the characterset is UTF8 or AL32UTF8 - http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch6unicode.htm#g1008165

    Verify by querying NLS_DATABASE_PARAMETERS.

    How is the data being loaded into the database ? Pl provide exact details

    HTH
    Srini
  • 6. Re: Character set conversion in oracle
    845109 Newbie
    Currently Being Moderated
    Thanks Srini . It was characterset issue .After setting it to UTF8 solved my problem
  • 7. Re: Character set conversion in oracle
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl elaborate on how you determined what the issue was and the steps you took to correct the situation, for the benefit of future readers of this thread.

    Pl also refrain from posting duplicates as this is considered to be rude - Character set conversion in oracle

    Thanks
    Srini
  • 8. Re: Character set conversion in oracle
    845109 Newbie
    Currently Being Moderated
    Hi Srini,
    Sorry my bad . I forgot about my last post :(

    Please find my below investigation :

    As I told by you I checked Unix locale settings as well as in oracle NLS_DATABASE_PARAMETERS.
    Unix locale was set to UTF8 but as you have mentioned NLS_DATABASE_PARAMETERS was set to AL32UTF8 which is why the characters where were not converted.
    So after changing it to UTF8 and column as Nvarchar solved my issue .

    Thanks you all for figuring this out .
  • 9. Re: Character set conversion in oracle
    orafad Oracle ACE
    Currently Being Moderated
    842106 wrote:

    As I told by you I checked Unix locale settings as well as in oracle NLS_DATABASE_PARAMETERS.
    Since you mentioned loading a file, how file content is encoded matters. And depending on how file is loaded, the char set part of NLS_LANG should be set accordingly.
    Unix locale was set to UTF8 but as you have mentioned NLS_DATABASE_PARAMETERS was set to AL32UTF8 which is why the characters where were not converted.
    So after changing it to UTF8 and column as Nvarchar solved my issue .
    How exactly did you change what?

    Could you post a dump() of an example column value?
  • 10. Re: Character set conversion in oracle
    Sergiusz Wolicki (Oracle) Expert
    Currently Being Moderated
    If you have changed the database character set from AL32UTF8 to UTF8, because of this problem, then this was a very poor decision and it certainly had no influence on the disappearance of the issue.


    -- Sergiusz

Legend

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