Character Set Issues - NLS_LANG

[Jason McCleskey]

Company recently switched from Toad to Pl/Sql Developer. Just encountered issue where a query with Chinese (or Japanese) characters issued from PSD does not return results but the same query executed from Toad (or Oracle Sql Developer) on same pc/database/oracle client works fine and returns expected row.

This is an NLS_LANG related issue in terms of the way the tools are working. The database in question is using AMERICAN_AMERICA.UTF8 lang/terr/charset. Querying v$session_connect_info or using PSD Support Info tab I can tell PSD is using AMERICAN_AMERICA.WE8MSWIN1252. The other tools show either UTF16 (Toad) or UTF8 (Sql Developer).

We work in a global system where we are routinely dealing with multiple different languages. Our PC's are locked down so we cannot change settings such as the Control Panel->Regions->Administrative->Language for Non-Unicode programs or the Registry settings or environment variables or even the installed Oracle client settings.

Is there a way we can set Pl/Sql Developer appropriately via configs/preferences so it will use a given NLS_LANG setting? If I launch PSD from a command line and first set NLS_LANG = AMERICAN_AMERICA.UTF8 then it works fine. It is odd to me that the other tools using the same client are somehow ending up with a UTF8/16 setting but I can't get same behavior in Pl/Sql Developer.

Example Query:
select name
from some_table
where name = '虞绿周'

Connection/Client Details
Oracle Client: 12.1.0.2.0 (using OCI/thick client)
Windows System Locale - Language for Non-unicode programs - English (United States)
Database NLS_Lang (based on V$NLS_PARAMETERS) -> AMERICAN_AMERICA.UTF8
Pl/Sql Developer->Preferences->Oracle->Options->Unicode Enabled is checked/true
PlSql Developer->Support Info shows:
-- Info Tab:
Character Sets
Character size: 3 byte(s)
CharSetID: 0
NCharSetID: 1
Unicode Support: True
NLS_LANG: AMERICAN_AMERICA.WE8MSWIN1252
NLS_NCHAR_CHARACTERSET: AL16UTF16
NLS_CHARACTERSET: UTF8

-- Registry Tab
SOFTWARE\ORACLE\KEY_OraClient12Home1_32bit
ORACLE_HOME = C:\PROGRA~2\Oracle\ORACLE~1\product\121~1.0\client_1
ORACLE_HOME_NAME = OraClient12Home1_32bit
ORACLE_GROUP_NAME = Oracle - OraClient12Home1_32bit
NLS_LANG = AMERICAN_AMERICA.WE8MSWIN1252
Every NLS_LANG setting in this tab shows the same NLS_LANG (since multiple Oracle homes on PC)

Windows Registry - searching entire Windows registry the only keys have WE8MSWIN1252.

