4 Replies Latest reply on Apr 2, 2013 8:30 PM by jymarkusg

    utf8 and substr question


      We're using oracle linux 5 as os and oracle 11.2 ee and AL32UTF8 characterset in our db. The os locale is set to en_US.UTF-8 and NLS_LANG is set to AMERIAN_AMERICA.AL32UTF8.

      The users of different countries (western and eastern europe) work with a forms application and modify data (with language specific characters) in the UTF8 database without any problems.

      On the database server we generate some flat files (over all countries) for different interfaces. When I open the flat file in a shell with putty (setting character set to UTF8) every language specific character is correct.

      Our partners load the flat file via sql loader in a flat table and split it up with their own procedures. Our hungarian partner has troubles with special characters. For testing I've set the NLS to HUNGARIAN_HUNGARY.EE8MSWIN1250 before creating the flat file. Now he could process the file.

      So what is wrong in his environment? He is also using AL32UTF in his database. Should he use substrc instead of a normal substr function in his procedures? He also tried to set NLS to AMERIAN_AMERICA.AL32UTF8 before processing the flat file without positive result...

        • 1. Re: utf8 and substr question
          Srini Chavali-Oracle
          Pl elaborate what "troubles" your partners are having. Looks like your setup is correct and works as expected. Can you get a copy of the complete SQL Loader commands, control file contents and the errors that your partners are encountering ?

          • 2. Re: utf8 and substr question
            What is the data type of your columns and variables? VARCHAR2 or NVARCHAR2? CHAR or NCHAR? CLOB or NCLOB?
            • 3. Re: utf8 and substr question
              Sergiusz Wolicki-Oracle
              ## Our hungarian partner has troubles with special characters.

              We cannot help with so little information. If setting NLS_LANG to .EE8MSWIN1250 helped, then the partner's client environment may be set to .EE8MSWIN1250 as well and files encoded in AL32UTF8 do not load correctly. The partner's test to set NLS_LANG could have been flawed (e.g. a misspelled variable name) and this is why it did not helped. But I can only speculate.

              -- Sergiusz
              • 4. Re: utf8 and substr question

                I'm sorry for my delay but I was out of office the last days...

                I'll try to get a copy of his loader scripts. The problem is that special characters (e.g. ő) are shown as unkown characters (wrong ?). For me it seems he only processes the first byte of the character...

                We're only using VARCHAR2 datatype.

                I'll also try to get more detailed information from his environment.