NLS Problems

[datadeconline]

Hi Allround Automations,

We work with PL/SQL Developer and connect to differents databases with different NLS_CHARACTERSET . We work with WE8ISO8859P1 and WE8MSWIN1252 and two UTF8 database, I try to redefine the NLS_LANG environment variable at execution time I think about use of AfterConnect.sql, Login.sql files but I don't know howto and I don't know how to do system call from PL/SQL Developer.
Any idea ...

Regards
PGutierrez

 

[Marco Kalter]

You cannot set the character set from within SQL or PL/SQL. This can only be done before application startup, for example by setting the NLS_LANG environment variable. You could create 2 command scripts for 2 different character sets that set NLS_LANG and execute plsqldev.exe.

 

[datadeconline]

ok, I try with this.
Can I disable connect options from PL/SQL Developer:

* "connect" option from Command Window
* "File/New Instance"
* "Session/Log on..."
* "Session/Log off"

Kind Regards
PGutierrez

 

[Zen]

Example why you should probably NOT make your client the same NLS_LANG as the database. The Client NLS_LANG is not meant to be 'dynamic'. Look at my example below.

CORRECT (Different characterset database - client)
Setting the environment for DOS SQL*Plus:

D:\tools\scripts>set NLS_LANG=dutch_america.WE8PC850
D:\tools\scripts>c:\oracle\ora92\bin\sqlplus.exe

SQL*Plus: Release 9.2.0.8.0 - Production on Wo Nov 21 09:46:19 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name:system@tst9ip1
...
system@TST9IP1> @.[%NLS_LANG%]. (Trick to show the NLS_LANG being used)
SP2-0310: unable to open file ".[dutch_america.WE8PC850]..sql"

NLS_LANG is set at WE8PC850 (Correct nls_lang for MSDOS West European)

system@TST9IP1> select * from nls_database_parameters where parameter='NLS_CHARACTERSET';

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET WE8ISO8859P1

Making a test table.

system@TST9IP1> create table test ( col varchar2(1));
Table created.

system@TST9IP1> insert into test values ('

 

[datadeconline]

Hi Sven,

I understand all this problem about NLS but in "PL/SQL Developer/Help/Support Info..." we show information about NLS with this:

Character Sets
Character size: 1 byte(s)
CharSetID: 31
NCharSetID: 2000
Unicode Support: True
NLS_LANG: SPANISH_SPAIN.WE8MSWIN1252
NLS_CHARACTERSET: WE8ISO8859P1
NLS_NCHAR_CHARACTERSET: AL16UTF16

Can I access to this variables/values with any command and display in a macro, I try with this but I can't show:
SQL> HOST echo %PATH%
%PATH%
SQL> HOST ECHO .[%PATH%].
.[%PATH%].
SQL> HOST ECHO .[%PATH%].
.[%PATH%].

Regards
PGutierrez