Live ORACLE

rem ----------------------------------------------------------------------
rem Filename:   smtp-att.sql
rem Purpose:    Send e-mail messages and attachments from PL/SQL
rem Notes:      From Oracle8i release 8.1.6 one can send e-mail messages
rem             directly from PL/SQL using either the UTL_TCP or UTL_SMTP
rem             packages. Jserver needs to be installed and configured.
rem             No pipes or external procedures required.
rem Date:       15-MAR-2001
rem Author:     Virgilio Nunes (Virgilio@logtek.co.za)
rem ----------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE SEND_MAIL (
  msg_from    varchar2 := 'EMAILADDRESS@DOMAIN.COM',    ----- MAIL BOX SENDING THE EMAIL
  msg_to      varchar2 := 'EMAILADDRESS@DOMAIN.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('196.35.140.18', 25);       ----- OPEN SMTP PORT CONNECTION
  rc := utl_tcp.write_line(c, 'HELO 196.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 196.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;
/

Send e-mail messages and attachments from PL/SQL