4 Replies Latest reply: Sep 26, 2012 11:50 AM by Sergiusz Wolicki-Oracle RSS

    Effect of NLS_LANG in SQL scripts

    Max
      DB version: 11.2.0.2
      Platform : RHEL 5.6


      We have a software vendor who occasionaly sends us SQL scripts to deploy.

      The scripts will usually contain few DDLs and lots DMLs

      From textpad, i can the see the DMLs (INSERTs , UPDATEs) in SQL Script. It usually contain non-english characters (Dutch, German, French) like
      'Übersicht Buchungssätze' .
      'Fehler beim Löschen der zugeordneten Referenzen!'
      They always ask us to set NLS_LANG like following.
      export NLS_LANG="AMERICAN_AMERICA.WE8ISO8859P15"
      After the INSERT, I queried the inserted values from PL/SQL developer. The values appear as it appears in the SQL Script text file as shown above.
      So, what was the effect of setting NLS_LANG ? What could have happened if I hadn't set NLS_LANG ?


      NLS info within out DB
      SQL > select * from nls_Database_parameters;
      
      PARAMETER                      VALUE
      ------------------------------ ----------------------------------------
      NLS_LANGUAGE                   ENGLISH
      NLS_TERRITORY                  UNITED KINGDOM
      NLS_CURRENCY                   #
      NLS_ISO_CURRENCY               UNITED KINGDOM
      NLS_NUMERIC_CHARACTERS         .,
      NLS_CHARACTERSET               AL32UTF8
      NLS_CALENDAR                   GREGORIAN
      NLS_DATE_FORMAT                DD-MON-RR
      NLS_DATE_LANGUAGE              ENGLISH
      NLS_SORT                       BINARY
      NLS_TIME_FORMAT                HH24.MI.SSXFF
      NLS_TIMESTAMP_FORMAT           DD-MON-RR HH24.MI.SSXFF
      NLS_TIME_TZ_FORMAT             HH24.MI.SSXFF TZR
      NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH24.MI.SSXFF TZR
      NLS_NCHAR_CHARACTERSET         AL16UTF16
      NLS_DUAL_CURRENCY              ?
      NLS_COMP                       BINARY
      NLS_LENGTH_SEMANTICS           BYTE
      NLS_NCHAR_CONV_EXCP            FALSE
      NLS_RDBMS_VERSION              11.2.0.2.0
      
      20 rows selected.
        • 1. Re: Effect of NLS_LANG in SQL scripts
          Zoltan Kecskemethy
          Check your input script encoding first. If it is not UTF8 you may have issues.
          It is important to setup the NLS_LANG when you want to use other encoding in your input than your DB character set.

          Your database character set is AL32UTF8, your default linux locale is UTF8 so when you execute an sql command oracle does not translate the characters just simple executes it.
          If you setup NLS_LANG oracle automatically translates characters from your input to the database character set accordingly. That's the difference.

          So if you did not set the NLS_LANG all characters went into the database as it is because there were no conversion as oracle expected an UTF8 input because that is the default.

          Try to get an UTF8 export of your data if that looks good you are good if not you have to fix. I assume you could have issues with specific characters.
          Please check your data using Oracle SQL Developer as well.

          If you have MOS account see Oracle Support Document 179133.1 [url https://support.oracle.com/epmos/faces/ui/km/DocumentDisplay.jspx?id=179133.1]The correct NLS_LANG in a Windows Environment and / or
          [url https://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=264157.1]The correct NLS_LANG setting in Unix Environments Doc ID: 264157.1
          for detailed info.
          • 2. Re: Effect of NLS_LANG in SQL scripts
            Sergiusz Wolicki-Oracle
            Vendor's requirement is well justified. SQL*Plus must know the encoding of the script to pass this information to the Oracle Client libraries. This information is used to control the client/server character set conversion. This way, the character encoding of string literals in the script can be changed to the database character set. The database character set will be used by the SQL engine to parse and process the literals. If you did not set NLS_LANG in a RHEL session, it would default to AMERICAN_AMERICA.US7ASCII. The Western European characters in the DML literals would be converted from US7ASCII, which does not know characters with umlauts or other accents, and thus corrupted.


            -- Sergiusz
            • 3. Re: Effect of NLS_LANG in SQL scripts
              Max
              Thank you Zoltan, Sergiuz for this valuable info.

              Check your input script encoding first. If it is not UTF8 you may have issues.


              I don't know how to check the character encoding of a text file straight from Windows or Unix. So, in my Windows XP laptop, I opened this SQL script using textpad.
              Under 'View' > 'Document Properties' , the 'Code Set' is shown as ANSI.

              In Windows XP/7 , when you click 'Save', both Notepad and Textpad have a drop down menu called 'Encoding' . One could save the file with 'UTF-8' using this.
              If our vendor had chosen to save the script with UTF-8 encoding, then , we didn't have to set NLS_LANG. Right ?
              • 4. Re: Effect of NLS_LANG in SQL scripts
                Sergiusz Wolicki-Oracle
                If our vendor had chosen to save the script with UTF-8 encoding, then , we didn't have to set NLS_LANG. Right ?

                Wrong. You would need to set it to AMERICAN_AMERICA.AL32UTF8. If you do not set NLS_LANG, it defaults to AMERICAN_AMERICA.US7ASCII. Therefore, only pure 7-bit ASCII scripts can be run in such environment. The Oracle Client does not use the database character set as the default. It uses the platform default (usually US7ASCII) or UTF-16 in case of JDBC, ODBC (Unicode mode), and ODP .NET drivers.


                -- Sergiusz