10 Replies Latest reply: Nov 13, 2012 2:44 PM by Sergiusz Wolicki-Oracle RSS

    Character set conversion in oracle

    845109
      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
          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
            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
              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
                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
                  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
                    Thanks Srini . It was characterset issue .After setting it to UTF8 solved my problem
                    • 7. Re: Character set conversion in oracle
                      Srini Chavali-Oracle
                      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
                        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
                          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
                            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