From oracle 11gR2 onwards ACLs(Access control list) are mandatory to send mail from procedure using UTL_MAIL or UTL_SMTP. For this make sure XDB component is installed.

If XDB component is not installed Check – How to install XDB component in oracle.

 

Verify whether UTL_MAIL and UTL_SMTP is installed or not.

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

no rows selected.

 

As in our case, both utl_mail and utl_stmp are not installed.

 

Install the packages:

[host@oracle]$ cd $ORACLE_HOME/rdbms/admin

[host@oracle]$sqlplus / as sysdba

SQL> @utlmail

SQL> @utlsmtp

SQL> @prvtmail.plb

SQL> GRANT EXECUTE ON utl_mail TO PUBLIC;

SQL> GRANT EXECUTE ON utl_smtp TO PUBLIC;

 

Set the SMTP_OUT_SERVER parameter

NOTE – Please check with your OS admin for this smtp mail server hostname/hostname.

SQL> alter system set smtp_out_server='smtp-server.mail.dbaclass.com' scope=both;

System altered.

If smtp_out_server is set incorrectly ,then it will throw error -: ORA-29278: SMTP transient error: 421 Service not available

Now create ACLS
Suppose the user SCOTT want to send mail from procedure.

--- creating ACL as below 
exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('scott_utl_mail.xml','Allow mail to be send','SCOTT', TRUE, 'connect');
commit;
----Grant the connect and resource privilege as below
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('scott_utl_mail.xml','SCOTT', TRUE, 'connect');
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('scott_utl_mail.xml' ,'SCOTT', TRUE, 'resolve');
exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('scott_utl_mail.xml','*',25);
commit;

 

Test whether user SCOTT is  able to send mail or not

 

SQL>conenct scott/tiger
connected
SQL>EXECUTE UTL_MAIL.SEND(SENDER=>'me@dbaclass.com',RECIPIENTS=>'rpatro@dbaclass.com,admin@dbaclass.com', MESSAGE=>'Hello World');

 

Dictionary tables for ACLS

 

SQL> select * from dba_network_acls;

SQL> select * from dba_network_acl_privileges;

 

SEE – COLLECTION OF USEFUL DATABASE SCRIPTS

 

SEE NEW FEATURES OF ORACLE 12.2: