Home » Oracle » How to use google translate URL in Oracle plsql

How to use google translate URL in Oracle plsql

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

google translate URL in Oracle plsql

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.

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.
See also  Oracle Real Application Clusters( Oracle RAC)

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

See also  How to check Tablespace in Oracle -Size,Free space,datafiles ,growth

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.

1 thought on “How to use google translate URL in Oracle plsql”

  1. Hi: I have create wallet and add the certificate. But how do I run the sql ? Do have a simpler sql to test conversion from English to Spanish ?

    Thank You

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top