Oracle 10g: UTL_MAIL

UTL_MAIL

The UTL_MAIL package provides a simple API to allow email to be sent from PL/SQL. In prior versions this was possible using the UTL_SMTP package (shown here), but this required knowledge of the SMTP protocol.

The package is loaded by running the following scripts.

CONN sys/password AS SYSDBA
@$ORACLE_HOME/rdbms/admin/utlmail.sql
@$ORACLE_HOME/rdbms/admin/prvtmail.plb

In addition the SMTP_OUT_SERVER parameter must be set to identify the SMTP server.

CONN sys/password AS SYSDBA
ALTER SYSTEM SET smtp_out_server='smtp.domain.com' SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP

With the configuration complete we can now send a mail.

BEGIN
  UTL_MAIL.send(sender     => '[email protected]',
                recipients => '[email protected],[email protected]',
                cc         => '[email protected]',
                bcc        => '[email protected]',
                subject    => 'UTL_MAIL Test',
                message    => 'If you get this message it worked!');
END;
/

The package also supports sending mails with RAW and VARCHAR2 attachments.