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.

 

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

 

Install the packages:

 

Set the SMTP_OUT_SERVER parameter

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

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.

 

Test whether user SCOTT is  able to send mail or not

 

 

Dictionary tables for ACLS

 

 

SEE – COLLECTION OF USEFUL DATABASE SCRIPTS

 

SEE NEW FEATURES OF ORACLE 12.2: