-- #############################################################################################
--
-- %Purpose: Send E-Mail Messages from PL/SQL with Oracle 8.1.6 using UTL_TCP or UTL_SMTP
--
-- Notes: From Oracle8i release 8.1.6 one can send e-mail messages
--        directly from PL/SQL using either the UTL_TCP or UTL_SMTP
--        packages. No pipes or external procedures required.
--
-- #############################################################################################
--
CREATE OR REPLACE PROCEDURE SEND_MAIL (
  msg_from    varchar2 := 'PL/SQL daemon',
  msg_to      varchar2 := 'martin.zahn@akadia.com',
  msg_subject varchar2 := 'Message from PL/SQL daemon',
  msg_text    varchar2 := 'This Message was automatically send by PL/SQL daemon' )
IS
  c  utl_tcp.connection;
  rc integer;
BEGIN
  c := utl_tcp.open_connection('127.0.0.1', 25);       -- open the SMTP port
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'HELO localhost');
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'MAIL FROM: '||msg_from);
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'RCPT TO: '||msg_to);
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'DATA');                 -- Start message body
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'Subject: '||msg_subject);
  rc := utl_tcp.write_line(c, '');
  rc := utl_tcp.write_line(c, msg_text);
  rc := utl_tcp.write_line(c, '.');                    -- End of message body
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'QUIT');
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  utl_tcp.close_connection(c);                         -- Close the connection
EXCEPTION
  when others then
       raise_application_error(-20000,
       'Unable to send e-mail message from pl/sql');
END;
/
show errors

--  Examples:
set serveroutput on

exec send_mail(msg_to  =>'martin.zahn@akadia.com');
exec send_mail(msg_to  =>'martin.zahn@akadia.com',
               msg_text=>'How to send E-Mail from PL/SQL');