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; /