UTL_MAIL & UTL_SMTP

Install / Configure

NOTE: UTL_MAIL is not installed by default because of the SMTP_OUT_SERVER configuration requirement and the security exposure this involves. In installing UTL_MAIL, you should take steps to prevent the port defined by SMTP_OUT_SERVER being swamped by data transmissions. (2)

sqlplus / as sysdba 

@$ORACLE_HOME/rdbms/admin/utlmail.sql 

@$ORACLE_HOME/rdbms/admin/prvtmail.plb 

GRANT EXECUTE ON UTL_MAIL TO user; 

ALTER SYSTEM SET smtp_out_server = 'mail.domain.com:25' SCOPE=BOTH;

SMTP_OUT_SERVER

SMTP_OUT_SERVER = host_name[ :port ] , [ host_name [ :port ] ]

SMTP_OUT_SERVER specifies the SMTP host and port to which UTL_MAIL delivers out-bound E-mail. Multiple servers may be specified, separated by commas.If the first server in the list is unavailable, then UTL_MAIL tries the second server, and so on.If SMTP_OUT_SERVER is not specified, then the SMTP server name defaults to the value of DB_DOMAIN, the port number defaults to 25, and the SMTP domain defaults to the suffix of DB_DOMAIN. (1)This parameter was introduced in release 10.1

DB_DOMAIN

DB_DOMAIN = domain_name 

Any legal string of name components, separated by periods and up to 128 characters long (including the periods). This value cannot be NULL (4) < NOT SURE THIS IS TRUE

ACLs

Use the following SQL to check that any less privileged users who may need to use UTL_MAIL/UTL_SMTP have the correct ACL to do so...

This example identifies which ACL, if any, allows myUser access to myMailserver.com (as identified by SMTP_OUT_SERVER)...

COLUMN host FORMAT a10

COLUMN acl  FORMAT a40

SET LINESIZE 132

SELECT  dna.host,

        dna.lower_port,

        dna.upper_port,

        dna.acl,

        DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(dna.aclid, 'MYUSER', 'connect'),

               1, 'GRANTED',

               0, 'DENIED',

               null) privilege

FROM    dba_network_acls dna

WHERE   host IN (SELECT  *

                 FROM    TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('mymailserver.com')))

ORDER BY DBMS_NETWORK_ACL_UTILITY.DOMAIN_LEVEL(host) desc,

         lower_port,

         upper_port

/

If the user does not have connect access, it will be unable to send mail. Refer to the ACL documentation for instructions on how to create a new ACL if required, along with steps to take if you change the name of your mailserver.

Usage Examples

BEGIN

   UTL_MAIL.SEND(sender => 'myuser@mydomain.com', 

                 recipients => 'youruser@yourdomain.com', 

                 cc => 'usera@domaina.com', 

                 bcc => 'userb@domainb.com', 

                 subject => 'UTL_MAIL Test', 

                 message => 'Hello'); 

END; 

/

Calling UTL_MAIL from a Trigger

CREATE OR REPLACE TRIGGER schemaname.triggername 

before shutdown on database 

begin 

   utl_mail.send( 

      sender => 'myuser@mydomain.com', 

      recipients => 'youruser@yourdomain.com', 

      subject => 'Testing utl_mail', 

      message => 'The receipt of this email means'|| 

         ' that shutting down the database'|| 

         ' works for UTL_MAIL ' 

      ); 

end; 

/

Troubleshooting

Calling UTL_SMTP directly

This can be useful for troubleshooting...

DECLARE

   c utl_smtp.connection; 

   r utl_smtp.reply; 

BEGIN

   c := utl_smtp.open_connection('mail.domain.com', 25); 

   utl_smtp.helo(c,'mydomain.com'); 

END; 

/

 If the above works then try this...

DECLARE

   c utl_smtp.connection; 

   r utl_smtp.reply; 

BEGIN

   c := utl_smtp.open_connection('mail.domain.com', 25); 

   utl_smtp.helo(c,'mydomain.com'); 

   utl_smtp.mail(c,'myuser@mydomain.com'); 

END; 

/


Troubleshooting outside the database instance

Can your SMTP server ping your mail server? 

ping mail.domain.com 

Can your SMTP server resolve the mail server name? 

nslookup mail.domain.com 

Does your host name have access to connect to the SMTP server on port 25? 

telnet mail.domain.com 25 

Trying... Connected to mail.domain.com. Escape character is '^]'. 220 mailhost.domain.com ESMTP Service (mailserver type/version) ready at date

quit 

221 mailhost.domain.com SMTP Service closing transmission channelConnection closed. 

Can you send mail via telnet? 

telnet mail.domain.com 25 

Trying... Connected to mail.domain.com. Escape character is '^]'. 220 mailhost.domain.com ESMTP Service (Lotus Domino Release 7.0.2) ready at Thu, 21 Jun 2007 03:46:39 -0500 

HELO mydomain 

250 mailhost.domain.com Hello mydomain ([ipadress]), pleased to meet you 

MAIL FROM: myuser@mydomain.com 

554 Mail from myuser@mydomain.com rejected for policy reasons.NOTE: this particular error could be due to a DNS mis-configuration

Bibliography & References

(1),(4) B14237-02 Oracle Database Reference 10g Release 2 (10.2)(2) B14258-01 (172-3) Oracle Database PL/SQL Packages and Types Reference 10g Release 2 (10.2)