PL/SQL: Send Email from the database

Collected from the links: https://sites.google.com/site/craigsoraclestuff/pl-sql/send-email-from-pl-sql

EMAIL_PKG

An Oracle PL/SQL package to facilitate the sending of email messages from an Oracle database.  This database stored package was designed to make it easier to send email messages from an Oracle database or an Oracle Forms application. 

I adapted this package from Oracle’s DEMO_MAIL package that is downloadable from the Oracle Technology Network (OTN). 

In order to utilize this package, you must make a few modifications to the code to configure the package to use your SMTP mail server.  I have seeded the package with a fictitious SMTP Server name and the package will compile in your Oracle database as is, but you will receive an Exception when you attempt to send an email because the SMTP server name is invalid.

Installation

1. Open the email_pkg.sql file in your favorite text editor.  (I prefer to use TextPad from Helios Software, but you can use Windows Notepad if you like so long as the editor saves the files as a true text file.)
2. Review the “Additional Information:” section in the package header (Lines 13-29).
3. Search the email_pkg.sql file for the word, “REQUIRED”.   There are three lines that must be modified to support your environment.  These lines start on line 98:
1. smtp_host               CONSTANT VARCHAR2(256) := 'mail.myserver.com';
2. smtp_port               CONSTANT PLS_INTEGER := 25;
3. smtp_domain          CONSTANT VARCHAR2(256) := 'myserver.com';
4. Modify the above variable declarations to use your SMTP server host address, port number (typically port 25), and the domain name.
4. If you intend to send email attachments, you will need to create a an Oracle Directory Object (as described on lines 25-29 in the email_pkg.sql file) or you will need to modify the package and replace all references to the UPLOAD_DIR Directory Object with a hard-coded directory path. 

In my opinion it is never a good idea to use hard-coded values in code because the cost of changing these hard-coded values is too great and it is a maintenance nightmare.  By using an Oracle Directory Object, if you need to change the location on the database server where file attachments reside, it is a simple update to the database object rather than modify multiple lines of code.

Once you have made these changes you are ready to compile the email_pkg package in your database.  The user that will own the email_pkg must have execute privileges to the UTL_SMTP and UTL_ENCODE Oracle packages in order to successfully create this package in your database.

Example of how to use email_pkg.

DECLARE

            V_Message_Body   VARCHAR2(4000);

BEGIN

            V_message_body := ‘This is a sample email message body.’);

            Email_pkg.set_from(‘donot_reply@some_server.com’);

            Email_pkg.set_recipient(‘recipient1@email_address.com’);

            Email_pkg.set_cc(‘CarbonCopy@email_address.com’);

            Email_pkg.set_bcc(‘blindCC@email_address.com’);

            Email_pkg.set_subject(‘This is a sample email subject’);

            Email_pkg.set_message(v_message_body);

            Email_pkg.send;

END;

Download the example files from: send-email-from-pl-sql