Home » SQL & PL/SQL » SQL & PL/SQL » Creating Oracle database to support chinese fonts (Oracle 11g)
Creating Oracle database to support chinese fonts [message #564049] Tue, 21 August 2012 01:49 Go to next message
abirami.thirunavukkarasu
Messages: 28
Registered: August 2012
Location: Bangalore
Junior Member
mentioned 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 is given below

/* 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 (
'...://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;
/

For this function - I had already registered acl.
Re: Creating Oracle database to support chinese fonts [message #564054 is a reply to message #564049] Tue, 21 August 2012 02:51 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but still Chinese language was not supported.


Yes, it is.

Quote:
I was getting the output as given below-


This is your SQL*Plus that does not support chinese or your NLS_LANG is not correctly set.

Please read How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

[Edit: add NLS_LANG remark]

[Updated on: Tue, 21 August 2012 03:04]

Report message to a moderator

Re: Creating Oracle database to support chinese fonts [message #564145 is a reply to message #564054] Tue, 21 August 2012 11:36 Go to previous messageGo to next message
abirami.thirunavukkarasu
Messages: 28
Registered: August 2012
Location: Bangalore
Junior Member
Thanks for the information michel.

But how to view the output in chinese language.Can u please let me know the information.
And my version details are given below-

SQL> select *  from v$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Re: Creating Oracle database to support chinese fonts [message #564147 is a reply to message #564145] Tue, 21 August 2012 11:45 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just execute the same thing but spool it in a file, then open the file with a program that supports chinese character (I think wordpad does but I have no example).
If you have no such program, then execute "SET MARKUP HTML ON SPOOL ON", "SPOOL myfile.html", reexecute the query and then "SPOOL OFF" and double-click on the spooled file.

Tell us the result.

Regards
Michel
Re: Creating Oracle database to support chinese fonts [message #564150 is a reply to message #564147] Tue, 21 August 2012 12:43 Go to previous messageGo to next message
abirami.thirunavukkarasu
Messages: 28
Registered: August 2012
Location: Bangalore
Junior Member
Hi,

Thanks for the immediate response.

But I had given spool via html. Unfortunately I am not getting the output. I am getting the result as ?????.
will there be any problem with the databse creation.

Here are my settings details.


SQL> SELECT * from NLS_DATABASE_PARAMETERS WHERE parameter IN ( 'NLS_LANGUAGE',
'NLS_TERRITORY', 'NLS_CHARACTERSET');

PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------

NLS_LANGUAGE
AMERICAN

NLS_TERRITORY
AMERICA

NLS_CHARACTERSET
ZHS16GBK

Re: Creating Oracle database to support chinese fonts [message #564152 is a reply to message #564150] Tue, 21 August 2012 13:08 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But I had given spool via html. Unfortunately I am not getting the output. I am getting the result as ?????.

What is your client NLS_LANG parameter?

Regards
Michel
Re: Creating Oracle database to support chinese fonts [message #564153 is a reply to message #564152] Tue, 21 August 2012 13:20 Go to previous messageGo to next message
abirami.thirunavukkarasu
Messages: 28
Registered: August 2012
Location: Bangalore
Junior Member
please find the nls_lang details-

SQL> show parameter nls_lang

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_language                         string      SIMPLIFIED CHINESE



thanks in advance
Re: Creating Oracle database to support chinese fonts [message #564155 is a reply to message #564153] Tue, 21 August 2012 13:34 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not nls_language, NLS_LANG, or NLS_CHARACTERSET if you prefer.

Regards
Michel

[Updated on: Tue, 21 August 2012 13:37]

Report message to a moderator

Re: Creating Oracle database to support chinese fonts [message #564157 is a reply to message #564155] Tue, 21 August 2012 13:39 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
 select distinct CLIENT_CHARSET from v$SESSION_CONNECT_INFO where CLIENT_CHARSET != 'Unknown';

Regards
Michel
Re: Creating Oracle database to support chinese fonts [message #564161 is a reply to message #564157] Tue, 21 August 2012 13:51 Go to previous messageGo to next message
abirami.thirunavukkarasu
Messages: 28
Registered: August 2012
Location: Bangalore
Junior Member
SQL> select distinct CLIENT_CHARSET from v$SESSION_CONNECT_INFO where CLIENT_CHA
RSET != 'Unknown';

CLIENT_CHARSET
----------------------------------------
WE8MSWIN1252
Re: Creating Oracle database to support chinese fonts [message #564162 is a reply to message #564161] Tue, 21 August 2012 13:52 Go to previous messageGo to next message
abirami.thirunavukkarasu
Messages: 28
Registered: August 2012
Location: Bangalore
Junior Member
Had given the result above.Pls validate.
Re: Creating Oracle database to support chinese fonts [message #564163 is a reply to message #564162] Tue, 21 August 2012 13:55 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No you have not or post a link to your post you show it.

Regards
Michel
Re: Creating Oracle database to support chinese fonts [message #564165 is a reply to message #564163] Tue, 21 August 2012 13:58 Go to previous messageGo to next message
abirami.thirunavukkarasu
Messages: 28
Registered: August 2012
Location: Bangalore
Junior Member
Please see the result here-

SQL> select distinct CLIENT_CHARSET from v$SESSION_CONNECT_INFO where CLIENT_CHA
RSET != 'Unknown';

CLIENT_CHARSET
----------------------------------------
WE8MSWIN1252




Please let me know, if you need any other information thanks

[Updated on: Tue, 21 August 2012 14:00]

Report message to a moderator

Re: Creating Oracle database to support chinese fonts [message #564167 is a reply to message #564165] Tue, 21 August 2012 14:18 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So your client character set does not support chinese characters and you got the expected result.
Try the following and post the result:
create table t (c varchar2(10 char));
begin
  insert into t 
  select translator ('table and chair','zh-CN','en') English_to_chinese from dual ;
end;
/
select dump(c) from t;

Regards
Michel

[Updated on: Tue, 21 August 2012 14:30]

Report message to a moderator

Re: Creating Oracle database to support chinese fonts [message #564175 is a reply to message #564167] Tue, 21 August 2012 21:06 Go to previous messageGo to next message
abirami.thirunavukkarasu
Messages: 28
Registered: August 2012
Location: Bangalore
Junior Member
Thanks for the continous support-

Please find the result of the code below-

SQL> begin
  2    insert into t
  3    select translator ('table and chair','zh-CN','en') English_to_chinese fro
m dual ;
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> select dump(c) from t;

DUMP(C)
--------------------------------------------------------------------------------

Typ=1 Len=10: 215,192,215,211,186,205,210,206,215,211
Re: Creating Oracle database to support chinese fonts [message #564185 is a reply to message #564175] Tue, 21 August 2012 23:46 Go to previous message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So the data are correct, the problem is just a get and display one.

Regards
Michel
Previous Topic: merge on view
Next Topic: Writable MVIEW
Goto Forum:
  


Current Time: Mon Oct 20 22:20:51 CDT 2014

Total time taken to generate the page: 0.09610 seconds