Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: Send email with attachments from PL/SQL Jump to:  
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)

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


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 217 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?