Ask the Expert

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: