Problem solve Get help with specific problems with your technologies, process and projects.

Send email from Oracle 10g Server

In order to send email within Oracle 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. This tip 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.

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 => '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.

This was last published in June 2006

Dig Deeper on Oracle database design and architecture

PRO+

Content

Find more PRO+ content and other member only offers, here.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close