SCOTT@orcl_11gR2> create table english_arabic
  2    (english  varchar2 (1),
  3  	arabic	 nvarchar2 (4))
  4  /

Table created.

SCOTT@orcl_11gR2> insert into english_arabic values
  2    ('M', 'm')
  3  /

1 row created.

SCOTT@orcl_11gR2> insert into english_arabic values
  2    ('A', 'a')
  3  /

1 row created.

SCOTT@orcl_11gR2> insert into english_arabic values
  2    ('R', 'r')
  3  /

1 row created.

SCOTT@orcl_11gR2> insert into english_arabic values
  2    ('T', 't')
  3  /

1 row created.

SCOTT@orcl_11gR2> insert into english_arabic values
  2    ('I', 'i')
  3  /

1 row created.

SCOTT@orcl_11gR2> insert into english_arabic values
  2    ('N', 'n')
  3  /

1 row created.

SCOTT@orcl_11gR2> create or replace function english_to_arabic
  2    (p_english in varchar2)
  3    return nvarchar2
  4  as
  5    v_arabic      nvarchar2 (4);
  6    v_result      nvarchar2 (4000);
  7  begin
  8    for i in 1 .. length (p_english) loop
  9  	 select arabic
 10  	 into	v_arabic
 11  	 from	english_arabic
 12  	 where	english = substr (p_english, i, 1);
 13  	 v_result := v_result || v_arabic;
 14    end loop;
 15    return v_result;
 16  end english_to_arabic;
 17  /

Function created.

SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> select english_to_arabic ('MARTIN') from dual
  2  /

ENGLISH_TO_ARABIC('MARTIN')
--------------------------------------------------------------------------------
martin

1 row selected.

SCOTT@orcl_11gR2>

PLS-00201: identifier 'DBMS_NETWORK_ACL_ADMIN.CREATE_ACL' must be declared
PLS-00201: identifier 'DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL' must be declared

SQL> connect system/manager
Connected.
SQL> ALTER DATABASE CHARACTER SET AL32UTF8;
ALTER DATABASE CHARACTER SET AL32UTF8
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set

ORA-12712: new character set must be a superset of old character set
 *Cause: When you ALTER DATABASE ... CHARACTER SET, the new
           character set must be a superset of the old character set.
           For example, WE8ISO8859P1 is not a superset of the WE8DEC.
 *Action: Specify a superset character set.

SQL> SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;

VALUE$
--------------------------------------------------------------------

WE8MSWIN1252

SQL> connect system/manager
Connected.
SQL> ALTER DATABASE CHARACTER SET AL32UTF8;
ALTER DATABASE CHARACTER SET AL32UTF8
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set

SQL> select translation('I','HI','EN') from dual;
ERROR:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00217: invalid character 2350 (U+092E)
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 254
ORA-06512: at "SCOTT.TRANSLATION", line 41
ORA-06512: at line 1

SQL> set define off
SQL> create or replace function translation
  2   (p_words in clob,  -- words to be translated
  3    p_to  in varchar2 default 'de', -- language to translate to (German)
  4    p_from in varchar2 default 'en') -- language to translate from (English)
  5                                          -- ar = Arabic
  6                                          -- en = English
  7                                          -- es = Spanish
  8                                          -- fr = French
  9  return clob
 10  as
 11       l_res        clob;
 12       l_words      clob;
 13     begin
 14       l_res := httpuritype
 15                   ('http://translate.google.com/?hl=' ||
 16                    p_from || '&layout=1&eotf=1&sl=' || p_from ||
 17                    '&tl=' || p_to || '&text=' ||
 18                    utl_url.escape (p_words) || '#').getclob();
 19       l_res := substr (l_res, instr (l_res, '<span title="' || p_words || '"'));
 20       l_res := substr (l_res, 1, instr (l_res, '</span>') + 6);
 21       l_words := XmlType (l_res).extract ('/span/text()').getStringVal();
 22       return l_words;
 23  end translation;
 24  /

Function created.

SQL> 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                                                                  

SQL> select translation ('I Love You','hi','en') from dual;
ERROR:
ORA-31011: XML parsing failed 
ORA-19202: Error occurred in XML processing 
LPX-00217: invalid character 2350 (U+092E) 
Error at line 1 
ORA-06512: at "SYS.XMLTYPE", line 254 
ORA-06512: at "SCOTT.TRANSLATION", line 21 
ORA-06512: at line 1 



no rows selected

SQL> spool off

SCOTT@orcl_11gR2> create or replace function translation
  2    (p_words in clob,		  -- words to be translated
  3  	p_to	in varchar2 default 'ar', -- language to translate to
  4  	p_from	in varchar2 default 'en') -- language to translate from
  5  					  -- ar = Arabic
  6  					  -- en = English
  7  					  -- es = Spanish
  8  					  -- fr = French
  9    return	   clob
 10  as
 11    l_res	   clob;
 12    l_words	   clob;
 13  begin
 14    l_res := httpuritype
 15  		  ('http://translate.google.com/?hl=' ||
 16  		   p_from || '&layout=1&eotf=1&sl=' || p_from ||
 17  		   '&tl=' || p_to || '&text=' ||
 18  		   utl_url.escape (p_words) || '#').getclob();
 19    l_words :=  regexp_substr
 20  		 (l_res,
 21  		  '<span id=result_box class="short_text"><span[^>]*>(.*)</span></span>',
 22  		  1, 1, 'i', 1);
 23    return l_words;
 24  end translation;
 25  /

