Non-English Character Display in Oracle SQL Developer

thatjeffsmith SQL Developer 37 Comments

Tell Others About This Story:

I get a variation on this question at least once a week, if not more frequently.

I’m from Israel, and the language on the databases is Hebrew. When I use the old and deprecated SQL*Plus (windows rich client) I can see the hebrew clearly, when I use the latest SQL Developer, I get gibberish.

This question appears on the forums about every week or so as well. So what’s the deal?

Well, it starts with a basic misunderstanding of NLS Client parameters. These should accurately reflect the language and locality setup on your LOCAL machine. DO NOT COPY what’s set in the database. The these parameters work together with the database so that information can be transferred back and forth correctly.

Having the wrong NLS parameters locally can be bad.

[ORACLE DOCS]Setting the NLS_LANG parameter properly is essential to proper data conversion. The character set that is specified by the NLS_LANG parameter should reflect the setting for the client operating system. Setting NLS_LANG correctly enables proper conversion from the client operating system character encoding to the database character set. When these settings are the same, Oracle Database assumes that the data being sent or received is encoded in the same character set as the database character set, so character set validation or conversion may not be performed. This can lead to corrupt data if conversions are necessary.

OK, so what are you supposed to do?

Set the Font!

9 times out of 10, this preference fixes the problem with display issues.

9 times out of 10, this preference fixes the problem with display issues.

Make sure you set a Font that supports the characters you’re trying to display. It’s as simple as that. This preference defines the font used to display characters in the editors and the data grids. If you have it set to a font that doesn’t have Hebrew character support – you’re not going to see Hebrew in SQL Developer.

A few years ago…wow, like 15 years ago, I learned that the Tahama Font is pretty Unicode-friendly.

Bad Font Selection

A Font that's not non-English friendly

A Font that’s not non-English friendly

Good Font Selection

Exact same text, except rendered with the Tahoma font

Exact same text, except rendered with the Tahoma font

Summary

