NLS_CHARACTERSET and SQL Developer

user652526

    I have an issue with counting the length of a string with multibyte characters in it.

     

    System setup:
    Oracle 19c version 19.3.0.0.0 - no patching
    SQL Developer Version 19.1.0.094
    NLS PARAMTERS

    "Parameter"             "Value"               
    "NLS_CALENDAR"          "GREGORIAN"           
    "NLS_CHARACTERSET"      "AL32UTF8"            
    "NLS_COMP"              "BINARY"              
    "NLS_CURRENCY"          "$"                   
    "NLS_DATE_FORMAT"       "DD-MON-RRRR HH24:MI:SS"
    "NLS_DATE_LANGUAGE"     "AMERICAN"            
    "NLS_DUAL_CURRENCY"     "$"                   
    "NLS_ISO_CURRENCY"      "AMERICA"             
    "NLS_LANGUAGE"          "AMERICAN"            
    "NLS_LENGTH_SEMANTICS"  "BYTE"                
    "NLS_NCHAR_CHARACTERSET""AL16UTF16"           
    "NLS_NCHAR_CONV_EXCP"   "FALSE"               
    "NLS_NUMERIC_CHARACTERS"".,"                  
    "NLS_SORT"              "BINARY"              
    "NLS_TERRITORY"         "AMERICA"             
    "NLS_TIMESTAMP_FORMAT"  "DD-MON-RR HH.MI.SSXFF AM" 
    "NLS_TIMESTAMP_TZ_FORMAT""DD-MON-RR HH.MI.SSXFF AM TZR"
    "NLS_TIME_FORMAT"       "HH.MI.SSXFF AM"      
    "NLS_TIME_TZ_FORMAT"    "HH.MI.SSXFF AM TZR"  


    When I execute the following statement in SQL Developer:

     

    select length('ä2ää13ääXX') from dual;
    

     

    It returns 10.

     

    When I execute it from SQL PLUS command line it returns 15.

    Could someone explain why the difference in the value returned for the count?

     

    Thanks in advance!

     

    John

      • 1. Re: NLS_CHARACTERSET and SQL Developer
        EdStevens

        user652526 wrote:

         

        I have an issue with counting the length of a string with multibyte characters in it.

         

        System setup:
        Oracle 19c version 19.3.0.0.0 - no patching
        SQL Developer Version 19.1.0.094
        NLS PARAMTERS

        "Parameter" "Value"
        "NLS_CALENDAR" "GREGORIAN"
        "NLS_CHARACTERSET" "AL32UTF8"
        "NLS_COMP" "BINARY"
        "NLS_CURRENCY" "$"
        "NLS_DATE_FORMAT" "DD-MON-RRRR HH24:MI:SS"
        "NLS_DATE_LANGUAGE" "AMERICAN"
        "NLS_DUAL_CURRENCY" "$"
        "NLS_ISO_CURRENCY" "AMERICA"
        "NLS_LANGUAGE" "AMERICAN"
        "NLS_LENGTH_SEMANTICS" "BYTE"
        "NLS_NCHAR_CHARACTERSET""AL16UTF16"
        "NLS_NCHAR_CONV_EXCP" "FALSE"
        "NLS_NUMERIC_CHARACTERS"".,"
        "NLS_SORT" "BINARY"
        "NLS_TERRITORY" "AMERICA"
        "NLS_TIMESTAMP_FORMAT" "DD-MON-RR HH.MI.SSXFF AM"
        "NLS_TIMESTAMP_TZ_FORMAT""DD-MON-RR HH.MI.SSXFF AM TZR"
        "NLS_TIME_FORMAT" "HH.MI.SSXFF AM"
        "NLS_TIME_TZ_FORMAT" "HH.MI.SSXFF AM TZR"


        When I execute the following statement in SQL Developer:

         

        1. selectlength('ä2ää13ääXX')fromdual;

         

        It returns 10.

         

        When I execute it from SQL PLUS command line it returns 15.

        Could someone explain why the difference in the value returned for the count?

         

        Thanks in advance!

         

        John

        Easy.

        The NLS parameters can be set at multiple levels but are ultimately controlled by the client.  Part of the design of SQL Dev is that it sets its own defaults.  On the other hand, sqlplus, absent any intervention by the user, simply inherits the values from the database's settings.

        1 位用户发现它有用
        • 2. Re: NLS_CHARACTERSET and SQL Developer
          cormaco

          From what operating system did you run sqlplus?

          I was able to get the same result in Centos 6:

          [cormaco@vbox1 ~]$ sqlplus hr/hr@pdb1
          
          SQL*Plus: Release 18.0.0.0.0 - Production on Wed Sep 2 16:11:37 2020
          Version 18.4.0.0.0
          
          Copyright (c) 1982, 2018, Oracle.  All rights reserved.
          
          Letzte erfolgreiche Anmeldezeit: Mi Sep 02 2020 16:06:40 +02:00
          
          Verbunden mit: 
          Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
          Version 18.4.0.0.0
          
          SQL> select length('ä2ää13ääXX') from dual; 
          
          LENGTH('??2????13????XX')
          -------------------------
                         15
          
          
          

           

          It can be fixed by setting NLS_LANG:

          [cormaco@vbox1 ~]$ export NLS_LANG=GERMAN_GERMANY.UTF8
          [cormaco@vbox1 ~]$ sqlplus hr/hr@pdb1
          
          SQL*Plus: Release 18.0.0.0.0 - Production on Mi Sep 2 16:12:39 2020
          Version 18.4.0.0.0
          
          Copyright (c) 1982, 2018, Oracle.  All rights reserved.
          
          Letzte erfolgreiche Anmeldezeit: Mi Sep 02 2020 16:11:37 +02:00
          
          Verbunden mit: 
          Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
          Version 18.4.0.0.0
          
          SQL> select length('ä2ää13ääXX') from dual; 
          
          LENGTH('Ä2ÄÄ13ÄÄXX')
          --------------------
                    10
          
          
          

           

          Important is setting UTF8 as the client characterset, you can choose any language and territory.

          1 位用户发现它有用
          • 3. Re: NLS_CHARACTERSET and SQL Developer
            thatJeffSmith-Oracle

            "Part of the design of SQL Dev is that it sets its own defaults"
            This is really something that falls out of being a java app using the oracle JDBC driver..where those properties are set automatically depending on the OS region/locale environments.

            1 位用户发现它有用
            • 4. Re: NLS_CHARACTERSET and SQL Developer
              user652526

              TO cormaco I'm running Oracle Linux

              • 5. Re: NLS_CHARACTERSET and SQL Developer
                user652526

                Thanks for the reply.  This really helps!

                 

                John