Snippet Name: Send email with attachments from PLSQL
Description: Send e-mail messages and attachments directly from PL/SQL using either the UTL_TCP or UTL_SMTP
packages. Jserver needs to be installed and configured.
Comment: (none)
Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 03rd, 2009
|
CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_from VARCHAR2 := '[email protected]', ----- MAIL BOX SENDING THE EMAIL
msg_to VARCHAR2 := '[email protected]', ----- 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;
/
|