How to use google translate URL in Oracle plsql



Last updated on November 14th, 2017 at 04:46 am

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

google translate URL in Oracle plsql

We will explore option to use google functionality in oracle plsql in oracle database to get translation done.

  1. First we need to create 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.

Click the “Certificate information” link and click the “Certification Path” tab on the resulting dialog.

google translate URL in Oracle plsql

For the root node in the “Certification path”, highlight the node and click the “View Certificate” button. On the resulting dialog, click the “Details” tab and click the “Copy to File…” button to save the certificate information.

On the resulting wizard, do the following.

  • Click the “Next” button on the welcome screen.
  • Select the “Base-64 encoded X.509 (.CER)” option and click the “Next” button. Other formats work, but I’ve found this to be the most consistent.
  • Enter suitable file name and click the “Next” button.
  • Click the “Finish” button.

Important Note: On IE , we may see Copy to file as disabled. If it is so, please start IE as administrator and you will see that as enable

3)  Create Oracle wallet and import this certificate into it

$orapki wallet create -wallet /home/oracle -pwd ora1_test

$ orapki wallet add -wallet /home/oracle -trusted_cert -cert /home/oracle/g1.cer -pwd ora1_test
Oracle PKI Tool : Version 11.2.0.4.0 – Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved

4) Next we need to the good translate URL through sql or plsql to translate text

$ sqlplus scott/toger

SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 15 15:14:32 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.

 

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> set define off
SQL> column spanish format A20
SQL> with t as (
2 select ‘fish’ txt from dual union all
select ‘dog’ txt from dual union all 3
4 select ‘cat’ txt from dual
5 )
select txt english,
6 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 /
ERROR:
ORA-29273: HTTP request failed
ORA-06512: at “SYS.UTL_HTTP”, line 1817
ORA-29024: Certificate validation failure
ORA-06512: at “SYS.HTTPURITYPE”, line 34

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

How to use Virtual Index in Oracle

Oracle Create table Syntax,Tip and Examples

Oracle PLSQL Tables

How to delete duplicate rows from a table

approx_count_distinct : Oracle Database 12c

How to work with date in Oracle sql


Leave a Reply