Send mail from oracle UTL_MAIL, UTL_SMTP | resolve ACL

From Oracle 11gR2 fine grained access to network services are introduced by using access control lists (ACL) in the XML DB (XDB) repository. ACL allows user access control on network resources. 

Check for UTL_MAIL / UTL_SMTP is installed.

SQL> select owner, object_name, object_type from dba_objects where object_name in('UTL_MAIL','UTL_SMTP'); 

no rows selected

To install UTL_MAIL:

sqlplus sys/<passwd>

SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql

SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb

SQL> @$ORACLE_HOME/rdbms/admin/utlsmtp.sql

SQL> GRANT EXECUTE ON utl_mail TO PUBLIC;

SQL> GRANT EXECUTE ON utl_smtp TO PUBLIC;

Setting SMTP_OUT_SERVER parameter :

You can define the SMTP_OUT_SERVER parameter in the init.ora parameter file. OR

SQL> alter system set smtp_out_server='sev42.smtp' scope=both;

System altered.

ORA-29278: SMTP transient error: 421 Service not available will be encountered if hostname is wrong.

NOTE – Linux Admin can tell you smtp mail server hostname.

CREATE ACL for the host

Oracle will create ACL when you append ACL entry for the first time.
Execute by logging in as DBA user. Input SMTP server hostname at '<example.com>'

begin 

 dbms_network_acl_admin.append_host_ace( 

    host=>'<example.com>', 

    lower_port=>25,

    upper_port=>25,

    ace=>xs$ace_type(privilege_list =>xs$name_list('connect','resolve'), 

                     principal_name=>'SCOTT', 

                     principal_type =>xs_acl.ptype_db)); 

 END;

TROUBLESHOOT ACL ISSUES

Check whether  the host is added into ACL list using DBA_NETWORK_ACL_PRIVILEGES views.

COLUMN ACL FORMAT A45

COLUMN HOST FORMAT A20

COLUMN PRINCIPAL FORMAT A10

COLUMN PRIVILEGE FORMAT A10

SELECT 

    A.ACL,

    B.HOST,

    B.LOWER_PORT,

    B.UPPER_PORT,

    B.ACE_ORDER,

    B.PRINCIPAL,

    B.PRIVILEGE

FROM DBA_NETWORK_ACL_PRIVILEGES A, DBA_HOST_ACES B

WHERE A.PRINCIPAL=B.PRINCIPAL

AND UPPER(A.PRIVILEGE)=UPPER(B.PRIVILEGE)

ORDER BY HOST,ACE_ORDER;

Default port for Mail server is 25. It can be changed.

Sending Email :

SQL>connect scott/tiger

connected

SQL>EXECUTE UTL_MAIL.SEND(

SENDER=>'sev01proddb02@dbateam.com',

RECIPIENTS=>'somename@yahoo.com,dbateam@gmail.com', 

MESSAGE=>'Test message from Prodserver12'

);

For more email sending options(with attachments, CC, BCC,etc..) check UTL_MAIL package