CoderZone.org

Category: >> Oracle PL/SQL >> Send email with attachments from PL/SQL Bookmark and Share

<< lastnext >>

Snippet Name: Send email with attachments from PL/SQL

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)

Author: CoderZone
Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 03rd, 2009

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;
/
 
 
There haven't been any comments added for this snippet yet. You may add one if you like.  Add a comment 
© coderzone.org | users online: 6