I have just recently started looking at the standalone SQLcl client. I have pulled the latest download (sqlcl-4.2.0.16.112.0616-no-jre.zip) and am running it on Windows 7.
My question is that I seem to be getting incorrect data from the database and am wondering if there is a client setting that I am missing?
Additional information:
Database: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production, running on Windows OS
Database character set: WE8MSWIN1252
Windows ANSI 1252 character set table: https://msdn.microsoft.com/en-us/goglobal/cc305145
Windows OEM 437 character set table: https://msdn.microsoft.com/en-us/goglobal/cc305156
Test Case:
1. Create a simple table
create table charset_test( a varchar2(5 char) );
2. Insert some characters into the database using the database's character set (no conversion from client to server for non-ASCII characters)
-- Latin Small Letter A: a
insert into charset_test( a ) values ( 'a' );
-- Inverted Exclamation Mark: ¡
insert into charset_test( a ) values ( chr(161) );
-- Latin Capital Letter C With Cedilla: Ç
insert into charset_test( a ) values ( chr(199) );
-- Latin Small Letter I With Acute: í
insert into charset_test( a ) values ( chr(237) );
commit;
3. Query the data using SQLCl
col DMP for a50
select a, dump(a, 1010) DMP from charset_test order by a;
A DMP
----- --------------------------------------------------
a Typ=1 Len=1 CharacterSet=WE8MSWIN1252: 97
¡ Typ=1 Len=1 CharacterSet=WE8MSWIN1252: 161
Ç Typ=1 Len=1 CharacterSet=WE8MSWIN1252: 199
í Typ=1 Len=1 CharacterSet=WE8MSWIN1252: 237
4 rows selected.
There are two problems with the returned data.
a) The non-ASCII characters are not returned properly to the client, just the ASCII 'a'.
b) There are extra leading characters returned for the non-ASCII characters.
I am asking if there is a client setting because I know that was required using SQL*Plus. With it being a command line utility on Windows, it runs using the OEM code page.
-- Command prompt
C:\>chcp
Active code page: 437
-- If I set NLS_LANG to properly indicate the client is using the OEM character set, things work in SQL*Plus
set NLS_LANG=AMERICAN_AMERICA.US8PC437
-- Then run SQL*PLus, connect to the same database as the same user, and run the same query.
select a, dump(a, 1010) DMP from charset_test order by a;
A DMP
----- --------------------------------------------------
a Typ=1 Len=1 CharacterSet=WE8MSWIN1252: 97
¡ Typ=1 Len=1 CharacterSet=WE8MSWIN1252: 161
Ç Typ=1 Len=1 CharacterSet=WE8MSWIN1252: 199
í Typ=1 Len=1 CharacterSet=WE8MSWIN1252: 237
4 rows selected.
SQL*Plus properly translates the non-ASCII characters from the server's 1252 character set to the client's OEM 437 character set and displays the proper characters.
As stated in other threads I have found in this forum, setting NLS_LANG makes no difference to the results seen using SQLCl.