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

Trick

Paste the code you’re having problems with in the preview section of the FONT selector, and then scroll through the font list, until you see your characters displayed correctly.

That’ll do it.

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.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

59 Comments

  1. Hi, Jeff.
    Oracle SQL Developer (version 18.4.0.376, build 376.1900) fails to identify newly installed system fonts (Windows 10 Version 10.0.17763 Build 17763). Eclipse and Notepad do not have such a problem. Do you know any possible solution to this ?

  2. Hi Jeff,

    I have inserted following values but “Priorużs”,

    The ETL process inserts it , but when I query from SQL Developer, it is showing me

    https://imgur.com/AI1XMG0

    How do I over come this. I tried changing the fonts but didnt work.

    Thanks !

    • What is it supposed to show, I mean, what’s the actual value?

      Are you sure it was inserted correctly, that is, is it displaying correctly everywhere else?

    • Jayanath

      Hi Jeff,

      Sorry about been not clear.

      For example , if I insert the value “Priorużs” within the Oracle SQL Developer to a table, I can successfully see it when I select.

      When the ETL team loads the data , I am seeing the values similar to image (https://imgur.com/AI1XMG0).

      So I am not clear why this is happening. As per my understanding the ETL has the a full Oracle Client installed in their environment.

      is it to do with some kind of a NLS SETTINGS on Client end (ETL node) ?

      Thanks,
      Jayanath

    • Sorry, I don’t have the bandwidth to help you with your ETL tool – do they not have a support system?

      Make sure the ETL tool has it’s NLS settings together and supports extended fonts/characters.

  3. Hey, Jeff!

    Tell me what’s the problem? When I do Export DDL tables into a file, all Russian characters turn into squares … But in other places SQL Developer there are no problems with Russian characters!

    • The problem is neither in the encoding of the file. This problem occurs when you call DDL, but not everywhere … For example, through the menu Edit Table-> DDL will be displayed correctly if through Quick DDL-> Save to Worksheet then Emojis. Unfortunately, I do not know how to attach screenshots.

    • CREATE TABLE “DM_DPA”.”TEST1″ (
      “COL1” NVARCHAR2(50)
      )
      SEGMENT CREATION DEFERRED
      PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE “DMTBS”;

      COMMENT ON TABLE “DM_DPA”.”TEST1″ IS
      ‘������ �����������. Example comment’;

    • Thanks, that helps a ton – that’s enough for me to have a go at reproducing your scenario – stay tuned.

  4. Привет, Джефф!

    Подскажи в чем проблема? Когда я делаю Export DDL таблицы, то все русские символы превращаются в квадраты… Но в других местах SQL Developer проблем с кодирвокой нет!!!

    • When you export to file, make sure you pick an encoding that can support your Cyrillic characters.

  5. 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;

    • 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

    • 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.

    • Try this ,it works for my similar problem
      1. tools – preferences – environment – encoding = UTF-8
      2.RESTART sqldeveloper

  6. Hi Jeff

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

    Thanks in Advance boss.

  7. 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

    • the font, what font do you have set in SQL Developer

      also, make sure that us7ascii charset will store hebrew correctly

  8. 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

    • You’re on a mac or Windows machine?

      Can you share the word/text so I can play around with it?

    • 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

    • ok, it’s got a 1,000 lines of output. i did a quick scan on my machine and it looks good. can you narrow it down to the lines that have the issues?

    • Hi Jeff,

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

      Thanks,
      Sujoy

  9. 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

    • 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

    • 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.

    • 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.

    • please give the solution. my oracle forms getting and showing bangla font rightly. but when I selecting by SQL query as select message from smstext. it is showing Unicode text as like garbase charater as like as bdhsdag jsrtyótrj…..my database 10g unicode enabled character. column is varchar2 . I tried nvarchar2 but can’t support it on oracle form compilation. pls solve me this issue.

  10. Thank you very much, your support is highly appreciated.
    God bless you and your family.

  11. 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?

  12. 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

    • Can you give me some more information? Like, what characters exactly? What fonts are you using? What version of SQL Developer?

    • 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.

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

    • depends on your version of Chinese – figure out your charset and then the encoding that supports it

  13. 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.

  14. 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.

    • 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.

Reply To Slava Cancel Reply