You need to execute below PL/SQL block in case you are using Oracle 11g. Because in 11g, 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.drop_acl
(acl=> 'utl_http.xml');
end;
/
 
PL/SQL procedure successfully completed.


 
begin
dbms_network_acl_admin.create_acl
  (acl        => 'utl_http.xml',
   description => 'HTTP Access',
   principal   => 'SCOTT',
   is_grant    => true,
   privilege   => 'connect');
dbms_network_acl_admin.assign_acl
  (acl       => 'utl_http.xml',
   host      => 'translate.google.com',
   lower_port => 80,
   upper_port => 80);
commit;
end;
/
 
PL/SQL procedure successfully completed.
 


You need to execute the above to PL/SQL block if you will be using Oracle 11g.
 
 



set define off
create or replace function translation
 (p_words in clob,  -- words to be translated
  p_to  in varchar2 default 'de', -- language to translate to (German)
  p_from in varchar2 default 'en') -- language to translate from (English)
                                        -- ar = Arabic
                                        -- en = English
                                        -- es = Spanish
                                        -- fr = French
return clob
as
     l_res        clob;
     l_words      clob;
   begin
     l_res := httpuritype
                 ('http://translate.google.com/?hl=' ||
                  p_from || '&layout=1&eotf=1&sl=' || p_from ||
                  '&tl=' || p_to || '&text=' ||
                  utl_url.escape (p_words) || '#').getclob();
     l_res := substr (l_res, instr (l_res, '<span title="' || p_words || '"'));
     l_res := substr (l_res, 1, instr (l_res, '</span>') + 6);
     l_words := XmlType (l_res).extract ('/span/text()').getStringVal();
     return l_words;
end translation;
/
 
Function created.
 



show errors
No errors.
 




SQL> select translation ('cats and dogs') from dual;
 
TRANSLATION('CATSANDDOGS')
----------------------------------------
 
Katzen und Hunde
 
SQL> select translation ('I Love You','de','en') from dual;
 
TRANSLATION('ILOVEYOU','DE','EN')
--------------------------------------------
 
Ich liebe dich