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 	=> 'SCOTT',
  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 soundmachine
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 soundmachine
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 soundmachine.getmp3('hello') from dual


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


http://xp-vm:2100/hrm/scott.soundmachine.soundcheck
http://xp-vm:2100/hrm/scott.soundmachine.play_sound?p_text=worldwide

http://xp-vm:2100/hrm/scott.soundmachine.play_employee?p_empno=7902&p_language=fr