This discussion is archived
7 Replies Latest reply: Aug 22, 2012 6:45 PM by 800242 RSS

Oracle Databse to support chinese fonts

953817 Newbie
Currently Being Moderated
The database is created with Character set = UTF8 and the National Character Set = AL16UTF16 and got the result for other languages (latin,german,french etc) but still Chinese language was not supported.


NLS_CHARACTERSET     AL32UTF8
NLS_NCHAR_CHARACTERSET     AL16UTF16

I was getting the output as given below-

SQL> select translator ('table and chair','zh-CN','en') English_to_chinese from dual ;

ENGLISH_TO_chinese
--------------------------------------------------------------------------------
┐┐┐┐┐

And the function I was using was

/* Formatted on 16/08/2012 21:55:39 (QP5 v5.215.12089.38647) */
CREATE OR REPLACE FUNCTION translator (p_words IN CLOB, -- words to be translated
p_to IN VARCHAR2, -- language to translate to
p_from IN VARCHAR2) -- language to translate from
RETURN CLOB
AS
l_uritype CLOB;
l_result CLOB;
BEGIN
l_uritype :=
httpuritype (
'http://translate.google.com/?hl='
|| p_from
|| '&layout=1&eotf=1&sl='
|| p_from
|| '&tl='
|| p_to
|| '&text='
|| UTL_URL.escape (p_words)
|| '#').getclob ();
l_result :=
REGEXP_SUBSTR (
l_uritype,
'<span id=result_box class="short_text"><span[^>]*>(.*)</span></span>',
1,
1,
'i',
1);
RETURN l_result;
END translator;
/

Please advise. Upon which I will get chinese
  • 1. Re: Oracle Databse to support chinese fonts
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    The Oracle database does not "support" fonts. It is a server system - it does not render data. It does not directly interact with an end-user. The client software does that. It renders data it receives from the database. It interacts with the end-user. It needs to support whatever fonts to render the data received from the database, correctly.

    As for the URL and approach you are using - it is flawed and a violation of Google's terms of use. The translator service, when used via client software and not an end-user using a web browser client, needs to be done via Google's API.

    Screen scraping the interactive HTML page for translation is a silly approach - made all the more complex by Google changing the internal HTML for the resulting page that will break whatever parsing rules used to scrape the results from the HTML response page.

    The Google API URL is http://ajax.googleapis.com/ajax/services/language/translate, and as I understand, you need a license to use it - i.e. make an authentication call prior to calling this URL directly.

     
    PS. https://developers.google.com/translate/ says "+Google Translate API is available as a paid service+". So you're out of luck trying to use the service for free.

    Edited by: Billy Verreynne on Aug 21, 2012 8:12 AM
  • 2. Re: Oracle Databse to support chinese fonts
    953817 Newbie
    Currently Being Moderated
    Fine. Thanks for the reply.
    But is there any way that i can insert chinese fonts into my database and view the same. Its not supported with thew abv mentioned nls setting.
  • 3. Re: Oracle Databse to support chinese fonts
    sybrand_b Guru
    Currently Being Moderated
    I don't think this is correct.
    For the server data to display correctly on the client, two things need to happen

    - the client characterset or code page needs to support Chinese
    - the client NLS_LANG environment variable (Unix) or registry string value (Windows) consisting of <language>_<territory>.<characterset> needs to be set correctly,
    the last part is the Oracle designation for the OS characterset.
    So likely "American_America.AL32UTF8" should work.

    So far you have not indicated this is set up correctly or set up at all in your environment.

    ---------
    Sybrand Bakker
    Senior Oracle DBA
  • 4. Re: Oracle Databse to support chinese fonts
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    950814 wrote:

    But is there any way that i can insert chinese fonts into my database and view the same. Its not supported with thew abv mentioned nls setting.
    Fonts are not inserted into the database. Text data is inserted as characters. Character data is defined by a specific character set.

    So as Sybrand commented - the client need to use the appropriate character set, and the database need to support that character set.

    See Oracle® Database Globalization Support Guide for details.
  • 5. Re: Oracle Databse to support chinese fonts
    953817 Newbie
    Currently Being Moderated
    Sybrand,

    Thanks for the information.

    But I am not getting you. Can you please elobrate on the below mentioned things, Since I am new to this DBA work-

    the client characterset or code page needs to support Chinese
    - the client NLS_LANG environment variable (Unix) or registry string value (Windows) consisting of <language>_<territory>.<characterset> needs to be set correctly,
    the last part is the Oracle designation for the OS characterset.
    So likely "American_America.AL32UTF8" should work.



    The database resides in windows.
    I had installed the database with American_America.AL32UTF8.
    And for the simplefied chinese characters, should we need to change the NLS setting?..

    And the client chacterset or code page means - this refers to sqlplus or toad ??? will I be able to view chinese characters in sqlplus ? or should i need to change any settings... pls advice.

    Thanks
  • 6. Re: Oracle Databse to support chinese fonts
    Sergiusz Wolicki (Oracle) Expert
    Currently Being Moderated
    You need to get four things correctly:

    1. The database character set must support Chinese characters -- not fonts. Fonts are OS or application objects containing instructions on how to draw characters on the screen. You can get a font into a database as a binary file but this is not what you want. You want to store Chinese characters (text) in a character column.

    Your database character set (NLS_CHARACTERSET) is AL32UTF8 and it supports Chinese, so this element is already fine.

    2. You need to get your Chinese data into the database in the database character set, i.e. AL32UTF8, which is Unicode UTF-8. How to do this depends on the particular method used to load the data. As the method you tried seemed to be invalid (both form the legal perspective and from the technical perspective, as the page you try to load is dynamic HTML), we will not help you with fixing it. Please, come up with some other source of Chinese data and I will tell you how to load it into the database.

    3. After the data is stored in the database, you need to get it to the client application in a way that preserves the Chinese characters. This means, the client software needs to talk to the database in a character set supporting Chinese. The actual setup required depends strongly on the particular client. For example, JDBC-based applications do not need any setup as they will always receive data in Unicode (in UTF-16 encoding). Unicode supports all languages you need. But SQL*Plus in a Command Prompt window needs the NLS_LANG environment variable or Registry setting to be set to .ZHS16GBK to receive Chinese data in a character set that SQL*Plus understands on Windows and which supports Chinese. SQL*Plus on Windows does not like Unicode when displaying data.

    4. The client application and the client operating system must be configured properly to display Chinese. This includes availability and use of Chinese-capable fonts. Modern Windows versions do not usually have problems with displaying Chinese but some specific steps must be required depending on the particular client application.


    There is no easy, universal solution to your problem. You need to concentrate on a given software configuration to get things right.


    -- Sergiusz
  • 7. Re: Oracle Databse to support chinese fonts
    800242 Newbie
    Currently Being Moderated
    The best choice is SQL Developer, it is implemented by Java and it takes care of everything for you.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points