Function created.

SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> select translation ('cats and dogs', 'es') from dual
  2  /

TRANSLATION('CATSANDDOGS','ES')
--------------------------------------------------------------------------------
gatos y perros

1 row selected.

SCOTT@orcl_11gR2> select translation ('cats and dogs', 'fr') from dual
  2  /

TRANSLATION('CATSANDDOGS','FR')
--------------------------------------------------------------------------------
les chats et les chiens

1 row selected.

SCOTT@orcl_11gR2> select translation ('cats and dogs', 'de') from dual
  2  /

TRANSLATION('CATSANDDOGS','DE')
--------------------------------------------------------------------------------
Katzen und Hunde

1 row selected.

SCOTT@orcl_11gR2> 

SCOTT@orcl_11gR2> create or replace function translation
  2    (p_words in clob,		  -- words to be translated
  3  	p_to	in varchar2 default 'ar', -- language to translate to
  4  	p_from	in varchar2 default 'en') -- language to translate from
  5  					  -- ar = Arabic
  6  					  -- en = English
  7  					  -- es = Spanish
  8  					  -- fr = French
  9    return	   clob
 10  as
 11    l_res	   clob;
 12    l_words	   clob;
 13  begin
 14    l_res := httpuritype
 15  		  ('http://translate.google.com/?hl=' ||
 16  		   p_from || '&layout=1&eotf=1&sl=' || p_from ||
 17  		   '&tl=' || p_to || '&text=' ||
 18  		   utl_url.escape (p_words) || '#').getclob();
 19    l_words :=  regexp_substr
 20  		 (l_res,
 21  		  '<span id=result_box class="short_text"><span[^>]*>(.*)</span></span>',
 22  		  1, 1, 'i', 1);
 23    return l_words;
 24  end translation;
 25  /

Function created.

SCOTT@orcl_11gR2> show errors
No errors.

SCOTT@orcl_11gR2> select translation ('I love you.', 'HI', 'EN') from dual
  2  /

TRANSLATION('ILOVEYOU.','HI','EN')
--------------------------------------------------------------------------------
मैं तुम्हें प्यार करता हूँ.


1 row selected.

SCOTT@orcl_11gR2>

SCOTT@orcl_11gR2> set define off
SCOTT@orcl_11gR2> create or replace function translation
  2    (p_words in clob,		  -- words to be translated
  3  	p_to	in varchar2 default 'ar', -- language to translate to
  4  	p_from	in varchar2 default 'en') -- language to translate from
  5  					  -- ar = Arabic
  6  					  -- de = German (Deutsch)
  7  					  -- en = English
  8  					  -- es = Spanish (Español)
  9  					  -- fr = French (Français)
 10    return	   clob
 11  as
 12    l_words	   clob;
 13  begin
 14    l_words := httpuritype
 15  		  ('http://translate.google.com/?hl=' ||
 16  		   p_from || '&layout=1&eotf=1&sl=' || p_from ||
 17  		   '&tl=' || p_to || '&text=' ||
 18  		   utl_url.escape (p_words) || '#').getclob();
 19    l_words := substr (l_words, instr (l_words, '<span id=result_box class="short_text"><span'));
 20    l_words := substr (l_words, instr (l_words, '>') + 1);
 21    l_words := substr (l_words, instr (l_words, '>') + 1);
 22    l_words := substr (l_words, 1, instr (l_words, '</span></span>') - 1);
 23    return l_words;
 24  end translation;
 25  /

Function created.

SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> select translation ('cats and dogs', 'es') from dual
  2  /

TRANSLATION('CATSANDDOGS','ES')
--------------------------------------------------------------------------------
gatos y perros

1 row selected.

SCOTT@orcl_11gR2> select translation ('cats and dogs', 'fr') from dual
  2  /

TRANSLATION('CATSANDDOGS','FR')
--------------------------------------------------------------------------------
les chats et les chiens

1 row selected.

SCOTT@orcl_11gR2> select translation ('cats and dogs', 'de') from dual
  2  /

TRANSLATION('CATSANDDOGS','DE')
--------------------------------------------------------------------------------
Katzen und Hunde

1 row selected.

SCOTT@orcl_11gR2> select translation ('I love you.', 'HI', 'EN') from dual
  2  /

TRANSLATION('ILOVEYOU.','HI','EN')
--------------------------------------------------------------------------------
मैं तुम्हें प्यार करता हूँ.


1 row selected.

SCOTT@orcl_11gR2>

SQL> select translation ('cats and dogs','hi','en') from dual;

TRANSLATION('CATSANDDOGS','HI','EN')                                            
----------------------------------------------------------------
बिल्लियों और &   <-- I wrote here some number with & like & # 2348; & # 2367 
(I putted space between & and # and number because 
its automatically converting them to characters here.)

select translation ('cats and dogs') from dual;
ERROR:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1674
ORA-12541: TNS:no listener
ORA-06512: at "SYS.HTTPURITYPE", line 34
ORA-06512: at "SYS.TRANSLATION", line 14
ORA-06512: at line 1

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.