Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

how to use sqlplus with utf8 on windows command line

2920439Dec 19 2007 — edited Dec 31 2007
I expected sqlplus to work interactively with the following settings:
- Lucida Console font for command window
- chcp 65001 (UTF8-Windows-Codepage)
- set NLS_LANG=.AL32UTF8

But it turned out that
1) sqlplus apparently does not use WIN32-API function ReadConsoleW and WriteConsoleW (these wide char functions must be used instead of ReadFile and WriteFile for Console I/O)
2) cmd silently ignores all .cmd and .bat files with codepage set to 65001.

I fear that such a problem regarding a minority of command line freaks has low priority for Oracle and for Microsoft, so I wrote two small filters in C.

rconsu8 passes keyboard input to stdout, using ReadConsoleW
wconsu8 passes stdin to console, using WriteConsoleW
both set the UTF8-codepage 65001 and restore the previous codepage at exit.

Anybody interested?
(Yes, I know that I could use sqldeveloper.)

Sample session:
C:\>set NLS_LANG=.AL32UTF8
C:\>rconsu8 | sqlplus scott/tiger@DBUTF8 | wconsu8

SQL> select '€' from dual;

'€'
---


SQL> select * from test_cyrillic;

NAME
---------------------------------------------------------------------------
Йозеф

Euro €

Comments

Sven W. Dec 19 2007
Do you have the same problem when using "sqlplusw" ?
2920439 Dec 19 2007
Forget sqlplusw if you want to display characters not contained in your Windows-ANSI-Codepage, e.g. cyrillic characters. AFAIK sqlplusw is a non unicode capable application, character set is restricted by ANSI codepage, default is 1252 for Western Europe. sqlplusw requires that the NLS_LANG encoding is set according to the ANSI codepage, that is WE8MSWIN1252 for codepage 1252.
Sven W. Dec 19 2007 — edited on Dec 19 2007
Ah I think now I understand what you are referring to.

SQL*PLUS is able to show the correct sign/codepages. However windows is not able to render the output from sqlplus correctly.

So far I didn't feel this is restricting much, because users usually don't use SQL*PLUS. But it is annoying/confusing somtimes for the developer.
2920439 Dec 29 2007
Console Output
----------------------
works for characters covered by Lucida Console font if the first character in a line is ASCII (code < 128, e.g. blank)
CHCP 65001
sqlplus
SQL> select ' ', unistr('Josef \0419\043E\0437\0435\0444') from dual;
Josef Йозеф

SQL> select ' ',unistr('Euro \20AC') from dual;
Euro €

Sven, windows is able to render the output (as covered by Lucida Console), but there is a quirk in MSVCRTxx (C Runtime-Lib), function fwrite(). The first byte in a line is passed separately to an isolated single write() call, which causes conversion of an utf8 sequence to fail.

Console Input
--------------------
For non-ascii characters (code >= 128), ReadConsoleW must be called, e.g. through a simple filter converting UTF-16 stdin to UTF-8 stdout.
Billy Verreynne Dec 31 2007
Nice. (from a fellow CLI freak)

Never had to worry or deal with Unicode using SQL*Plus thus far though... touch wood. Also, using Linux almost exclusively these days. :-)
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 28 2008
Added on Dec 19 2007
5 comments
35,555 views