---------------------------------------------------------------
-- script  :  send_emails.sql
-- author  :  amar_padhi@hotmail.com
-- date    :  12 oct 02
-- Note    :  refer original mail.
--            compile the below code in your database.
--            Execute the anonymous block at the end
--            of this script along with the right 
--            e-mail address.
----------------------------------------------------------------

create or replace package email is
  procedure send_mail(pi_frmadd  in            varchar2, 
                      pi_tooadd  in            varchar2, 
                      pi_subjct  in            varchar2, 
                      pi_msg     in            varchar2, 
                      pio_status in out nocopy varchar2);
end email;
/
show err


create or replace package body email is
  g_maicon utl_smtp.connection;	

  procedure open_mail_server(pio_status in out nocopy varchar2) is
    l_maihst varchar2(100) := 'localhost'; 
     -- mail server IP address/name, instead of hardcoding this can be made a system parameter. 
  begin
    pio_status := 'OK';
    g_maicon := utl_smtp.open_connection(l_maihst);
    utl_smtp.helo(g_maicon, l_maihst);   -- perform initial handshake.
  exception
    when others then
      pio_status := substr(sqlerrm, 1, 200);
  end open_mail_server;


  procedure close_mail_server(pio_status in out nocopy varchar2) is
  begin
    pio_status := 'OK';
    utl_smtp.quit(g_maicon);
  exception
    when others then
      pio_status := substr(sqlerrm, 1, 200);
  end close_mail_server;


  procedure send_mail(pi_frmadd  in            varchar2, 
                      pi_tooadd  in            varchar2, 
                      pi_subjct  in            varchar2, 
                      pi_msg     in            varchar2, 
                      pio_status in out nocopy varchar2) is

    errexc  exception;
  begin
    --< open connection >--
    pio_status := 'OK';
    open_mail_server(pio_status);
    if pio_status != 'OK' then
      raise errexc;
    end if;

    --< assign from and to >--
    utl_smtp.mail(g_maicon, pi_frmadd);
    utl_smtp.rcpt(g_maicon, pi_tooadd);


    --< create message text >--
    utl_smtp.open_data(g_maicon);
    utl_smtp.write_data(g_maicon, 'From: "' || pi_frmadd || '"<'
                         || pi_frmadd || '>' || utl_tcp.crlf);
    utl_smtp.write_data(g_maicon, 'To: "' || pi_tooadd || '"<' 
                         || pi_tooadd || '>' || utl_tcp.crlf);
    utl_smtp.write_data(g_maicon, 'Subject: ' || pi_subjct || utl_tcp.crlf);
    utl_smtp.write_data(g_maicon, pi_msg); 
    utl_smtp.close_data(g_maicon);

    --< close connection >--
    close_mail_server(pio_status);
  exception
    when errexc then
      return;
    when others then
      pio_status := substr(sqlerrm, 1, 200);
  end send_mail;

end email;
/

show err

/* 
--Cut-paste the below code and execute it to send email from Oracle. 
--Specify the receipent e-mail ids.

set serverout on size 1000000
declare
  l_frmadd varchar2(100) := 'ts2017@emirates.com';
  l_tooadd varchar2(100) := 'ts2017@emirates.com';
  l_subjct varchar2(100) := 'Cool Oracle mail';
  l_msg    varchar2(500) := 'You have received this mail from the database server BINGO!!' ||
                             utl_tcp.crlf || 'Checking if the content is acceptable!!!' ;
  l_status varchar2(200);
begin
  email.send_mail(l_frmadd, l_tooadd, l_subjct, l_msg, l_status);
  dbms_output.put_line(l_status);
end; 
/
*/