Hi,
I hesitated before posting this… on the one hand the experts will be bored and wonder why I say things everyone knows. On the other hand, as a newbie, I wish I could find such information for my own learning. So here goes. Before you try out any of this stuff, give the experts some time to point out the mistakes, I am sure there are some!
I’ve been reading about NLS_LANG from this excellent documentation: nls_lang faq
I thought I didn’t need to worry about NLS_LANG, since I don’t need globalization for my own purposes. Big mistake! The issue I am about to discuss has nothing to do with globalization.
This issue only arises on Windows systems – not in Unix. It only affects you if you like to use both SQL*Plus (in a Command box) and a GUI like Toad or SQL Developer on the same computer
When you start a database session, the client (the front-end application, like SQL*Plus or Toad) must tell the sever (where the db actually runs) what your character set is – even if both run on your laptop. Characters are transmitted just as bytes, and the db needs to know in which character set you mean those bytes to be interpreted to become characters again
Problem is, Windows systems use TWO different character sets, one for “Windows” programs and another for “DOS Prompt” based programs like SQL*Plus. (EXCEPTION: Asian languages like Chinese, Korean, Japanese use the same character set for both, so they don’t run into “my” issue.)
Before I go too far, here is how you can experiment to see if you have this problem. My setting is (American) English language and Windows 10; with another language, or another version of Windows, the example may work out slightly differently, but the issue is the same.
EXAMPLE:
- From SQL*Plus or Toad or wherever, CREATE TABLE t (col1 VARCHAR2(20)).
- Open Microsoft Word or a simiar “Windows” type editor with a new (blank) document. Insert just one character: å Copy it to the clipboard (CTRL-A, CTRL-C).
- Start or go back to Toad or SQL Developer (GUI). Navigate to table t, Data, insert new row, paste the å from the clipboard (click in the field, CTRL-V). Then COMMIT (don’t forget this step!)
- Now start or go back to SQL*Plus and SELECT * FROM t.
If this is what you get, read on:
SQL> select * from t;
COL1
----------
σ
You stored a “latin small letter a with a ring” (that is the technical name!) but what you got back is a “greek small letter sigma.” This is because Oracle thinks the character set when you query the table is the same as the character set used when the value was inserted into the table.
For GUI, Windows uses “code page” 1252 (for this discussion code page means character set, it’s just Microsoft terminology); the Oracle name for this character set is WE8MSWIN1252. However, for the DOS box the character set is code page 437; Oracle: US8PC437. So far in my example, Oracle “thinks” the SQL*Plus session uses code page 1252, which is incorrect. In code page 1252, the small letter a with a ring has hex code E5. Code page 437 also has the small letter a with a ring, but at position (hex code) C5; position E5 on code page 437 is small letter sigma.
OK, what now?
The client does’t tell the server “I’m on Windows and I am using code page 1252.” Instead, the information is encoded in a session parameter, NLS_LANG. And what is shown in NLS_LANG is the Oracle name of the character set, not the Microsoft name of the same set. The value of this parameter (on the client side) is saved either as a Registry key or as a (global, persistent) environment variable. In many cases it’s a Registry key, and the character set portion of NLS_LANG refers to the WINDOWS (GUI) character set. Fine, but how should SQL*Plus tell the server what character set it (SQL*Plus) needs to use?
We need to override the Registry key. That can be done with a global environment variable – but that is a bad solution, because the environment variable will take precedence over the Registry key for Toad, too. By the way, the SQL*Plus User’s Manual, from Oracle, instructs us to change the Registry key… that is fine if you ONLY use SQL*Plus; if you also want to use SQL Developer or Toad, that is bad advice!
Rather, you need to set a local, temporary environment variable. Start a Command Prompt window, and BEFORE starting sqlplus do this:
> SET NLS_LANG = .US8PC437 (note the period . right after the = sign, it is essential).
Now when you start SQL*Plus, connect to the db and run the same query, you will get this:
SQL> select * from t;
COL1
----------
å
Unfortunately we can’t save this in (g)login.sql to be executed when we start SQL*Plus. There is a lot of misunderstanding about this; on stackoverflow and perhaps on this forum and in other places, you hear about “ALTER SESSION SET NLS_LANGUAGE …” - that does NOT change the character set parameter! Note the distinction between LANG and LANGUAGE; in fact NLS_LANGUAGE is the first of three components that together make up NLS_LANG, the third is the character set - and NLS_LANG cannot be changed with ALTER SESSION. The character set portion of the NLS_LANG parameter MUST be set BEFORE sqlplus starts.
Being lazy, I wrote a small batch file:
SET NLS_LANG=.US8PC437
START SQLPLUS.EXE /NOLOG
Now if I run the batch file, it opens a Command Prompt for me, it sets NLS_LANG and it starts sqlplus. This local variable (as in any decent programming setting) supercedes any Registry key or global variable by the same name, but only for the extent and duration of the Command Prompt instance. The NLS_LANG setting will remain in force as long as I connect and disconnect to/from the database WITHIN THE SAME Command Prompt session. When I exit SQL*Plus AND the Command Prompt, NLS_LANG is gone. And even if I start Toad while the Command Prompt box is still open, that has no effect on Toad, because the local NLS_LANG variable is visible only to the Command Prompt instance. Toad will use the NLS_LANG key from the Registry instead, as it normally would.
One final trick – again being lazy, I navigated to my TaskBar folder, right-clicked the icon for “Run SQL Command Prompt”, went to Properties > Shortcut and changed the target to point to my batch file (instead of the sqlplus executable). Easier than pinning the batch file to the task bar and having to hunt for the nice SQL*Plus icon (so I can assign the icon to the batch file on the task bar). By the way, the TaskBar folder is here (Windows 10):
%AppData%\Microsoft\Internet Explorer\Quick Launch\User Pinned\TaskBar
At the command prompt, do > ECHO %AppData% to find out what folder that is in your case.
Oh – and I hope I didn’t confuse you so much as to believe CHR(n) in a SQL or PL/SQL statement has anything to do with your (client-side) character set; that is always in the database character set, which is independent of your front-end application. You shouldn’t have to worry about the database character set, but if you are curious: SELECT * FROM NLS_DATABASE_PARAMETERS
A lot more information, very clear and full explanations and examples in the article I linked to at the top.
Hope this helps! Cheers, mathguy-ro