Send email from Oracle 10g Server

In order to send email within 10g you must install and set up the UTL_MAIL package.

UTL_MAIL isn't installed when the database is installed because the SMTP_OUT_SERVER parameter must be configured. Listing 1 shows how to install UTL_MAIL and the results from the script. You must connect to the database as user SYS and run the two scripts identified in the listing.

Installation of UTL_MAIL

SQL> connect sys/password as sysdba
Connected.

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

Package created.

Synonym created.

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

Package body created.

No errors.

Next, the SMTP_OUT_SERVER parameter must be configured.

You must connect to SYS and then use the alter system command to configure SMTP_OUT_SERVER parameter as shown here:

SQL> alter system set smtp_out_server = 'ip-address:port' scope=Both;

System altered.

If you're not sure what your SMTP server is, try and find the information on one of your existing data:

SQL> show parameter smtp_out_server

This will show the IP address as well as the port number, which is usually port 25 by defaul.


SEND Procedure

This procedure packages an email message into the appropriate format, locates SMTP information, and delivers the message to the SMTP server for forwarding to the recipients. It hides the SMTP API and exposes a one-line email facility for ease of use.

UTL_MAIL.SEND (
sender IN VARCHAR2 CHARACTER SET ANY_CS,
recipients IN VARCHAR2 CHARACTER SET ANY_CS,
cc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
bcc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
subject IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
message IN VARCHAR2 CHARACTER SET ANY_CS,
mime_type IN VARCHAR2 DEFAULT 'text/plain; charset=us-ascii',
priority IN PLS_INTEGER DEFAULT NULL);

Grant privilege to user to user UTL_MAIL:

GRANT execute ON utl_mail TO user ;