Install / Configure

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 ] ]

DB_DOMAIN

DB_DOMAIN = domain_name 

Check

SET LINESIZE 200

COLUMN OWNER FORMAT A10

COLUMN OBJECT_NAME FORMAT A40

SELECT owner, 

       object_name, 

       object_type 

  FROM dba_objects 

 WHERE object_name LIKE '%UTL_MAIL%';

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 myrelay.mydomain.com (as identified by SMTP_OUT_SERVER)...

COLUMN host FORMAT a30

COLUMN acl  FORMAT a60

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('myrelay.mydomain.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 

quit 

Can you send mail via telnet? 

telnet mail.domain.com 25 

HELO mydomain 

MAIL FROM: myuser@mydomain.com 

Bibliography & References