Translating and more with Google API's

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.