Oracle 11g Embed Text to Sound translator

CREATE TABLE EMP (EMPNO VARCHAR2(10),ENAME VARCHAR2(20), SAL VARCHAR2(10), JOB VARCHAR2(10) ,HIREDATE DATE,DEPTNO VARCHAR2(10));


 INSERT INTO EMP VALUES('10','KSHITIJ','100','DBA','01-DEC-2011','12');
COMMIT;

------------------------------------------------------------------------------------------------


begin
        dbms_network_acl_admin.create_acl (
                acl             => 'utlpkg.xml',
                description     => 'Normal Access',
                principal       => 'CONNECT',
                is_grant        => TRUE,
                privilege       => 'connect',
                start_date      => null,
                end_date        => null
        );
  dbms_network_acl_admin.add_privilege (
  acl => 'utlpkg.xml',
  principal => 'DPCDSL',
  is_grant => TRUE,
  privilege => 'connect',
  start_date => null,
  end_date => null);

  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'utlpkg.xml',
    host        => 'translate.google.com',
    lower_port  => 80,
    upper_port  => NULL);

  COMMIT;
END;
/

--------------------------------------------------------------------------------------------------------------------------------
create or replace
package mytranslator
as

function getMP3
( p_text in varchar2
, p_language in varchar2 default 'en'
) return blob
;

procedure play_sound
( p_text in varchar2
);

procedure play_employee
( p_empno    in number
, p_language in varchar2 default 'en'
);

procedure soundcheck;
end;
/

------------------

create or replace
package body mytranslator
as

function load_binary_from_url (p_url  IN  VARCHAR2)
return blob
AS
  l_http_request   UTL_HTTP.req;
  l_http_response  UTL_HTTP.resp;
  l_blob           BLOB;
  l_raw            RAW(32767);
BEGIN
  -- Initialize the BLOB.
  DBMS_LOB.createtemporary(l_blob, FALSE);
  -- Make a HTTP request and get the response.
  l_http_request  := UTL_HTTP.begin_request(p_url);
  l_http_response := UTL_HTTP.get_response(l_http_request);
  -- Copy the response into the BLOB.
  BEGIN
    LOOP
      UTL_HTTP.read_raw(l_http_response, l_raw, 32767);
      DBMS_LOB.writeappend (l_blob, UTL_RAW.length(l_raw), l_raw);
    END LOOP;
  EXCEPTION
    WHEN UTL_HTTP.end_of_body
THEN
      UTL_HTTP.end_response(l_http_response);
  end;
  return l_blob;
end load_binary_from_url;


function getMP3
( p_text in varchar2
, p_language in varchar2 default 'en'
) return blob
is
  l_base_url varchar2(2000):= 'http://translate.google.com/translate_tts?tl=';
begin
  return load_binary_from_url
  ( p_url=> l_base_url||p_language||chr(38)||'q='
  ||utl_url.escape(p_text)
  );
 -- note: chr(38) is the ampersand
end getMP3;

procedure download_blob
( p_blob in out nocopy blob
) as
  l_mime_type varchar2(30):= 'audio/mpeg';
  l_blob_size number(10);
begin
l_blob_size := dbms_lob.getlength(p_blob);
owa_util.mime_header(l_mime_type, FALSE, NULL);
    htp.p('Content-length: '|| l_blob_size);
  owa_util.http_header_close;
  -- download BLOB
wpg_docload.download_file(p_blob);
end download_blob;

procedure play_sound
( p_text in varchar2
) is
  l_blob blob;
begin
  l_blob:= getMP3 ( p_text);
  download_blob (l_blob);
end play_sound;

procedure play_employee
( p_empno    in number
, p_language in varchar2 default 'en'
) is
  l_blob blob;
  l_blob2 blob;
begin

  l_blob:= getMP3 ( 'Details on Employee', p_language);
 
  for emp in (select ename, sal, job, to_char(hiredate, 'MONTH YYYY') hireyear, deptno
              from   emp
              where  empno = p_empno
              ) loop
    l_blob2:= getMP3 ( 'Employee is called '||emp.ename||' his job is '||emp.job, p_language);
    DBMS_LOB.APPEND
    ( dest_lob => l_blob
    , src_lob  => l_blob2
    );
    l_blob2:= getMP3 ( 'He works in department '||emp.deptno||' and earns  '||emp.sal||' dollar', p_language);
    DBMS_LOB.APPEND
    ( dest_lob => l_blob
    , src_lob  => l_blob2
    );
    l_blob2:= getMP3 ( 'He was hired back in '||emp.hireyear, p_language);
    DBMS_LOB.APPEND
    ( dest_lob => l_blob
    , src_lob  => l_blob2
    );
  end loop;
 
  download_blob (l_blob);  
exception
  when others
  then
    l_blob:= getMP3 ( 'Exception occurred '||sqlerrm, p_language);
    download_blob (l_blob);  
end play_employee;


procedure soundcheck
is
  l_blob blob;
  space varchar2(4):='%20';
begin

  l_blob:= getMP3 ( 'Test one two three test', p_language=> 'en');
  download_blob (l_blob);  
exception
  when others
  then
    l_blob:= getMP3 ( 'Exception'||space||'occurred', p_language =>'en');
    download_blob (l_blob);  
end soundcheck;


end;

------------------

select mytranslator.getmp3('hello how r u what is your name. my name is john') from dual

select mytranslator.play_sound('hello how r u what is your name. my name is john') from dual


BEGIN
  DBMS_EPG.create_dad
  ( dad_name => 'hrmreport'
  , path => '/hrm/*'
  );
  DBMS_EPG.AUTHORIZE_DAD('hrmreport','DPCDSL');
end;
/


http://blog.mclaughlinsoftware.com/2009/12/01/oracle-11g-xdb-dads/

http://www.oracle-base.com/articles/10g/dbms_epg_10gR2.php

http://psoug.org/reference/dbms_epg.html

SELECT * FROM dba_epg_dad_authorization;