OTN Discussion Forums : sqlplus in Windows and Linux display ...

user495336

Posts: 53
Registered: 03/14/06

sqlplus in Windows and Linux display same data diversely
Posted: Apr 12, 2007 7:55 AM

Database 10.2.0.1.0 on Linux.
NLS parameter from nls_database_parameters:

NLS_CHARACTERSET = AL32UTF8
NLS_NCHAR_CHARACTERSET = AL16UTF16

Environment variable:
NLS_LANG=ESTONIAN_ESTONIA.AL32UTF8

When I insert characters like öü from sqlplus they appear correctly.
But if I try to insert character ä (test is table with one varchar2(19) column)

insert into test values('ä');

I recive:

ORA-ORA-01756: quoted string not properly terminated

I don't understand why can't I insert that character.

When I use sqlplus on my Windows machine I see ?? instead of characters öü which I inserted successfully in Linux.

On Windows I'm able to insert characters öüä into table test. I can aslo quey them and see correctly. But if I query them from Linux sqlplus then I see just one starnge caracter ā instead of öüä.

On Windows I have NLS_LANG=ESTONIAN_ESTONIA.BLT8MSWIN1257

Can somebody help me with that issue. I'm very confused. I would like to be able to insert and see character öüä from both environments.

orafad

Posts: 5,752
Registered: 02/04/99

Re: sqlplus in Windows and Linux display same data diversely
Posted: Apr 12, 2007 8:04 AM

in response to: user495336

Reply

Please post back output of (replace with your table/column names in query below)
select column,dump(column,1016) from test_table;

user495336

Posts: 53
Registered: 03/14/06

Re: sqlplus in Windows and Linux display same data diversely
Posted: Apr 12, 2007 11:55 PM

in response to: orafad

Reply

In linux

A          DUMP
---------- ------------------------------------------------------------------
ā
 köök      Typ=1 Len=4 CharacterSet=AL32UTF8: 6b,f6,f6,6b
öü         Typ=1 Len=2 CharacterSet=AL32UTF8: f6,fc
õ          Typ=1 Len=1 CharacterSet=AL32UTF8: f5

In Windows

A          DUMP
---------- --------------------------------------------------------------
öüä        Typ=1 Len=8 CharacterSet=AL32UTF8: e2,80,9d,c2,81,e2,80,9e
k??k       Typ=1 Len=4 CharacterSet=AL32UTF8: 6b,f6,f6,6b
??         Typ=1 Len=2 CharacterSet=AL32UTF8: f6,fc
?          Typ=1 Len=1 CharacterSet=AL32UTF8: f5

orafad

Posts: 5,752
Registered: 02/04/99

Re: sqlplus in Windows and Linux display same data diversely
Posted: Apr 13, 2007 1:15 AM

in response to: user495336

Reply

A          DUMP
----------
------------------------------------------------------
--------
öüä        Typ=1 Len=8 CharacterSet=AL32UTF8:
e2,80,9d,c2,81,e2,80,9e
k??k       Typ=1 Len=4 CharacterSet=AL32UTF8:
6b,f6,f6,6b
??         Typ=1 Len=2 CharacterSet=AL32UTF8: f6,fc
?          Typ=1 Len=1 CharacterSet=AL32UTF8:
f5

Before you worry about the output/display aspect, you have illegal data stored in the db. E.g. there is no single-byte code points F5, F6 or FC in UTF-8.

This is probably due to having char set part of NLS_LANG incorrectly set. In the Linux system, are you sure it is setup to produce utf-8? I think the Linux system uses a single-byte Iso Latin encoding.

What code page are you using on the Windows side?

Also you have (in the first row above) special Windows characters right double quote marks (U+201D) and double low quote marks (U+201E), plus a control "character", not defined in Windows-1257. The Windows special characters does not exist in the closest-matching Iso char set (Latin-4?).

How are you inserting the data? What does the users use to enter the data?

Message was edited by:
orafad

user495336

Posts: 53
Registered: 03/14/06

Re: sqlplus in Windows and Linux display same data diversely
Posted: Apr 13, 2007 2:14 AM

in response to: orafad

Reply

In the Linux system, are you sure it is setup to produce utf-8?

In Linux I have environment variable NLS_LANG=ESTONIAN_ESTONIA.AL32UTF8

In Windows I have NLS_LANG=ESTONIAN_ESTONIA.BLT8MSWIN1257

How are you inserting the data?

The first line is inserted via sqlplus in Windows and other rows via sqlplus in Linux.

orafad

Posts: 5,752
Registered: 02/04/99

Re: sqlplus in Windows and Linux display same data diversely
Posted: Apr 13, 2007 7:13 AM

in response to: user495336

Reply

In the Linux system, are you sure it is setup to produce utf-8?

In Linux I have environment variable
NLS_LANG=ESTONIAN_ESTONIA.AL32UTF8

