4 Replies Latest reply on Jan 5, 2018 2:46 PM by chijar

    sqlplus with utf8 (command line)

    chijar

      Dear all,

      Well, after I change my character set on my database 11204  from 1252 to  AL32UTF8, I tested insert with different characters like this:

      Drop table scott.test purge;
      Create table scott.test ( LANGUAGE_NAME VARCHAR2(40 CHAR), WELCOME_TRANSLATION VARCHAR2(40 CHAR));
      insert into scott.test values ('Modern Greek','Καλώς Ορίσατε');
      insert into scott.test values ('Chinese - Cantonese','歡迎');
      insert into scott.test values ('Chinese - Mandarin','歡迎光臨');
      insert into scott.test values ('Bengali','স্বাগতম');
      insert into scott.test values ('Arabic','أهلاً و سهلاً');
      insert into scott.test values ('Georgian','კეთილი იყოს თქვენი');
      insert into scott.test values ('Gujarati','પધારો');
      insert into scott.test values ('Lao','ຍິນດີຕ້ອນຮັບ');
      insert into scott.test values ('Persian - Farsi',' خوش آمدید');
      insert into scott.test values ('Limburgish','Wilkóm');
      insert into scott.test values ('Korean','환영합니다');
      insert into scott.test values ('Japanese','ようこそ');
      insert into scott.test values ('Russian','Добро пожаловать!');
      insert into scott.test values ('Czech','Vítáme tĕ');
      insert into scott.test values ('Polish','Witam Cię');
      commit;
      select * from scott.test;
      

       

      Well, as per I'm describing, the results looks good on :

      - Toad for Oracle 11

      - PL/SQL developer 11

      - SQL developer

      but, when I try to view from my command line (sqlplus) those characters doesn't show correct.

       

      Actions done:

      action 1:

      chcp 65001

      set NLS_LANG=AMERICAN_AMERICA.AL32UTF8 (on DOS)

      execute sqlplus

      select * from scott.test;

       

      action 2:

      set NLS_LANG=AMERICAN_AMERICA.AL32UTF8

      execute sqlplus

      select * from scott.test;

       

      action 3:

      I have created NLS_LANG on "environment variables" on computer properties on the USER VARIABLES and I put the same value of the above actions (AMERICAN_AMERICA.AL32UTF8)

      execute sqlplus

      select * from scott.test;

       

      But those actions do not change anything.

       

      Facts:

      Oracle db 11204

       

       SELECT *
      FROM NLS_DATABASE_PARAMETERS
      WHERE PARAMETER LIKE '%CHARACTERSET';  2    
      PARAMETER------------------------------
      VALUE
      --------------------------------------------------------------------------------
      NLS_CHARACTERSET
      AL32UTF8
      NLS_NCHAR_CHARACTERSET
      AL16UTF16
      
      

       

      Client side (from I execute sqlplus through command prompt)

      windows 7 SP1

      oracle client 11201

       

      I saw: this:

      how to use sqlplus with utf8 on windows command line

      but, seriously, I cannot got it.

       

      I read the articles on metalink:The Correct NLS_LANG in a Microsoft Windows Environment (Doc ID 179133.1)

      and on this they describe the following:

      Note that:

      * The "Unicode" "chcp 65001" and "chcp 65000" values are NOT supported with sqlplus.exe.

       

      really? that means I never saw all characters through sqlplus executed on command window?

      If u know any other method to view those characters, please your kindly help.

      thanks a lot

        • 1. Re: sqlplus with utf8 (command line)
          Gaz in Oz

          Well, as per I'm describing, the results looks good on :

          - Toad for Oracle 11

          - PL/SQL developer 11

          - SQL developer

          Please share you "Preferences" and show a screenshot of your sqldev setup, encoding, font, etc showing the inserts and select "looks good".

          sqldev for me shows some chars ok, but for japanese, chinese, not "looks good".

          What languages do you have installed in windows?

          what is your locale set to?

           

          A thought is to use powershell setup to display utf-8, and NLS_LANG set to ".UTF8", to start sqlplus

          • 2. Re: sqlplus with utf8 (command line)
            top.gun

            I know there is a perception out there that you need to set the NLS_LANG to the database characterset - but this is not true!

            NLS_LANG needs to be set to the characterset of the client (ie your workstation).

            See the following FAQ.

            nls_lang faq

            • 3. Re: sqlplus with utf8 (command line)
              Sergiusz Wolicki-Oracle

              Do not fight with SQL*Plus in Command Prompt to get multilingual support. BTW, what do you need it for? Command Prompt is an administrative environment with limited internationalization. It does not support the full set of Windows fonts and the fonts available do not cover all languages in your example. There is no complex script rendering for Command Prompt, which means that complex scripts, such as Arabic, Hebrew, Devanagari, or Thai will not work. Use a GUI tool instead.

               

              If you need to run some multilingual scripts via Command Prompt, prepare the scripts in Notepad or Notepad++ and save as UTF-8. Run the script in SQL*Plus after setting NLS_LANG to .AL32UTF8, spooling the output to a spool file. View the spool file in Notepad or Notepad++ making sure it is opened as UTF-8.

               

              Thanks,
              Sergiusz

              • 4. Re: sqlplus with utf8 (command line)
                chijar

                Thanks to all of you for your responses guys.