Sending e-mail from Oracle Server in 10g

最新推荐文章于 2025-12-01 17:13:11 发布

转载最新推荐文章于 2025-12-01 17:13:11 发布 · 1.7k 阅读

文章标签：

#oracle #server #database #system #null #sql

Oracle DB 专栏收录该内容

28 篇文章

订阅专栏

本文介绍如何在Oracle 10g中配置并使用UTL_MAIL包发送电子邮件。文章详细说明了安装UTL_MAIL的过程及配置SMTP_OUT_SERVER参数的方法，并提供了发送邮件的示例。

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 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 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 => 'gjwilliams01@yahoo.com',
   recipients => ' gjwilliams01@yahoo.com',
   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.