;Querying V$SESSION_CONNECT_INFO for sessions connected from different tools shows:
Toad connections using client_charset = UTF16 (I have no idea how/why)
PlSql Dev Connections using client_charset = WE8MSWIN1252
Sql Developer connections using client_charaset = UTF8 (I assume it's just defaulting to the database setting)

Any insight would be appreciated. Thank you!
Jason McCleskey

 

[lost]

Do you able to change the user's environment variables in "Advanced system settings" or in registry HKCU\Environment\NLS_LANG (which is the same thing)?
If not, maybe AfterConnect.sql & Login.sql (templates placed in main PLSQLdev folder) could help you.

 

[Marco Kalter]

If you start regedit.exe and go to:

SOFTWARE\ORACLE\KEY_OraClient12Home1_32bit

You will see an NLS_LANG key that is defined as follows:

NLS_LANG = AMERICAN_AMERICA.WE8MSWIN1252

Change this to:

NLS_LANG = AMERICAN_AMERICA.UTF8

 

[Jason McCleskey]

Thank you both. Problem is due to corporate security we can't edit any Registry settings or environment variables (even user environment variables). We'd have to go through corporate security to have that done and honestly it's just easier to launch Pl/Sql Developer from my own bat file with the set nls_lang in there first.

Marco - Is there any Login.sql or any script that Pl/Sql Dev utilizes where i could do the set nls_lang? I tried it with the Login.sql that lost mentioned but it didn't have any impact. Just curious since other tools are somehow either not using or override the system/oracle client level NLS_LANG. Was hoping Pl/Sql Developer had some equivalent.

Thanks,
Jason

 

[lost]

https://forums.allroundautomations....&Words=AfterConnect.sql&Search=true#Post46125

 

[Jason McCleskey]

Thanks lost. But nope, didn't work. I have tried adding "SET NLS_LANG=AMERICAN_AMERICA.UTF8" in Login.sql and AfterConnect.sql (in the installation directory) and no change. Also added it in the "Initialization" tab on the Define Connections for the specific connection and it still doesn't work.

I think Pl/Sql Developer is just going to use default Windows/Oracle Home settings unless launched in an environment with the NLS_LANG set ahead of time.

Oh and for reference this is on ...
PL/SQL Developer
Version 14.0.6.1988 (32 bit)
Windows 10 (64-bit) Build 18362
en-US(1033)/English (United States)

Thanks,
Jason

 

[lost]

Oh no! "set" is for command line, so you used it in bat-file correctly.
But AfterConnect.sql & Login.sql is for sql-scripts like that:

alter session set nls_date_format = "DD.MM.YYYY";
alter session set nls_timestamp_format = "DD.MM.YYYY HH24.MI.SSXFF";
alter session set nls_timestamp_tz_format = "DD.MM.YYYY HH24.MI.SSXFF TZR";
alter session set nls_numeric_characters = ". ";

My fault! I thought, that we can change charset during oracle session, but i've mistaken.
It seems, the only way is yours - to use a custom bat file to run PLSQLdev.

 

[lost]

Btw, on my work we haven't admin rights too. What I did in this case:
0. Save sqlnet.ora, tnsnames.ora
1. I asked corp admins to completely wipe huge (>1.2Gb) oracle client from disk and clean registry keys named "oracle".
2. Then download from oracle.com & unpack "instant client basic lite"+sqlpuls+sqlldr (if needed), to the path where you have full access. It consumed only a 125Mb of disk space.
* notice that it doesn't contain tnsping!
3. Create "NLS_LANG" environment variable corresponding to our database.
4. Move saved sqlnet.ora, tnsnames.ora to .....\instantclient_18_5\network\admin\
5. Choose correct path to oci library (e.g. d:\oracle\instantclient_18_5\oci.dll) in PLSQLdev preferences\connection settings.
That's all!
I use that config & scenario also on home computer under Win7 connecting to Oracle Cloud Database.
And it is strange, why you don't have access to user's environment vars. In my work's Win10, I opens the Windows' "Start" menu and type "environment", then choose "User environment" in search results, and I may change them on my own.

 

[Jason McCleskey]

Good call! The environment variable lockdown is new here and I was surprised we couldn't hit our own user env vars. If I do as you note and open Start->type "environment" -> Choose "Edit environment variables for your account" that does allow me to set NLS_LANG as my own env variable. Too many years of going to Advanced System Properties (which I can't access anymore) and I didn't even try to look for another way to get there. Of course I'd have to do as you said which is have corp admins cleanout registry for that to work since the registry settings still override that.

So that gives us 3 options for anyone else reading this thread:
1) Launch PlSql Dev form bat file where you explicitly set the NLS_LANG before launching the tool Example:
SET NLS_LANG=AMERICAN_AMERICA.UTF8
cd
start /b plsqldev.exe -- the /b makes sure the command windows proceeds and exits so it doesn't hang out in the background
exit

2) Clean out registry, set your own env var for NLS_LANG

3) Update registry vars for appropriate Oracle Homes to have correct NLS_LANG. This is the simplest one if you have admin right or corp security allows it

Thanks,
Jason

 

[Marco Kalter]

Note that you can also set NLS_LANG in the Params.ini file in the PL/SQL Developer installation directory or on the command-line:

plsqldev.exe nls_lang=american_america.utf8

 

[Jason McCleskey]

Perfect! That is the type of solution I was looking for. Added this in the params.ini and now it is working without having to launch from a bat file. Thank you Marco!

# Jason Customization
# Add NLS_LANG setting here to override Windows 10 region settings
nls_lang=american_america.utf8

Thank you!
Jason

 

[johnsok]

Adding nls_lang to the params.ini file worked great, thank you.