PL/SQL Send Mail Procedure

A useful way of sending email through Oracle.

Parameters such as the SMTP host servername, SMTP port may be entered in as default values or be passed in for greater flexibility

CREATE OR REPLACE PROCEDURE SEND_MAIL
(  p_smtp_host in varchar2 default {'smtp_host'} 
 , p_smtp_port in number default {smtp_port}
 , p_sender in varchar2 default null
 , p_recipient in varchar2 default null
 , p_subject in varchar2 default null
 , p_message in varchar2 default null
) IS

connection UTL_SMTP.CONNECTION;

BEGIN

 connection := utl_smtp.open_connection( p_smtp_host, p_smtp_port );

  utl_smtp.helo( connection, p_smtp_host );
  utl_smtp.mail( connection, p_sender );
  utl_smtp.rcpt( connection, p_recipient );
  utl_smtp.data( connection, 'Subject: ' || p_subject  || utl_tcp.crlf ||       p_message );
  utl_smtp.quit( connection );

EXCEPTION
WHEN OTHERS THEN
 dbms_output.put_line(SQLERRM);

END;
/

The procedure may be called using syntax as follows.

Note: Parameters which are passed in with default values on the SEND_MAIL procedure, e.g. p_smtp_host and p_smtp_port may be excluded from this call.

exec send_mail  ( p_smtp_host => 'smtp_host', -
                  p_smtp_port => smtp_port, -
                  p_sender    => 'sender@sender_address', -
                  p_recipient => 'recipient@recipient_address', -
                  p_subject   => 'Subject Line - Cricket Site', -
                  p_message   => 'Message Body - Check out www.nazimcricket.com' -
                );

From Oracle 10g onward, utl_mail appears to be an easier way to send mail.

Prerequisite is to install the packages as they aren't available by default:

SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb

Also init.ora parameter smtp_out_server needs to be set

The package can be executed as follows:

DECLARE

vSender VARCHAR2(100) := 'sender@senderemail.com';
vRecip VARCHAR2(100) := 'recipient@recipientemail.com';
vSubj VARCHAR2(100) := 'Subject Line';
vMesg VARCHAR2(4000) := 'Message Body';
vMType VARCHAR2(30) := 'text/plain; charset=us-ascii';

BEGIN
utl_mail.send
(vSender, vRecip, NULL, NULL, vSubj, vMesg, vMType, NULL);
END;
/