8 Replies Latest reply: Jun 18, 2002 11:59 AM by 292702 RSS

    How to setup sql*plus to see retrieve and see UNICODE characters from a UTF-8 DB ?

    266509
      Hi,

      How can I see UNICODE via SQL*PLus ?
      I search for a tool to work against oracle and which makes it
      possible to insert and retrieve UNICODE data.

      SqlPlus does not work and I can setup and UNICODE font.
      Any ideas how to use SqlPlus with UNICODE or any other tool
      which can handle this problems more properly.

      Thanks!
      brg robert
        • 1. re:How to setup sql*plus to see retrieve and see UNICODE characters from a UTF-8 DB ?
          3047
          What version of SQL*Plus do you have and what platform are you
          using?

          -- CJ
          • 2. re:How to setup sql*plus to see retrieve and see UNICODE characters from a UTF-8 DB ?
            266509
            It's SQL*Plus 8.1.7 under NT or Win2000.
            • 3. re:How to setup sql*plus to see retrieve and see UNICODE characters from a UTF-8 DB ?
              3047

              8.1.7 and Windows?? I don't recall off the top of my head. There is a lot of unicode information in the Globalization Manual for Oracle 9iR2 (see http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server.920/a96529/ch5.htm#1019808). Also there is a globalization discussion forum at http://forums.oracle.com/forums/forum.jsp?id=452569 that may help you while I try to find out.

              -- CJ
              • 4. re:How to setup sql*plus to see retrieve and see UNICODE characters from a UTF-8 DB ?
                3047

                My understanding is that the Oracle Globalization team suggests viewing Unicode data in 8.1.7 using command line SQL*Plus to generate HTML. The SQL*Plus command SET MARKUP HTML ON makes SQL*Plus generate HTML. Set NLS_LANG to <language>_<territory>.UTF8 beforehand to get the correct character mapping. Use Internet Explorer with an encoding of UTF-8 to display the HTML.

                The web based iSQL*Plus interface available with Oracle 9i makes this easier. You would need to set NLS_LANG to <language>_<territory>.UTF8 before you start the HTTP listener.

                The 9i Globalization documentation shows that recent versions of Oracle handle Unicode overall better than 8.1.7.

                -- CJ
                • 5. re:How to setup sql*plus to see retrieve and see UNICODE characters from a UTF-8 DB ?
                  266509
                  Thanks for your info.
                  The only problem is that I also want to insert data via SQL*Plus and there the HTML feature
                  does not help, as this only solves the viewing problem.
                  Any idea about how to get the easiest data into Oracle 8 in UNICODE ?
                  brg robert
                  • 6. re:How to setup sql*plus to see retrieve and see UNICODE characters from a UTF-8 DB ?
                    3047
                    The way I understand it is that tools like Notepad will
                    let you type in Unicode characters in SQL script files. These files can then be run in command-line SQL*Plus.

                    -- CJ
                    • 7. re:How to setup sql*plus to see retrieve and see UNICODE characters from a UTF-8 DB ?
                      273273
                      These are the following steps to perform to enable the client SQL*Plus (command line and GUI) and SQL*Worksheet to display the euro currency symbol correctly for example...

                      1) Search from the top of the registry (regedit) for ALL occurrences of NLS_LANG and make the value AMERICAN_AMERICA.WE8MSWIN1252.

                      2) Go to the following registry entry HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage\ACP
                      and change the active code page (ACP) to 1252. If there is an OEMACP entry there change that also to 1252.

                      3) Reboot the PC

                      To test .. go to DOS and enter at the prompt..

                      E:\>chcp

                      Active code page: 1252

                      If the above message IS NOT 1252 you must have missed editting the registry entry
                      explained above. However you can change it for this session by entering

                      E:\>chcp 1252

                      - < Set font to Lucida Console (in Properties, Font tab) >

                      To get the UNICODE characters you want insert into the database do the following

                      E:\>charmap

                      You will see a display of different characters.
                      If you click on advanced view at the lower right corner a search screen comes up. Enter UNICODE for the character set, ALL for group by and EURO for the search for and the euro currency symbol will come up. This is one way how the user can enter this character into the app. You can copy the character in this function as you can see and then you can paste it elsewhere. Copy whatever character you want to test with. My test was with the euro curreny symbol.

                      E:\>sqlplus.exe <user>/<pwd>

                      SQL*Plus: Release 8.1.7.0.0 - Production on Fri Jul 13 13:22:19 2001
                      (c) Copyright 1999 Oracle Corporation. All rights reserved.
                      Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
                      With the Partitioning option JServer Release 8.1.7.0.0 - Production

                      SQL> create table t (a char(3));

                      Table created.

                      SQL>insert into t values ('');

                      1 row inserted. SQL>

                      commit;

                      SQL> select * from t;

                      A
                      ---


                      If the all the registry edits were performed correctly this will work with
                      SQL*Plus GUI and SQL*Worksheet.

                      Check these notes# 132453.1, 152260.1 & 158577.1 out on metalink also for further details.
                      • 8. re:How to setup sql*plus to see retrieve and see UNICODE characters from a UTF-8 DB ?
                        292702
                        Hi Ralph .. I did what you said .. my active code page is now 1252 on NT4 running SQL*Plus release 8.1.7.0.0 .. but it does not have a unicode font so the multibyte chars are displayed as ? .. do you know of a way to make SQL*Plus use a unicode font? like ms gothic?