Home » SQL & PL/SQL » SQL & PL/SQL » Using internet translators from Oracle (11g)
| Using internet translators from Oracle [message #503540] |
Sun, 17 April 2011 18:34  |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Not sure if I already posted it here, but after reading post "Code to Convert English Names to Arabic" I decided to post this (even though it is not a solution for proper name translation). I will show how to use www.translate.google.com to translate from english to spanish. I am using 11g, where security has changed and we need to create and assign ACLs in order to access web sites. Below code creates acl:
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
acl => 'network_services.xml',
description => 'SITE ACL',
principal => 'SCOTT',
is_grant => true,
privilege => 'connect'
);
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => 'network_services.xml',
host => '*'
);
COMMIT;
END;
/
Now we can use http://translate.google.com. If we explore the site we will figure out the interface (well, one of them). It is http://translate.google.com/?hl=display-language-code&layout=1&eotf=1&sl=translate-from-language-code&tl=translate-to -language-code&text=word-to-be-translated#. Code en is english and code es is spanish. So all we need to do is:
set define off
column spanish format A20
with t as (
select 'fish' txt from dual union all
select 'dog' txt from dual union all
select 'cat' txt from dual
)
select txt english,
regexp_substr(tr,'<span id=result_box class="short_text"><span[^>]*>(.*)</span></span>',1,1,'i',1) spanish
from (
select txt,
httpuritype('http://translate.google.com/?hl=en&layout=1&eotf=1&sl=en&tl=es&text='||utl_url.escape(txt)||'#').getclob() tr
from t
)
/
ENGL SPANISH
---- ---------
fish peces
dog perro
cat gato
SQL>
Code fr is french:
column french format a20
with t as (
select 'fish' txt from dual union all
select 'dog' txt from dual union all
select 'cat' txt from dual
)
select txt english,
regexp_substr(tr,'<span id=result_box class="short_text"><span[^>]*>(.*)</span></span>',1,1,'i',1) french
from (
select txt,
httpuritype('http://translate.google.com/?hl=en&layout=1&eotf=1&sl=en&tl=fr&text='||utl_url.escape(txt)||'#').getclob() tr
from t
)
/
ENGL FRENCH
---- ---------
fish poissons
dog chien
cat cat
SQL>
SY.
P.S. code ar is arabic, but since I do not have arabic installed, all I get is obviously a bunch of ¿.
[Updated on: Sun, 17 April 2011 18:36] Report message to a moderator |
|
|
|
| Re: Using internet translators from Oracle [message #503641 is a reply to message #503540] |
Mon, 18 April 2011 09:58  |
 |
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Solomon,
Nice work! I previously tried to do something similar but failed. I got stuck at trying to figure out the part in bold below.
httpuritype ('http://translate.google.com/?hl=en&layout=1&eotf=1&sl=en&tl=es&text=' || utl_url.escape(txt) || '#').getclob()
I tried going to http://translate.google.com then doing a translation, then viewing the source code for the resulting page, but could not figure it out. I couldn't figure out what to use in the code above so that I could return something as a clob and view it, so that I could figure out how to parse it. Please let me know how you figured that out.
Now that I have what you figured out, I am able to finish what I was trying to do and have posted it below. Due to character set issues the Arabic is illegible, but should work for somebody who has the appropriate character set.
SYS@orcl_11gR2> begin
2 dbms_network_acl_admin.drop_acl
3 (acl => 'utl_http.xml');
4 end;
5 /
PL/SQL procedure successfully completed.
SYS@orcl_11gR2> begin
2 dbms_network_acl_admin.create_acl
3 (acl => 'utl_http.xml',
4 description => 'HTTP Access',
5 principal => 'SCOTT',
6 is_grant => true,
7 privilege => 'connect');
8 dbms_network_acl_admin.assign_acl
9 (acl => 'utl_http.xml',
10 host => 'translate.google.com',
11 lower_port => 80,
12 upper_port => 80);
13 commit;
14 end;
15 /
PL/SQL procedure successfully completed.
SYS@orcl_11gR2> connect scott/tiger
Connected.
SCOTT@orcl_11gR2> set define off
SCOTT@orcl_11gR2> create or replace function translation
2 (p_words in clob, -- words to be translated
3 p_to in varchar2 default 'ar', -- language to translate to
4 p_from in varchar2 default 'en') -- language to translate from
5 -- ar = Arabic
6 -- en = English
7 -- es = Spanish
8 -- fr = French
9 return clob
10 as
11 l_res clob;
12 l_words clob;
13 begin
14 l_res := httpuritype
15 ('http://translate.google.com/?hl=' ||
16 p_from || '&layout=1&eotf=1&sl=' || p_from ||
17 '&tl=' || p_to || '&text=' ||
18 utl_url.escape (p_words) || '#').getclob();
19 l_res := substr (l_res, instr (l_res, '<span title="' || p_words || '"'));
20 l_res := substr (l_res, 1, instr (l_res, '</span>') + 6);
21 l_words := XmlType (l_res).extract ('/span/text()').getStringVal();
22 return l_words;
23 end translation;
24 /
Function created.
SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> select translation ('cats and dogs', 'es') from dual
2 /
TRANSLATION('CATSANDDOGS','ES')
--------------------------------------------------------------------------------
gatos y perros
1 row selected.
SCOTT@orcl_11gR2> select translation ('cats and dogs', 'fr') from dual
2 /
TRANSLATION('CATSANDDOGS','FR')
--------------------------------------------------------------------------------
les chats et les chiens
1 row selected.
SCOTT@orcl_11gR2> select translation ('cats and dogs') from dual
2 /
TRANSLATION('CATSANDDOGS')
--------------------------------------------------------------------------------
¿¿¿¿¿ ¿¿¿¿¿¿¿
1 row selected.
SCOTT@orcl_11gR2>
|
|
|
|
| Re: Using internet translators from Oracle [message #503813 is a reply to message #503641] |
Tue, 19 April 2011 15:13 |
|
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Substituting Solomon's code that uses regexp_substr for the xml in the function seems to work better with other character sets. Although the result in other character sets displays on my system as numbers and ampersands, when copied and pasted onto this site, it displays in the proper character set.
SCOTT@orcl_11gR2> create or replace function translation
2 (p_words in clob, -- words to be translated
3 p_to in varchar2 default 'ar', -- language to translate to
4 p_from in varchar2 default 'en') -- language to translate from
5 -- ar = Arabic
6 -- en = English
7 -- es = Spanish
8 -- fr = French
9 return clob
10 as
11 l_res clob;
12 l_words clob;
13 begin
14 l_res := httpuritype
15 ('http://translate.google.com/?hl=' ||
16 p_from || '&layout=1&eotf=1&sl=' || p_from ||
17 '&tl=' || p_to || '&text=' ||
18 utl_url.escape (p_words) || '#').getclob();
19 l_words := regexp_substr
20 (l_res,
21 '<span id=result_box class="short_text"><span[^>]*>(.*)</span></span>',
22 1, 1, 'i', 1);
23 return l_words;
24 end translation;
25 /
Function created.
SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> select translation ('I love you.', 'HI', 'EN') from dual
2 /
TRANSLATION('ILOVEYOU.','HI','EN')
--------------------------------------------------------------------------------
मैं तुम्हें प्यार करता हूँ.
1 row selected.
SCOTT@orcl_11gR2>
|
|
|
|
Goto Forum:
Current Time: Sun Jun 01 11:23:28 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