In Windows I have
NLS_LANG=ESTONIAN_ESTONIA.BLT8MSWIN1257

What I meant was that, did you setup linux to use utf-8 encoding? Setting nls_lang char set does not change the char set of the os, you use it to indicate what you in fact have.

Same thing in Windows. Is your current ACP set to windows-1257?

user495336

Posts: 53
Registered: 03/14/06

Re: sqlplus in Windows and Linux display same data diversely
Posted: Apr 16, 2007 12:20 AM

in response to: orafad

Reply

What I meant was that, did you setup linux to use utf-8 encoding?

How can I check it? Does environment variable LANG shows it? Curretly I have LANG=en_US.UTF-8

Same thing in Windows. Is your current ACP set to windows-1257?

How can I check it?

orafad

Posts: 5,752
Registered: 02/04/99

Re: sqlplus in Windows and Linux display same data diversely
Posted: Apr 16, 2007 1:07 AM

in response to: user495336

Reply

How can I check it? Does environment variable LANG
shows it? Curretly I have LANG=en_US.UTF-8

That may go only half-way. How do you login to the Linux machine? Via terminal emulation app on Windows? Which one and how is this setup regarding character set?

Same thing in Windows. Is your current ACP set to windows-1257?

How can I check it?

command prompt (if on XP)
c:\> reg query HKLM\System\CurrentControlSet\Control\Nls\Codepage /v ACP

If reg query command does not exist, use a registry editor to verify setting.

user495336

Posts: 53
Registered: 03/14/06

Re: sqlplus in Windows and Linux display same data diversely
Posted: Apr 16, 2007 5:58 AM

in response to: orafad

Reply

How do you login to the Linux machine? Via terminal emulation app on Windows? Which one and how is this setup regarding character set?

I use putty to login to the Linux machine. There was charcter set translation option set so that recived data assumed to be in ISO-8859-13:1998 (Latin 7, Baltic)

Now I changed it to UTF8 and when I enterd characters öäü into table then when I query then frin windows sqlplus I see the following output

÷õ³

c:\> reg query HKLM\System\CurrentControlSet\Control\Nls\Codepage /v ACP

! REG.EXE VERSION 3.0

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Nls\Codepage
ACP REG_SZ 1257

orafad

Posts: 5,752
Registered: 02/04/99

Re: sqlplus in Windows and Linux display same data diversely
Posted: Apr 16, 2007 6:15 AM

in response to: user495336

Reply

Now I changed it to UTF8 and when I enterd characters öäü into table

Now when Linux (locale) and Putty is set for UTF-8, and you also set NLS_LANG char set accordingly,

Can you show (copy paste) output from insert and select dump(col,1016) from table where ...; from the linux machine

Please also paste output from Windows
SQL> @[.%NLS_LANG%.]
plus similar select dump as above.

then when I query then frin windows
sqlplus I see the following output

÷õ³

Is this gui app (sqlplusw) or dos app (sqlplus)?

user495336

Posts: 53
Registered: 03/14/06

Re: sqlplus in Windows and Linux display same data diversely
Posted: Apr 16, 2007 6:26 AM

in response to: orafad

Reply

The output of dump(...) in Linux

DUMP(A,1016)
--------------------------------------------------------------------------------
Typ=1 Len=10 CharacterSet=AL32UTF8: c3,b6,c3,a4,c3,bc,20,75,74,66

Normal select gives

öäü utf

Same dump in Windows

DUMP(A,1016)
--------------------------------------------------------------------------------
Typ=1 Len=10 CharacterSet=AL32UTF8: c3,b6,c3,a4,c3,bc,20,75,74,66
 
SQL> @[.%NLS_LANG%.]
SP2-0310: unable to open file "[.ESTONIAN_ESTONIA.BLT8MSWIN1257.]"

Hm is this gui app (sqlplusw) or dos app (sqlplus)?

This is dos app sqlplus not sqlplusw

orafad

Posts: 5,752
Registered: 02/04/99

Re: sqlplus in Windows and Linux display same data diversely
Posted: Apr 16, 2007 6:38 AM

in response to: user495336

Reply

DUMP(A,1016)
------------------------------------------------------
--------------------------
Typ=1 Len=10 CharacterSet=AL32UTF8:
c3,b6,c3,a4,c3,bc,20,75,74,66

Normal select gives

öäü utf

Good! Now data is coming in and get stored without problems.

SP2-0310: unable to open file
"[.ESTONIAN_ESTONIA.BLT8MSWIN1257.]"

This is dos app sqlplus not sqlplusw

So one conversion problem is left, when displaying in dos box sql*plus.

1257 is used in Ansi environment, for OEM (dos/console) you would need to check e.g. via chcp command and then set NLS_LANG accordingly.

Example
C:\>chcp
Aktuell teckentabell: 850

C:\>set nls_lang=.we8pc850

C:\>sqlplus usr/pw@host/service