Sending E-Mail in PL/SQL

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.');