How to use google translate URL in Oracle plsql

We all know Google Translate which can help translate from one language to another.

We will explore the option to use Google functionality in Oracle plsql in the Oracle database to get the translation done.

1. First, we need to create a Network access list which is required from Oracle 11g

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
acl => 'google_translatorapi.xml',
description => 'Google Translator Access',
principal => 'SCOTT',
is_grant => true,
privilege => 'connect'
);
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => 'google_translatorapi.xml',
principal => 'SCOTT',
is_grant => true,
privilege => 'resolve'
);
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => 'google_translator.xml',
host => 'translate.google.com'
);
COMMIT;
END;
/

2) Next google   URL https://translate.google.com/ is https, so we need to create oracle wallet to store public certificate to access it

Download google public certificate using below procedure

a) The easiest way to do this is using a browser. The example below uses the IE browser.

Using the browser, go to the URL you are attempting to access from PL/SQL. In this case  https://translate.google.com/ Click the lock icon in the URL bar to display the website identification  and click on the “view certificate” tab.

On the resulting wizard, do the following.

So this will not work like this. we need to set the oracle wallet, before calling this procedure

SQL> exec UTL_HTTP.SET_WALLET ('file:/home/oracle/','ora1_test');
PL/SQL procedure successfully completed.

SQL> set define off
SQL> column spanish format A20
SQL> with t as (
2 select 'fish' txt from dual union all
3 select 'dog' txt from dual union all
4 select 'cat' txt from dual
)
5 6 select txt english,
7 regexp_substr(tr,'<span id=result_box class="short_text"><span[^>]>(.)</span></span>',1,1,'i',1) spanish
8 from (
9 select txt,
10 httpuritype('http://translate.google.com/?hl=en&layout=1&eotf=1&sl=en&tl=es&text='||utl_url.escape(txt)||'#').getclob() tr
11 from t
12 )
13 /

ENGL SPANISH
—— ————
fish peces
dog perro
cat cat

Hope you like these steps on How to use google translate URL in Oracle plsql

Related Articles
Virtual Index in Oracle : What is Virtual Index in Oracle? Uses,limitation ,advantage and how to use to check explain plan in Oracle database, Hidden parameter _USE_NOSEGMENT_INDEXES
Oracle Create table : Tables are the basic unit of data storage in an Oracle Database.we covers how to use Oracle create table command to create table with foreign key /primary key
Oracle PLSQL Tables : Check out this post for the detail description on PLSQL Tables.How to manipulate it and work on it in Oracle PLSQL block and benefits
delete duplicate rows in oracle : Tips and ways on How to delete duplicate rows from oracle table . Various options are given to delete the duplicate rows without any issues
approx_count_distinct
Oracle Date Functions : Check out this post for oracle date functions, oracle date difference in years,oracle date difference in days, oracle date difference in months.