Having problems seeing non-English text in SQL Developer? Check the font! And do not start messing with NLS parameters without talking to your DBA first.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 37

  1. Sorry for pushing this old post …

    While it may be true, that “query result” does make use of UTF-8 and even “script result” can display Arabic characters.

    But just try “ansiconsole” as a script output option … and you’re doomed.
    Not a single diacritc (no ä, ö, ü no ñ, no …). 🙁

    Searched a bit around … and nowhere found something like “be warned … AnsiConsole removes your diacritics”.

    Added a query for test purposes … hope all the diacritics “survive” the submit …

    select /*ansiconsole*/ ‘Arabische Republik Syrien’ someDia, ‘الجمهورية العربية السورية (al-Dschumhūriyya al-ʿarabiyya as-sūriyya, Arabische Republik Syrien)’ manyDia from dual
    union all select ‘Königreich Spanien’ someDia, ‘Reino de España’ manyDia from dual
    union all select ‘Republik Albanien’ someDia, ‘Republika e Shqipërisë (Republik Albanien)’ manyDia from dual
    union all select ‘Republik Österreich’ someDia, ‘Republik Österreich’ manyDia from dual;

    1. These are sample outputs from my script output window … csv and ansiconsole …

      “someDia”,”manyDia”
      “Arabische Republik Syrien”,”الجمهورية العربية السورية (al-Dschumhūriyya al-ʿarabiyya as-sūriyya, Arabische Republik Syrien)”
      “Königreich Spanien”,”Reino de España”
      “Republik Albanien”,”Republika e Shqipërisë (Republik Albanien)”
      “Republik Österreich”,”Republik Österreich”

      someDia manyDia
      Arabische Republik Syrien ????????? ??????? ??????? (al-Dschumh?riyya al-?arabiyya as-s?riyya, Arabische Republik Syrien)
      K�nigreich Spanien Reino de Espa�a
      Republik Albanien Republika e Shqip�ris� (Republik Albanien)
      Republik �sterreich Republik �sterreich

    2. thatjeffsmith Post
      Author

      SQL Developer’s script output window is just a java window…it’s not console output like what you see when running SQLcl in bash or windows cmd.

      It works in spite of itself, with a few exceptions – notably the above in what you describe.

      It’s not officially supported in SQLDev – but I use it when doing certain things.

  2. Hi Jeff

    I have issue with Arabic word. When inserting the arabic text its showing like ????.
    Please plz help me.

    Thanks in Advance boss.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  3. i have db with us7ascii charter set.
    i see hebrew in “putty”
    but i didnt see heberew in sql developer
    what to do
    pls help

    1. thatjeffsmith Post
      Author
  4. Hi Jeff,

    Saw this post of yours and was trying to display some non-English text in SQL Developer using the Nirmala UI font. However, I noticed that a few words were not being displayed correctly…a case of symbols missing from the words rather than junk characters being displayed. Just to be sure that it wasn’t an issue with the font, I copy-pasted the text from SQL Developer into an MS Word document set to the same font. The word was displayed correctly in MS Word.
    Do you have any pointers on how to resolve this?

    Thanks,
    Sujoy

    1. thatjeffsmith Post
      Author
      1. Hi Jeff,

        I am on Windows. For testing this I have been using a modified version of Matthias Roger’s query (https://marogel.wordpress.com/2012/03/23/learning-foreign-languages-with-oracle-sql).

        Here is the relevant text (it is displayed correctly here in the comment): মার্চ
        And here is the query, in case you want to run it on your SQL Developer:
        with y as
        (
        select
        add_months(date’2012-01-01′, level-1) monn,
        to_char(add_months(date’2012-01-01′, level-1), ‘MONTH’) mon
        from dual
        connect by level<=12
        )
        select
        value as language,
        y.mon,
        to_char(y.monn, 'MONTH', q'|nls_date_language='|' || value || q'|'|') month,
        to_char(y.monn, 'MON', q'|nls_date_language='|' || value || q'|'|') month_s
        from v$nls_valid_values n, y
        where n.parameter='LANGUAGE'
        and value ='BANGLA'
        and trim(mon) = 'MARCH'
        order by language, y.monn;

        Database character set: AL32UTF8
        NLS_LANG set in Windows registry: AMERICAN_AMERICA.WE8MSWIN1252

        1. thatjeffsmith Post
          Author
          1. Hi Jeff,

            Did you try the query I pasted in the comment? It should return only one row.

            Thanks,
            Sujoy

  5. Hi Jeff,
    I have an issue while inserting chinese char into my Oracle DB.
    I am using :
    =====================================
    Java(TM) Platform 1.6.0_45
    Oracle IDE 3.2.20.10.20
    Versioning Support 3.2.20.10.20.
    =====================================
    My NLS Values are :
    NLS_RDBMS_VERSION 12.1.0.1.0
    NLS_NCHAR_CONV_EXCP FALSE
    NLS_LENGTH_SEMANTICS BYTE
    NLS_COMP BINARY
    NLS_DUAL_CURRENCY $
    NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
    NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
    NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
    NLS_TIME_FORMAT HH.MI.SSXFF AM
    NLS_SORT BINARY
    NLS_DATE_LANGUAGE AMERICAN
    NLS_DATE_FORMAT DD-MON-RR
    NLS_CALENDAR GREGORIAN
    NLS_NUMERIC_CHARACTERS .,
    NLS_NCHAR_CHARACTERSET AL16UTF16
    NLS_CHARACTERSET WE8MSWIN1252
    NLS_ISO_CURRENCY AMERICA
    NLS_CURRENCY $
    NLS_TERRITORY AMERICA
    NLS_LANGUAGE AMERICAN
    ====================================

    What should I do?
    Thank You.
    Hendi Santika

    1. thatjeffsmith Post
      Author

      check to see if your editor font supports that character

      give me a test case, as in, show me your INSERT and SELECT output

      otherwise, you’re asking me to guess

      and, your software is pretty old, but that version, as well as all versions of sqldev, have always been 100% unicode compliant

      1. Hi Jeff,
        This is my DDL Table Script :
        CREATE TABLE “WMS_12M”.”WM_R_PAGE_TEXT_DESC”
        ( “PAGE_TEXT_DESC_ID” NUMBER,
        “LANGID” NUMBER,
        “PAGE_TEXT_ID” NUMBER,
        “DESCRIPTION” VARCHAR2(2000 BYTE),
        “UPDATEDBY” VARCHAR2(50 BYTE),
        “DTUPDATED” DATE,
        CONSTRAINT “WM_R_PAGE_TEXT_DESC_PK” PRIMARY KEY (“PAGE_TEXT_DESC_ID”)
        USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
        STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
        BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
        TABLESPACE “WMS_12M” ENABLE,
        FOREIGN KEY (“PAGE_TEXT_ID”)
        REFERENCES “WMS_12M”.”WM_R_PAGE_TEXT” (“PAGE_TEXT_ID”) ENABLE
        ) SEGMENT CREATION IMMEDIATE
        PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
        NOCOMPRESS LOGGING
        STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
        BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
        TABLESPACE “WMS_12M” ;

        I use dialog input font. It can show the chinese char on the SQL worksheet. But I can not insert it.

        Here my query :
        UPDATE WM_R_PAGE_TEXT_DESC SET DESCRIPTION = ‘若存有空白或不完整,请勿签署此表格。’ WHERE PAGE_TEXT_DESC_ID = 2225;

        But result :
        2225 2 2288 ¿¿¿¿¿¿¿¿¿,¿¿¿¿¿¿¿¿

        Please give me an advice.

        Thank You.

        1. thatjeffsmith Post
          Author

          Two things.

          Here’s what I always do to start debugging a character display issue, can you select it from dual?

          Second thing, you’re inserting the text into a VARCHAR2 column, so it’s going to use the database character set. Does your database character set support those characters? If not, you’ll need to store it in a NVARCHAR2 column.

    1. thatjeffsmith Post
      Author
      1. thatjeffsmith Post
        Author
  6. I have a issue that I use sql developer connect to a Timesten server(character is WE8ISO8859P1).When i query data at sql developer it will display messy code. PS: We use WE8ISO8859P1 to store Chinese at Timesten, So could u tell me how to set my sql developer character set?

  7. Hi Jeff,

    I’m having an issue getting non-English characters to display in my INSERT statement export, but can see the characters fine in my Query Result window. Is there a simple solution to this problem? Exporting to Excel does not present a problem either.

    Thanks,

    John

    1. thatjeffsmith Post
      Author
    2. thatjeffsmith Post
      Author
      1. I’m having issues with all Chinese characters. I’m running Version 4.0.3.16. I’m using the font called Dialog. My default encoding selection is Cp1252. I’ve tried some other encoding options but haven’t been successful.

        1. thatjeffsmith Post
          Author
          1. UTF-8 does not resolve my issue. Selecting UTF-8 changes what should be Chinese characters from a series of question marks to ‘è‹�自强’.

          2. thatjeffsmith Post
            Author
  8. Just wanted to update with my follow-up question because it might be relevant to others. Although I need to see the data in other languages, I still like to use the original ORA error messages in english (it’s clear and searchable).
    So, with Jeff’s help. Now I have the NLS_LANG environment variable as AMERICAN_AMERICA.AL32UTF8 and I also modified the
    Preferences -> Database -> NLS:Language to english and it did the job.

  9. From the JDBC Developer’s Guide http://docs.oracle.com/database/121/JJDBC/global.htm#JJDBC28643

    “Starting from Oracle Database 10g, the NLS_LANG variable is no longer part of the JDBC globalization mechanism. The JDBC driver does not check NLS environment. So, setting it has no effect.”

    This makes sense to me since Java is Unicode anyway.

    If you run the following query from SQL Developer:

    select distinct client_charset
    from V$SESSION_CONNECT_INFO
    where sid = (select sid from v$mystat where rownum = 1);

    The answer should be ‘Unknown’ no matter what NLS_LANG is set to.

    1. thatjeffsmith Post
      Author

      True – but folks don’t realize this and go into their NLS settings/windows registries/etc and start mucking around – and this WILL affect their other local Oracle applications and programs.

Leave a Reply

Your email address will not be published. Required fields are marked *