14 Replies Latest reply on Sep 18, 2017 8:49 AM by B.Delmée

    SQL Developer cannot show accents on table data

    pawuyulan

      When I insert or select data from a sqlplus command window, it shows accented characters correctly (áéíóúüºª)

       

      But the table data tab on SQL Developer substitutes accents with other characters.

       

      I checked that the default font "DialogInput" supports accents. What else I need to do?

       

      Untitled.png

       

      The OS is Windows 7 in English. I don't know if that is an issue. Any other software works correctly with accents.

        • 1. Re: SQL Developer cannot show accents on table data
          Gaz in Oz

          What specific version of sql developer are you using?

          What locale is your OS using?

          What is your OS and version?

          How do you start SQL Developer?

          Under "Tools" -> "Preferences" -> "Environment"... what's your "Encoding" set to?

          What is your database NLS settings?

          What is your session NLS settings?

          Show example of a "working env.

          Show  example of a non-working env.

          • 2. Re: SQL Developer cannot show accents on table data
            thatJeffSmith-Oracle

            Works here, Win7. American English Locale. SQLDev 17.2. Default Font. Works good for Droid Sans Mono too.

             

            accents.png

            • 3. Re: SQL Developer cannot show accents on table data
              pawuyulan

              Gaz in Oz wrote:

               

              What specific version of sql developer are you using?

              What locale is your OS using?

              What is your OS and version?

              How do you start SQL Developer?

              Under "Tools" -> "Preferences" -> "Environment"... what's your "Encoding" set to?

              What is your database NLS settings?

              What is your session NLS settings?

              Show example of a "working env.

              Show example of a non-working env.

              SQL Developer Version: 4.2.0.17.089

              Build: 17.089.1709

               

              The OS is Windows 7 64bit, in English, but locale is Spanish (Ar)

               

              I start SQL Developer by running sqldeveloper.exe

               

              "Tools" -> "Preferences" -> "Environment" -> "Encoding" is cp1252 (default value)

               

              I'm not sure how to get the database NLS settings. I googled it and got this command:

              SQL> SELECT name,value$ FROM sys.props$;

               

               

              NAME

              ------------------------------

              VALUE$

              --------------------------------------------------------------------------------

              DICT.BASE

              2

               

               

              DEFAULT_TEMP_TABLESPACE

              TEMP

               

               

              DEFAULT_PERMANENT_TABLESPACE

              SYSTEM

               

               

               

               

              NAME

              ------------------------------

              VALUE$

              --------------------------------------------------------------------------------

              DEFAULT_EDITION

              ORA$BASE

               

               

              Flashback Timestamp TimeZone

              GMT

               

               

              TDE_MASTER_KEY_ID

               

               

               

               

               

               

              NAME

              ------------------------------

              VALUE$

              --------------------------------------------------------------------------------

              WORKLOAD_REPLAY_MODE

               

               

               

               

              DST_UPGRADE_STATE

              NONE

               

               

              DST_PRIMARY_TT_VERSION

              14

               

               

               

               

              NAME

              ------------------------------

              VALUE$

              --------------------------------------------------------------------------------

              DST_SECONDARY_TT_VERSION

              0

               

               

              DEFAULT_TBS_TYPE

              SMALLFILE

               

               

              NLS_LANGUAGE

              AMERICAN

               

               

               

               

              NAME

              ------------------------------

              VALUE$

              --------------------------------------------------------------------------------

              NLS_TERRITORY

              AMERICA

               

               

              NLS_CURRENCY

              $

               

               

              NLS_ISO_CURRENCY

              AMERICA

               

               

               

               

              NAME

              ------------------------------

              VALUE$

              --------------------------------------------------------------------------------

              NLS_NUMERIC_CHARACTERS

              .,

               

               

              NLS_CHARACTERSET

              AL32UTF8

               

               

              NLS_CALENDAR

              GREGORIAN

               

               

               

               

              NAME

              ------------------------------

              VALUE$

              --------------------------------------------------------------------------------

              NLS_DATE_FORMAT

              DD-MON-RR

               

               

              NLS_DATE_LANGUAGE

              AMERICAN

               

               

              NLS_SORT

              BINARY

               

               

               

               

              NAME

              ------------------------------

              VALUE$

              --------------------------------------------------------------------------------

              NLS_TIME_FORMAT

              HH.MI.SSXFF AM

               

               

              NLS_TIMESTAMP_FORMAT

              DD-MON-RR HH.MI.SSXFF AM

               

               

              NLS_TIME_TZ_FORMAT

              HH.MI.SSXFF AM TZR

               

               

               

               

              NAME

              ------------------------------

              VALUE$

              --------------------------------------------------------------------------------

              NLS_TIMESTAMP_TZ_FORMAT

              DD-MON-RR HH.MI.SSXFF AM TZR

               

               

              NLS_DUAL_CURRENCY

              $

               

               

              NLS_COMP

              BINARY

               

               

               

               

              NAME

              ------------------------------

              VALUE$

              --------------------------------------------------------------------------------

              NLS_LENGTH_SEMANTICS

              BYTE

               

               

              NLS_NCHAR_CONV_EXCP

              FALSE

               

               

              NLS_NCHAR_CHARACTERSET

              AL16UTF16

               

               

               

               

              NAME

              ------------------------------

              VALUE$

              --------------------------------------------------------------------------------

              NLS_RDBMS_VERSION

              11.2.0.2.0

               

               

              GLOBAL_DB_NAME

              XE

               

               

              DBTIMEZONE

              00:00

               

               

               

               

              NAME

              ------------------------------

              VALUE$

              --------------------------------------------------------------------------------

              EXPORT_VIEWS_VERSION

              8

               

               

              WORKLOAD_CAPTURE_MODE

               

               

               

               

              NO_USERID_VERIFIER_SALT

              6333C638B9B4F01D91C899E81BFE748B

               

               

               

               

              36 rows selected.

              If I enter accents inside SQL Developper, it works correctly:

              Untitled.png

               

              But if I enter from command line (SQLPLUS):

               

              SQL> INSERT INTO "DEV"."DUMMY" (COLUMN1) VALUES ('áñº')

                2  ;

               

               

              1 row created.

               

               

              SQL> commit;

              Then SQL Developper stops working correctly:

              Untitled.png

              If I check it from command line, I get the right encoding from values entered from command line, but the wrong values from values entered from SQL Developper

               

               

              SQL> select column1 from dummy;

               

              COLUMN1

              --------------------

              áñº

              ß±║

               

              And APEX 5 works the same way as SQL Developer

              Untitled.png

              • 4. Re: SQL Developer cannot show accents on table data
                pawuyulan

                NLS_LANG in  HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_XE  is  AMERICAN_AMERICA.WE8MSWIN1252

                • 5. Re: SQL Developer cannot show accents on table data
                  pawuyulan

                  thatJeffSmith-Oracle wrote:

                   

                  Works here, Win7. American English Locale. SQLDev 17.2. Default Font. Works good for Droid Sans Mono too.

                  It works if I enter and read the data inside SQL Developer.

                   

                  It breaks when I enter data in SQL Developer and read it from command line, or vice versa (when I enter from command line and read in SQL Developer)

                  • 6. Re: SQL Developer cannot show accents on table data
                    pawuyulan

                    The database version is

                     

                    SQL> SELECT * FROM V$VERSION;

                     

                     

                    BANNER

                    --------------------------------------------------------------------------------

                    Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

                    PL/SQL Release 11.2.0.2.0 - Production

                    CORE    11.2.0.2.0      Production

                    TNS for 64-bit Windows: Version 11.2.0.2.0 - Production

                    NLSRTL Version 11.2.0.2.0 - Production

                    • 7. Re: SQL Developer cannot show accents on table data
                      thatJeffSmith-Oracle

                      FULL STOP

                       

                      SQL> INSERT INTO "DEV"."DUMMY" (COLUMN1) VALUES ('áñº')

                        2  ;

                       

                       

                      1 row created.

                       

                       

                      SQL> commit;

                       

                      THIS is your problem. SQL Developer and APEX both are showing the data CORRECTLY as its stored in the database. The data is being corrupted when you insert it with SQL*Plus - probably due to a NLS issue on your client.

                      1 person found this helpful
                      • 8. Re: SQL Developer cannot show accents on table data
                        B.Delmée

                        If you want to use the windows console version of sql*plus with UTF-8, you need the right combination of

                        • code page (perhaps "chcp 65001" according to a stack exchange reply)
                        • unicode-capable font (Lucida Console is suggested from the same source)
                        • Oracle client-side NLS settings

                        Sorry I cannot be more specific (I believe the key is consistency between the active code page and your NLS_LANGUAGE, it can be UTF-8 or ANSI and the oracle client will translate to whatever your database server uses to store the data, but CP & NLS have to be compatible)

                        1 person found this helpful
                        • 9. Re: SQL Developer cannot show accents on table data
                          pawuyulan

                          thatJeffSmith-Oracle wrote:

                          THIS is your problem. SQL Developer and APEX both are showing the data CORRECTLY as its stored in the database. The data is being corrupted when you insert it with SQL*Plus - probably due to a NLS issue on your client.

                          Do commonly the command line client have a different NLS settings than the SQL database?

                           

                          I do not understand if I should fix the NLS settings of:

                           

                          The OS

                          The SQLPlus client

                          The Oracle database

                           

                          Also I do not understand if both APEX and SQL Developper have independent NLS settings that the Oracle Database, or if they grab his settings from the database and some way SQLPlus does not do it.

                          • 10. Re: SQL Developer cannot show accents on table data
                            pawuyulan

                            pawuyulan wrote:

                             

                            The OS is Windows 7 64bit, in English, but locale is Spanish (Ar)

                            I'm kind of retard. SQL is running inside a virtual machine, with Windows 10 64 bit (not Windows 7). All the other data is correct

                            • 11. Re: SQL Developer cannot show accents on table data
                              thatJeffSmith-Oracle

                              >>Do commonly the command line client have a different NLS settings than the SQL database

                              NLS on the client describes the client environment. So yes, it's quite common for them to be different. And it's a big mistake to assume they should 'match.'

                               

                              https://asktom.oracle.com/pls/asktom/f?p=100:11:0%3A%3A%3A%3AP11_QUESTION_ID:1224836384599

                              1 person found this helpful
                              • 12. Re: SQL Developer cannot show accents on table data
                                B.Delmée

                                pawuyulan wrote:

                                 

                                NLS_LANG in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_XE is AMERICAN_AMERICA.WE8MSWIN1252

                                Try running "CHCP 1252" before invoking SQL*PLUS

                                This way your NLS_LANG and CODEPAGE both agree on using ANSI

                                 

                                Alternatively you could

                                • open a CLI ("cmd.exe")
                                • force your own value for NLS_LANG instead of inheriting it from the registry: "set NLS_LANG=AMERICAN_AMERICA.AL32UTF8"
                                • accordingly use the UTF-8 codepage: "chcp 65001"
                                • invoke SLQ*PLUS

                                This (2d) way, your client uses the same UTF-8 character set as your server and no translation is necessary.

                                As explained previously, on windows your CP and NLS have to agree; unix makes it perhaps clearer as both LANG and NLS_LANG are environment variables and no registry is involved. And of course you need to be using a font which has the glyphs you want.

                                • 13. Re: SQL Developer cannot show accents on table data
                                  pawuyulan

                                  B.Delmée wrote:

                                   

                                  Try running "CHCP 1252" before invoking SQL*PLUS

                                  This way your NLS_LANG and CODEPAGE both agree on using ANSI

                                  It perfectly works.

                                   

                                  Thank you.

                                  • 14. Re: SQL Developer cannot show accents on table data
                                    B.Delmée

                                    Thanks for reporting back. Note that Unicode is much richer than ANSI, so if you need characters besides the ones you used in your example, you have the option of using UTF-8 on the client-side as explained above (and your database server is already configured to accomodate those by using AL32UTF8)