Sending email from a stored procedure or trigger

Is it possible to send e-mail from an Oracle stored procedure or trigger. For example, I want to send mail to the relevant people if records in a table are older than a certain date.

    Requires Free Membership to View

    Login

Yes, using the UTL_SMTP package this is quite easy.

Here's an example directly from the Oracle documentation Supplied Packages Reference: The following code example illustrates how the SMTP package might be used by an application to send email. The application connects to an SMTP server at port 25 and sends a simple text message.

PROCEDURE send_mail (sender IN VARCHAR2,
recipient IN VARCHAR2,
message IN VARCHAR2)
IS
mailhost VARCHAR2(30) := 'mailhost.mydomain.com';
mail_conn utl_smtp.connection;
BEGIN
mail_conn := utl_smtp.open_connection(mailhost, 25);
utl_smtp.helo(mail_conn, mailhost);
utl_smtp.mail(mail_conn, sender);
utl_smtp.rcpt(mail_conn, recipient);
utl_smtp.data(mail_conn, message);
utl_smtp.quit(mail_conn);
EXCEPTION
WHEN OTHERS THEN
-- Handle the error
END;
Here's a link to another resource for a sample and more indepth explanation of UTL_SMTP: http://gethelp.devx.com/techtips/oracle_pro/10min/10min0602/10min0602.asp. Take a look at the documentation for more details on the functions/procedures in the UTL_SMTP package and you should be well on your way.

For More Information


This was first published in May 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top