Thursday, December 30, 2010

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 => ''
22 );
23 dbms_network_acl_admin.assign_acl
24 ( acl => cn_acl
25 , host => ''
26 );
27 end;
28 /

PL/SQL procedure successfully completed.

For the translation API, I assigned and for another example later on I also assigned 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 ( '' ||
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(',5.0514789&sensor=false').getclob()
2 from dual
3 /

<?xml version="1.0" encoding="UTF-8"?>
<formatted_address>Rijnzathe 6, 3454 De
Meern, The Netherlands</formatted_addres
<long_name>Vleuten-De Meern</long_name
<short_name>Vleuten-De Meern</short_name>
[removed lots of lines]

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 ( '' ||
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 /

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.


  1. Hi All
    just to let you know

    "Important: Google Translate API v1 was officially deprecated on May 26, 2011; it will be shut off completely on December 1, 2011. For text translations, you can use the Google Translate API v2, which is now available as a paid service. For website translations, we encourage you to use the Google Website Translator gadget."

    hope it will help somebody there.


  2. Google Translate API v2 - easy to use
    1. get the ssl cerficate of loaded into your Oracle Wallet
    2. update the script bellow with the correct values
    l_url->key (obtained from -> Translate API Project)
    l_wallet_path (see step 1)
    wallet_password (see step 1)
    3. install PL/JSON from
    4. run the script below
    5. test it (select bts_translate('Welcome to Google Translate!!!','en','ro') from dual;)

    set define off;

    drop function bts_translate;

    create function bts_translate
    ( p_text in varchar2
    , p_source_language_code in varchar2
    , p_target_language_code in varchar2
    ) return varchar2
    l_output long;
    response json;
    l_url varchar2(4000) default '';
    l_wallet_path varchar2(255) default 'file:C:\WALLETS';
    l_url:=l_url||utl_url.escape(p_text) ||'&source=' || p_source_language_code || '&target=' || p_target_language_code;
    l_output := utl_http.request
    ( url => l_url,
    proxy => '',
    wallet_path => l_wallet_path,
    wallet_password => 'my_wallet_password'


    if length(json_ext.get_string(response, 'error.message'))>0 then
    return 'GTError: '||json_ext.get_string(response, 'error.message');
    return json_ext.get_string(response, 'data.translations[1].translatedText');
    end if;

    return 'GTError: EROARE NECUNOCUTA';

    end bts_translate;

    --select bts_translate('Welcome to Google Translate!!!','en','ro') from dual

  3. Hello Pant,

    How do I get the SSL for googleapis. I am trying to do a translation from Russia to english for one of our projects. Can you please provide me more inputs.