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!
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
Good Font Selection
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.
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.
59 Comments
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 ?
I’m on 19.4, and I’m successfully using a font I installed called Inconsolata.
Which font are you having problems with?
The fonts are: Droid Sans Mono, Source Code Variable
Droid Sans is working on my machine…do you have the filter enabled to hide non-fixed width fonts?
Negative, the filter is off.
No idea then…
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?
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.
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!
Check your file encoding.
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.
there are dozens of file/image sharing websites out there, like https://imgur.com/upload
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’;
https://photos.app.goo.gl/Ec2wSYfhxgowGcV2A
Thanks, that helps a ton – that’s enough for me to have a go at reproducing your scenario – stay tuned.
Привет, Джефф!
Подскажи в чем проблема? Когда я делаю Export DDL таблицы, то все русские символы превращаются в квадраты… Но в других местах SQL Developer проблем с кодирвокой нет!!!
When you export to file, make sure you pick an encoding that can support your Cyrillic characters.
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
Hi Jeff
I have issue with Arabic word. When inserting the arabic text its showing like ????.
Please plz help me.
Thanks in Advance boss.
TNKS!
You’re very welcome Dan!
want to view Chinese data from SQL developer,please help
Did you read this post? Did you try the thing it asked you to try?
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
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
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.
Here is what I have tried.
You really save my life
thanks a lot
WOW, thanks? Let me know what else we can do to help make your day a bit better!
Thank you very much, your support is highly appreciated.
God bless you and your family.
Wow… that took quite a long to fix… but yes, you got it right!
Thanks, need to bookmark this!
I added this solution to the following thread at StackOverflow:
http://stackoverflow.com/questions/25637449/pl-sql-developer-displays-does-not-display-hebrew-characters-properly/31538258#31538258
Mentioning your post as the solution. Thanks.
You’re welcome. Now, how do I get you to use our IDE instead of Allround’s?
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?
Thanks sir,
It helped a lot.
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?
Also, make sure the default file encoding is set correctly when you go to do the export.
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.
change the file encoding to Unicode/UTF-8 and try again
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
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.
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.