Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

NLS_LANG setting on SQLPLUS

Posted on 2013-11-01
6
Medium Priority
?
4,513 Views
Last Modified: 2013-12-13
I am trying to set NLS_LANG setting to POLISH_POLAND.EE8PC852 in a batch file. The batch file further executes sql files as under

set NLS_LANG=POLISH_POLAND.EE8PC852
sqlplus username/password@sid @SetLang.sql

Open in new window


SetLang.sql updates some tables in my database. When I call this bat file the special characters do not get updated in my database but when I execute the same sql from sqldeveloper they get updated.

Where am I going wrong?
0
Comment
Question by:jim_imtech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 6

Expert Comment

by:Greg Clough
ID: 39616457
What format is the SetLang.sql file in?  I'm presuming that your database is in EE8PC852 character set, but is the SQL file in the same format... or is it in UTF-8?

Remember, you need to set the NLS_LANG to match the "client", not the "database".

Alternatively, it appears you're using csh.  Have you tried using "setenv"?

setenv NLS_LANG=POLISH_POLAND.EE8PC852

Open in new window


If you could give a dump of the SQL file (using "od"), and of the database rows (using "dump(YOUR_COLUMN_HERE,1016)") that would be helpful.
0
 

Author Comment

by:jim_imtech
ID: 39616479
My sql file has simple update statements

update TBLDETECTOR_TYPEID set typedescription = 'Predkosc' where typedescription = 'Speed';

commit;

Open in new window


Please note the special characters in 'Predkosc' are removed....

The database column shows Pr¦Ödko+T¦ç
Using setenv  has the same result
0
 

Author Comment

by:jim_imtech
ID: 39616487
You are right. My sql file is in UTF-8. Is that the issue? How can I save it in EE8PC852 using notepad or Notepad++?
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 6

Expert Comment

by:Greg Clough
ID: 39617126
If you set your NLS_LANG thus:

set NLS_LANG=POLISH_POLAND.AL32UTF8

Open in new window


Then Oracle will know that the file you are loading is UTF-8, and convert it to whatever character set the database is using... in this case EE8PC852.  There is a very common misconception that you need to set your NLS_LANG to match your database, but that is dangerously wrong.  Always set your NLS_LANG to match the client that's connecting to the database and let the magic of TNS character set translation sort the conversion for you.

If the character set of your client is set to match your database, then Oracle presumes there is no character translation required and loads whatever you feed it directly into the database... thus you're getting UTF-8 data in your database, but when it's read back the database is presuming it's EE8PC852.  This type of corruption is difficult if not impossible to reverse.

NOTE: I'm presuming you're on v10 or v11 of Oracle as their UTF-8 character set is AL32UTF8, as otherwise if you're on v9 or earlier then I'll have to google to remember what it was.
0
 

Author Comment

by:jim_imtech
ID: 39624400
I am running this script from SQLPLus and looking at the link http://docs.oracle.com/html/B10546_01/gblsupp.htm there is no setting of AL32UTF8 for Dos.

I still tried it but I get SP2-0734 error. Basically sqlplus finds junk characters around my sql. Any suggestions?

I am using Oracle 11.2
0
 
LVL 6

Accepted Solution

by:
Greg Clough earned 1500 total points
ID: 39629051
That URL points to a v9.2 document, and I believe that the availability of Windows Codepages has moved on quite a lot on Windows since those days.

As far as I'm aware, even if your version of Windows doesn't support UTF-8, so long as the file is actually encoded in UTF-8 and you have your NLS_LANG set correctly, then Oracle will read and process the file as a UTF-8 file and do character set translation so that it gets inserted into your database correctly.

Again, not being a windows guy I can only suggest that you follow the advice from here:

http://www.orafaq.com/wiki/NLS_LANG

Which basically says to run the "set" command in your CMD window prior to starting sqlplus.  It should be able to make this persistent by setting the Registry Key to the correct value.  You should search for "NLS_LANG" underneath:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to recover a database from a user managed backup

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question