Sending e-mail from Oracle Server

Gregory Williams
06.22.2006
Rating: -3.60- (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 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 = '

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_shutdown
before shutdown on database
begin
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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/308563/viewspace-171964/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/308563/viewspace-171964/