Sending Mail from Oracle

1) Send Mail

Usage:

set serveroutput on

exec send_mail(msg_to =>'dba@oncalldba.com');

exec send_mail(msg_to =>'dba@oncalldba.com', -
msg_text=>'Sample email text from the Oracle database' -
);

Procedure:

CREATE OR REPLACE PROCEDURE SEND_MAIL (
  msg_from    VARCHAR2 := 'oracle',
  msg_to      VARCHAR2,
  msg_subject VARCHAR2 := 'Email alert from the Oracle database',
  msg_text    VARCHAR2 := '' )
IS
  c  UTL_TCP.connection;
  rc INTEGER;
BEGIN
  c := UTL_TCP.open_connection('127.0.0.1', 25);       -- open the SMTP port 25 on local machine
  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 because of: '||
           SQLERRM);
END;
/ 

2) Sending Mail with Attachments

CREATE OR replace PROCEDURE Send_mail (
msg_from    VARCHAR2 := 'dba@oncalldba.com',
----- MAIL BOX SENDING THE EMAIL
msg_to      VARCHAR2 := 'dba@oncalldba.com',
----- MAIL BOX RECIEVING THE EMAIL
msg_subject VARCHAR2 := 'Output file TEST1',----- EMAIL SUBJECT
msg_text    VARCHAR2 := 'THIS IS THE TEXT OF THE EMAIL MESSAGE.',
v_output1   VARCHAR2 :=
'THIS IS THE TEXT OF THE ATTACHMENT FILE. THIS TEXT SHOULD BE IN A TEXT FILE ATTACHED TO THE EMAIL.'
)
IS
  c    utl_tcp.connection;
  rc   INTEGER;
  crlf VARCHAR2(2) := Chr(13)
                      ||Chr(10);
  mesg VARCHAR2( 32767 );
BEGIN
    c := utl_tcp.Open_connection('192.35.140.18', 25);
    ----- OPEN SMTP PORT CONNECTION
    rc := utl_tcp.Write_line(c, 'HELO 192.35.140.18');
    ----- PERFORMS HANDSHAKING WITH SMTP SERVER
    dbms_output.Put_line(utl_tcp.Get_line(c, TRUE));
    rc := utl_tcp.Write_line(c, 'EHLO 192.35.140.18');
    ----- PERFORMS HANDSHAKING WITH SMTP SERVER, INCLUDING EXTRA INFORMATION
    dbms_output.Put_line(utl_tcp.Get_line(c, TRUE));
    rc := utl_tcp.Write_line(c, 'MAIL FROM: '
                                ||msg_from); ----- MAIL BOX SENDING THE EMAIL
    dbms_output.Put_line(utl_tcp.Get_line(c, TRUE));
    rc := utl_tcp.Write_line(c, 'RCPT TO: '
                                ||msg_to); ----- MAIL BOX RECIEVING THE EMAIL
    dbms_output.Put_line(utl_tcp.Get_line(c, TRUE));
    rc := utl_tcp.Write_line(c, 'DATA'); ----- EMAIL MESSAGE BODY START
    dbms_output.Put_line(utl_tcp.Get_line(c, TRUE));
    rc := utl_tcp.Write_line(c, 'Date: '
                                ||To_char(SYSDATE, 'dd Mon yy hh24:mi:ss'));
    rc := utl_tcp.Write_line(c, 'From: '
                                ||msg_from
                                ||' <'
                                ||msg_from
                                ||'>');
    rc := utl_tcp.Write_line(c, 'MIME-Version: 1.0');
    rc := utl_tcp.Write_line(c, 'To: '
                                ||msg_to
                                ||' <'
                                ||msg_to
                                ||'>');
    rc := utl_tcp.Write_line(c, 'Subject: '
                                ||msg_subject);
    rc := utl_tcp.Write_line(c, 'Content-Type: multipart/mixed;');
    ----- INDICATES THAT THE BODY CONSISTS OF MORE THAN ONE PART
    rc := utl_tcp.Write_line(c, ' boundary="-----SECBOUND"');
    ----- SEPERATOR USED TO SEPERATE THE BODY PARTS
    rc := utl_tcp.Write_line(c, '');
    ----- INSERTS A BLANK LINE. PART OF THE MIME FORMAT AND NONE OF THEM SHOULD BE REMOVED.
    rc := utl_tcp.Write_line(c, '-------SECBOUND');
    rc := utl_tcp.Write_line(c, 'Content-Type: text/plain');
    ----- 1ST BODY PART. EMAIL TEXT MESSAGE
    rc := utl_tcp.Write_line(c, 'Content-Transfer-Encoding: 7bit');
    rc := utl_tcp.Write_line(c, '');
    rc := utl_tcp.Write_line(c, msg_text); ----- TEXT OF EMAIL MESSAGE
    rc := utl_tcp.Write_line(c, '');
    rc := utl_tcp.Write_line(c, '-------SECBOUND');
    rc := utl_tcp.Write_line(c, 'Content-Type: text/plain;');
    ----- 2ND BODY PART.
    rc := utl_tcp.Write_line(c, ' name="Test.txt"');
    rc := utl_tcp.Write_line(c, 'Content-Transfer_Encoding: 8bit');
    rc := utl_tcp.Write_line(c, 'Content-Disposition: attachment;');
    ----- INDICATES THAT THIS IS AN ATTACHMENT
    rc := utl_tcp.Write_line(c, ' filename="Test.txt"');
    ----- SUGGESTED FILE NAME FOR ATTACHMENT
    rc := utl_tcp.Write_line(c, '');
    rc := utl_tcp.Write_line(c, v_output1);
    rc := utl_tcp.Write_line(c, '-------SECBOUND--');
    rc := utl_tcp.Write_line(c, '');
    rc := utl_tcp.Write_line(c, '.'); ----- EMAIL MESSAGE BODY END
    dbms_output.Put_line(utl_tcp.Get_line(c, TRUE));
    rc := utl_tcp.Write_line(c, 'QUIT'); ----- ENDS EMAIL TRANSACTION
    dbms_output.Put_line(utl_tcp.Get_line(c, TRUE));
    utl_tcp.Close_connection(c); ----- CLOSE SMTP PORT CONNECTION
EXCEPTION
  WHEN OTHERS THEN
             Raise_application_error(-20000, SQLERRM);
END;
/  

~~ Cheers