6 Replies Latest reply on Sep 3, 2015 10:51 PM by Gary Graham-Oracle

    SQLFORMAT ANSICONSOLE

    user5457989

      SQL> set sqlformat ansiconsole

      SQL> select 1200 from dual

       

      1200  

      1�200

       

      Why this character adds between number?

      How to solve this problem?

       

      Thanks

        • 1. Re: SQLFORMAT ANSICONSOLE
          Gary Graham-Oracle

          It should be a representation of the NLS group separator character (groups of thousands) for numbers for your locale.  It displays a comma for AMERICAN. Perhaps there is a bug for your specific locale?  [Tested on the current beta sqlcl-4.2.0.15.177.0246-no-jre]

          • 2. Re: SQLFORMAT ANSICONSOLE
            stueckl

            But it seems like SQLcl does ignore NLS_NUMERIC_CHARACTERS for sqlformat ansiconsole (see BUG - SQLcl-4.2.0.15.177.0246 ansiconsole ignores NLS_NUMERIC_CHARACTERS).

             

            PS: you know ... displaying AMERICAN decimal separators/currency symbols/... is not really a proof of anything working. Just as when I as Austrian would say: "The webapp is displaying German labels - thus it is international" ;-)

            • 3. Re: SQLFORMAT ANSICONSOLE
              user5457989

              I am nls FRENCh.

              DB_TIMEZONE +02:00

              NLS_CALENDAR GREGORIAN

              NLS_CHARACTERSET WE8MSWIN1252

              NLS_COMP BINARY

              NLS_CURRENCY €

              NLS_DATE_FORMAT DD/MM/RRRR_HH24:MI:SS

              NLS_DATE_LANGUAGE FRENCH

              NLS_DUAL_CURRENCY €

              NLS_ISO_CURRENCY FRANCE

              NLS_LANGUAGE FRENCH

              NLS_LENGTH_SEMANTICS BYTE

              NLS_NCHAR_CONV_EXCP FALSE

              NLS_NUMERIC_CHARACTERS ,

              NLS_SORT FRENCH

              NLS_TERRITORY FRANCE

              NLS_TIME_FORMAT HH24:MI:SSXFF

              NLS_TIMESTAMP_FORMAT DD/MM/RRRR_HH24:MI:SSXFF

              NLS_TIMESTAMP_TZ_FORMAT DD/MM/RRRR_HH24:MI:SSXFF TZR

              NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR

              SESSION_TIMEZONE Europe/Paris

              SESSION_TIMEZONE_OFFSET +02:00

               

              I added the comma for NLS group separator character.

               

              It changes nothing

              • 4. Re: SQLFORMAT ANSICONSOLE
                Gary Graham-Oracle

                NLS_NUMERIC_CHARACTERS ,

                Is that what show nls or select * from v$nls_parameters returns for the NLS_NUMERIC_CHARACTERS parameter?

                 

                When I try to alter session set nls_numeric_characters = ','  (that is, enter anything other than 2 valid characters) in either SQL*Plus or SQLcl, I get

                ORA-12705: Cannot access NLS data files or invalid environment specified

                So I am curious about what value for the group separator portion of NLS_NUMERIC_CHARACTERS the database thinks it has...

                 

                Edit:  Also, don't forget poster stueckl got Jeff to log a bug based on comments in the discussion referenced above.

                • 5. Re: SQLFORMAT ANSICONSOLE
                  user5457989

                  SQL> set sqlformat ansiconsole

                  SQL> select * from v$nls_parameters

                   

                  PARAMETER                VALUE                       

                  NLS_LANGUAGE             FRENCH                      

                  NLS_TERRITORY            FRANCE                      

                  NLS_CURRENCY             �                           

                  NLS_ISO_CURRENCY         FRANCE                      

                  NLS_NUMERIC_CHARACTERS   ,                           

                  NLS_CALENDAR             GREGORIAN                   

                  NLS_DATE_FORMAT          DD/MM/RRRR_HH24:MI:SS       

                  NLS_DATE_LANGUAGE        FRENCH                      

                  NLS_CHARACTERSET         WE8MSWIN1252                

                  NLS_SORT                 FRENCH                      

                  NLS_TIME_FORMAT          HH24:MI:SSXFF               

                  NLS_TIMESTAMP_FORMAT     DD/MM/RRRR_HH24:MI:SSXFF    

                  NLS_TIME_TZ_FORMAT       HH24:MI:SSXFF TZR           

                  NLS_TIMESTAMP_TZ_FORMAT  DD/MM/RRRR_HH24:MI:SSXFF TZR

                  NLS_DUAL_CURRENCY        �                           

                  NLS_NCHAR_CHARACTERSET   AL16UTF16                   

                  NLS_COMP                 BINARY                      

                  NLS_LENGTH_SEMANTICS     BYTE                        

                  NLS_NCHAR_CONV_EXCP      FALSE                       

                   

                   

                  19 lignes sélectionnées

                   

                  SQL> set sqlformat fixed

                  SQL> select * from v$nls_parameters

                   

                  "PARAMETER"                   "VALUE"                     

                  "NLS_LANGUAGE"                "FRENCH"                    

                  "NLS_TERRITORY"               "FRANCE"                    

                  "NLS_CURRENCY"                "€"                         

                  "NLS_ISO_CURRENCY"            "FRANCE"                    

                  "NLS_NUMERIC_CHARACTERS"      ", "                        

                  "NLS_CALENDAR"                "GREGORIAN"                 

                  "NLS_DATE_FORMAT"             "DD/MM/RRRR_HH24:MI:SS"     

                  "NLS_DATE_LANGUAGE"           "FRENCH"                    

                  "NLS_CHARACTERSET"            "WE8MSWIN1252"              

                  "NLS_SORT"                    "FRENCH"                    

                  "NLS_TIME_FORMAT"             "HH24:MI:SSXFF"             

                  "NLS_TIMESTAMP_FORMAT"        "DD/MM/RRRR_HH24:MI:SSXFF"  

                  "NLS_TIME_TZ_FORMAT"          "HH24:MI:SSXFF TZR"         

                  "NLS_TIMESTAMP_TZ_FORMAT"     "DD/MM/RRRR_HH24:MI:SSXFF TZR"

                  "NLS_DUAL_CURRENCY"           "€"                         

                  "NLS_NCHAR_CHARACTERSET"      "AL16UTF16"                 

                  "NLS_COMP"                    "BINARY"                    

                  "NLS_LENGTH_SEMANTICS"        "BYTE"                      

                  "NLS_NCHAR_CONV_EXCP"         "FALSE"                     

                   

                  19 lignes sélectionnées

                   

                  The character are coma and blank space.

                   

                  The character € is not displayed correctly.


                  I modified NLS_NUMERIC_CHARACTERS ",." and the problem is the same.

                  • 6. Re: SQLFORMAT ANSICONSOLE
                    Gary Graham-Oracle

                    Thanks for the update and the test case.  I will log another bug to go along with the one Jeff logged earlier.