Sending e-mail from Oracle Server in 10g

   
ORACLE DATABASE ADMINISTRATOR

Sending e-mail from Oracle Server

Gregory Williams
06.22.2006
Rating: -3.66- (out of 5)

In order to send e-mail 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.

Listing 1. Installation of UTL_MAIL

SQL> connect sys/password as sysdbaConnected. 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.

That's it! The installation and setup for UTL_MAIL is complete. The UTL_MAIL package only has one procedure, called Send, for sending e-mail (see Listing 2 for the syntax for the Send procedure). This package bundles the e-mail message and sends it to the UTL_SMTP package, and then the e-mail message is sent to the SMTP server.

Listing 2. Syntax for UTL_MAIL.SEND

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

Examples: I've created a database trigger that will send an e-mail using UTL_MAIL (see Listing 3).

Listing 3. Database shutdown using UTL_MAIL

CREATE OR REPLACE TRIGGER SCOTT.db_shutdownbefore shutdown on databasebegin utl_mail.send(   sender => '[e-mail]',   recipients => ' [e-mail]',   subject => 'Testing utl_mail',   message => 'The receipt of this email means'||    ' that shutting down the database'||    ' works for UTL_MAIL '   );end;/

Conclusion

There are two other procedures within the UTL_MAIL package that make life easy: UTL_MAIL.ATTACH_VARCHAR2 and UTL_MAIL.ATTACH_RAW. These procedures are for varchar2 and raw attachments, respectively.