Oracle DBA Tips Corner      Return to the Oracle DBA Tips Corner.

Sending E-Mail in PL/SQL

by Jeff Hunter, Sr. Database Administrator

Example Code

CREATE OR REPLACE PROCEDURE SEND_MAIL_TCP (
    msg_from     VARCHAR2 := 'sender@testing.com'
  , msg_to       VARCHAR
  , msg_subject  VARCHAR2 := 'E-Mail message from your database'
  , msg_text     VARCHAR2 := ''
)
IS
  c   UTL_TCP.CONNECTION;
  rc  INTEGER;
BEGIN 
  c  := UTL_TCP.OPEN_CONNECTION('localhost', 25);       -- open the SMTP port 25 on local machine
  rc := UTL_TCP.WRITE_LINE(c, 'HELO localhost'); 
  rc := UTL_TCP.WRITE_LINE(c, 'MAIL FROM: '||msg_from); 
  rc := UTL_TCP.WRITE_LINE(c, 'RCPT TO: '||msg_to); 
  rc := UTL_TCP.WRITE_LINE(c, 'DATA');                  -- Start message body 
  rc := UTL_TCP.WRITE_LINE(c, 'Subject: '||msg_subject); 
  rc := UTL_TCP.WRITE_LINE(c, ''); 
  rc := UTL_TCP.WRITE_LINE(c, msg_text); 
  rc := UTL_TCP.WRITE_LINE(c, '.');                     -- End of message body 
  rc := UTL_TCP.WRITE_LINE(c, 'QUIT');
  UTL_TCP.CLOSE_CONNECTION(c);                          -- Close the connection 
EXCEPTION 
  WHEN others THEN 
    RAISE_APPLICATION_ERROR(-20000, 'Unable to send e-mail message from PL/SQL routine.'); 
END;

Testing the Example Code

EXEC SEND_MAIL_TCP ( msg_to => 'test_user@testing.com'
                   , msg_text => 'This is a test message.');