Wednesday, October 31, 2012

Windows command prompt and correct national characters

Until Oracle 11g, every Oracle client installation on Windows had sqlplusw.exe, which is classic Windows application version of sqlplus. But in 11g, Oracle decided that this small application is not necessary. So only sqlplus.exe remain (not counting iSqlPlus which works only through WEB interface and additional setting). For Linux world this is far from a problem, but on Windows some of users may find in problem according this.

The Problem

Start sqlplus from command prompt (Start->Run->cmd):
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\>sqlplus tools/pass@db1

SQL*Plus: Release 11.2.0.1.0 Production on Sri Lis 24 13:20:35 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining Scoring Engine and Real Application Testing options

SQL>
Create small table with two columns: c1 number and c2 varchar2(16 char):
SQL> create table xxx (c1 number, c2 varchar2(16 char));

Table created.

SQL>
Put in this table one row with some national accented characters (in Croatia):
SQL> insert into xxx values (1,'đšžćčĐŠŽĆČ');

1 row created.

SQL> commit;

Commit complete.
Let's see from the same sqlplus the content of inserted row:
SQL> select * from xxx;

        C1 C2
---------- ----------------
         1 đšžćčĐŠŽĆČ

SQL>
Looks good. Let's see the same data from Toad:
Ouups! Something went wrong badly.
Let me look in some important properties in same command prompt, nls setings:
select * from nls_database_parameters;                        select * from nls_instance_parameters;     select * from nls_session_parameters;

PARAMETER                      VALUE                          PARAMETER                      VALUE       PARAMETER                      VALUE
------------------------------ --------------------------     ------------------------------ ---------   ------------------------------ ----------------------
NLS_NCHAR_CHARACTERSET         AL16UTF16                      NLS_LANGUAGE                   CROATIAN    NLS_LANGUAGE                   CROATIAN
NLS_LANGUAGE                   CROATIAN                       NLS_TERRITORY                  CROATIA     NLS_TERRITORY                  CROATIA
NLS_TERRITORY                  CROATIA                        NLS_SORT                                   NLS_CURRENCY                   kn
NLS_CURRENCY                   kn                             NLS_DATE_LANGUAGE                          NLS_ISO_CURRENCY               CROATIA
NLS_ISO_CURRENCY               CROATIA                        NLS_DATE_FORMAT                            NLS_NUMERIC_CHARACTERS         ,.
NLS_NUMERIC_CHARACTERS         .,                             NLS_CURRENCY                               NLS_CALENDAR                   GREGORIAN
NLS_CHARACTERSET               EE8MSWIN1250                   NLS_NUMERIC_CHARACTERS                     NLS_DATE_FORMAT                DD.MM.RR
NLS_CALENDAR                   GREGORIAN                      NLS_ISO_CURRENCY                           NLS_DATE_LANGUAGE              CROATIAN
NLS_DATE_FORMAT                DD.MM.RR                       NLS_CALENDAR                               NLS_SORT                       CROATIAN
NLS_DATE_LANGUAGE              CROATIAN                       NLS_TIME_FORMAT                            NLS_TIME_FORMAT                HH24:MI:SSXFF
NLS_SORT                       CROATIAN                       NLS_TIMESTAMP_FORMAT                       NLS_TIMESTAMP_FORMAT           DD.MM.RR HH24:MI:SSXFF
NLS_TIME_FORMAT                HH24:MI:SSXFF                  NLS_TIME_TZ_FORMAT                         NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR
NLS_TIMESTAMP_FORMAT           DD.MM.RR HH24:MI:SSXFF         NLS_TIMESTAMP_TZ_FORMAT                    NLS_TIMESTAMP_TZ_FORMAT        DD.MM.RR HH24:MI:SSXFF TZR
NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR              NLS_DUAL_CURRENCY                          NLS_DUAL_CURRENCY              kn
NLS_TIMESTAMP_TZ_FORMAT        DD.MM.RR HH24:MI:SSXFF TZR     NLS_COMP                                   NLS_COMP                       BINARY
NLS_DUAL_CURRENCY              kn                             NLS_LENGTH_SEMANTICS           BYTE        NLS_LENGTH_SEMANTICS           BYTE
NLS_COMP                       BINARY                         NLS_NCHAR_CONV_EXCP            FALSE       NLS_NCHAR_CONV_EXCP            FALSE
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE                          17 rows selected.                          17 rows selected.
NLS_RDBMS_VERSION              10.2.0.5.0

20 rows selected.
All looks more then clear and correct.
NLS_CHARACTERSET is EE8MSWIN1250, what is the default value on every Oracle Windows configuration in mine country. NLS_NCHAR_CHARACTERSET is strong enough to keep all these characters, but something is obviously wrong.
Let me show active NLS_LANG variable, in which command prompt was run:
SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\>echo %NLS_LANG%
CROATIAN_CROATIA.EE8MSWIN1250

C:\>
Looks correct also, and may say, like it should be in Windows installations, again, in our region.

The Solution

To cut a long story short, I'll show you the main reason of this behavior-Windows code page in command prompt. In regular Windows installation (all defined as Croatia or Croatian), default code page in command prompt is:
C:\>chcp
Active code page: 852

C:\>
Amazing, regardless the whole Windows run in something they call MS Win 1250, command prompt is not in the same code page.
So, changing the code page to 1250 brought me the correct result:
C:\>chcp
Active code page: 852

C:\>chcp 1250
Active code page: 1250

C:\>sqlplus tools/pass@db1

SQL*Plus: Release 11.2.0.1.0 Production on Sri Lis 24 14:34:43 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining Scoring Engine and Real Application Testing options

SQL> insert into xxx values (2,'đšžćčĐŠŽĆČ');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from xxx;

        C1 C2
---------- ----------------
         1 Đ秆źŃ榏¬
         2 đšžćčĐŠŽĆČ

SQL>
Notice that second row, C1=2, is inserted under new 1250 code page, while C1=1 is from previous, 852 code page.
Check in Toad gave me correct result also:
Looks perfect. In all environments is the same.

Make changes to be permanent

To make permanent code page 1250 in command prompt, you have to edit registry. These steps are for 32 bit Windows, so in 64 bit version something may be slightly different:
  1. Start->Run->regedit
  2. Go to [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage]
  3. Change the "OEMCP" value (from "852") to "1250"
  4. Restart Windows

The End

For many users that are Linux based users, this note might not be too important, but I knew few Windows users that were avoiding Oracle in command prompt because mentioned property. Also reason not to upgrade to 11g was nearly the same - missing sqlplusw.exe.
So now there is no need to skip installation of new Oracle 11g Client and start to use sqlplus.exe in all ways ... like you see in this post ...
:-)
Cheers!

No comments :

Post a Comment

Zagreb u srcu!

Copyright © 2009-2018 Damir Vadas

All rights reserved.


Sign by Danasoft - Get Your Sign