5 Replies Latest reply: Dec 31, 2007 3:17 AM by Billy~Verreynne RSS

    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. :-)