Google has a lot of API's that you can use in SQL and PL/SQL as well. A couple of months ago I saw a very nice example on OTN here. It was an example of how to use Google's Translate API. Here is how to do it yourself.
First of all, since version 11, you need to specify fine grained access to external network services, which is described here in the documentation. The package DBMS_NETWORK_ACL_ADMIN should be used to create an access control list saying that access to the googleapis is allowed:
rwijk@ORA11202> declare
2 cn_acl constant varchar2(100) := 'googleapis.xml';
3 begin
4 dbms_network_acl_admin.create_acl
5 ( acl => cn_acl
6 , description => 'Google Translate API'
7 , principal => 'RWIJK'
8 , is_grant => true
9 , privilege => 'connect'
10 , start_date => null
11 , end_date => null
12 );
13 dbms_network_acl_admin.add_privilege
14 ( acl => cn_acl
15 , principal => 'RWIJK'
16 , is_grant => true
17 , privilege => 'resolve'
18 );
19 dbms_network_acl_admin.assign_acl
20 ( acl => cn_acl
21 , host => 'ajax.googleapis.com'
22 );
23 dbms_network_acl_admin.assign_acl
24 ( acl => cn_acl
25 , host => 'maps.googleapis.com'
26 );
27 end;
28 /
PL/SQL procedure successfully completed.
For the translation API, I assigned ajax.googleapis.com and for another example later on I also assigned maps.googleapis.com. Now I can use the API and create my own translate function:
rwijk@ORA11202> create function my_translate
2 ( p_text in varchar2
3 , p_source_language_code in varchar2
4 , p_target_language_code in varchar2
5 ) return varchar2
6 is
7 begin
8 return regexp_substr
9 ( httpuritype
10 ( 'http://ajax.googleapis.com/ajax/services/language/translate?v=1.0&q=' ||
11 utl_url.escape(p_text) ||
12 '&langpair=' || p_source_language_code || '|' || p_target_language_code
13 ).getclob()
14 , '[^"]+'
15 , 1
16 , 6
17 );
18 end my_translate;
19 /
Function created.
And I can execute it to translate an English sentence to Dutch:
rwijk@ORA11202> exec dbms_output.put_line(my_translate('How do I translate English sentences?','en','nl'));
Hoe vertaal ik Engels zinnen?
PL/SQL procedure successfully completed.Note that the translation is not perfect as the Dutch sentence should read "Hoe vertaal ik Engelse zinnen?". And to demonstrate that other languages work as well:
rwijk@ORA11202> exec dbms_output.put_line(my_translate('How do I translate English sentences?','en','fr'));
Comment puis-je traduire des phrases en anglais?
PL/SQL procedure successfully completed.
rwijk@ORA11202> exec dbms_output.put_line(my_translate('How do I translate English sentences?','en','de'));
Wie übersetze ich englische Sätze?
PL/SQL procedure successfully completed.
rwijk@ORA11202> exec dbms_output.put_line(my_translate('How do I translate English sentences?','en','es'));
¿Cómo traducir frases Inglés?
PL/SQL procedure successfully completed.Another example is to get geographical data from Google, using the Google Geocoding API. Here is an example generating XML output with the old address of Oracle Netherlands:
rwijk@ORA11202> select httpuritype('http://maps.googleapis.com/maps/api/geocode/xml?latlng=52.0715554,5.0514789&sensor=false').getclob()
2 from dual
3 /
HTTPURITYPE('HTTP://MAPS.GOOGLEAPIS.COM/MAPS/API/GEOCODE/XML?LATLNG=52.0715554,5
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<GeocodeResponse>
<status>OK</status>
<result>
<type>street_address</type>
<formatted_address>Rijnzathe 6, 3454 De
Meern, The Netherlands</formatted_addres
s>
<address_component>
<long_name>6</long_name>
<short_name>6</short_name>
<type>street_number</type>
</address_component>
<address_component>
<long_name>Rijnzathe</long_name>
<short_name>Rijnzathe</short_name>
<type>route</type>
</address_component>
<address_component>
<long_name>Vleuten-De Meern</long_name
>
<short_name>Vleuten-De Meern</short_name>
<type>sublocality</type>
<type>political</type>
</address_component>
<address_component>
<long_name>Oudenrijn</long_name>
<short_name>Oudenrijn</short_name>
<type>locality</type>
<type>political</type>
</address_component>
...
[removed lots of lines]
...
</geometry>
</result>
</GeocodeResponse>
1 row selected. Google has a lot more API's available. You can find them here.
I'd like to end this year of blogging with this query:
rwijk@ORA11202> select regexp_substr
2 ( httpuritype
3 ( 'http://ajax.googleapis.com/ajax/services/language/translate?v=1.0&q=' ||
4 utl_url.escape('Ik wens alle lezers van deze blog een gelukkig nieuwjaar') ||
5 '&langpair=nl|' || column_value
6 ).getclob()
7 , '[^"]+'
8 , 1
9 , 6
10 ) wish
11 from table(sys.dbms_debug_vc2coll('en','fr','de','es','it','ru','zh','in','pl','sv','fi','ar','el'))
12 /
WISH
---------------------------------------------------------------------------------------
I wish all readers of this blog a happy new year
Je souhaite à tous les lecteurs de ce blog une heureuse nouvelle année
Ich wünsche allen Lesern dieses Blogs ein frohes neues Jahr
Les deseo a todos los lectores de este blog un feliz año nuevo
Auguro a tutti i lettori di questo blog un felice anno nuovo
Я желаю всем читателям этого блога с новым годом
我希望这个博客的所有读者新年快乐
Saya berharap semua pembaca blog ini tahun baru bahagia
Życzę wszystkim czytelnikom tego bloga, szczęśliwego nowego roku
Jag önskar alla läsare av denna blogg ett gott nytt år
Toivotan kaikille lukijoille tämän blogin onnellista uutta vuotta
أتمنى لجميع قراء هذا بلوق سنة جديدة سعيدة
Εύχομαι σε όλους τους αναγνώστες αυτού του blog ένα ευτυχισμένο νέο έτος
13 rows selected.
