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.
Listing 1. 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.
That's it! The installation and setup for UTL_MAIL is complete. The UTL_MAIL package only has one procedure, called Send, for sending email (see Listing 2 for the syntax for the Send procedure). This package bundles the email message and sends it to the UTL_SMTP package, and then the email 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 email using UTL_MAIL (see Listing 3).
Listing 3. Database shutdown using UTL_MAIL
CREATE OR REPLACE TRIGGER SCOTT.db_shutdown before shutdown on database begin utl_mail.send( sender => '[email protected]', recipients => ' [email protected]', subject => 'Testing utl_mail', message => 'The receipt of this email means'|| ' that shutting down the database'|| ' works for UTL_MAIL ' ); end; /
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.