how to use sqlplus with utf8 on windows command line

joehtg
    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 €
      • 1. Re: how to use sqlplus with utf8 on windows command line
        Sven W.
        Do you have the same problem when using "sqlplusw" ?
        • 2. Re: how to use sqlplus with utf8 on windows command line
          joehtg
          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.
          • 3. Re: how to use sqlplus with utf8 on windows command line
            Sven W.
            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.
            • 4. how to use sqlplus with utf8 on windows command line
              joehtg
              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.
              • 5. Re: how to use sqlplus with utf8 on windows command line
                Billy~Verreynne
                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. :-)