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:
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:
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:- Start->Run->regedit
- Go to [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage]
- Change the "OEMCP" value (from "852") to "1250"
- 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!
