Note: Technical Talk being moved to:
www.nazimcricket.com/wiki


Tuesday, 12 August 2008

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

No comments: