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.eraazi.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@eraazi.com',RECIPIENTS=>'rpatro@eraazi.com,admin@eraazi.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:

  •  Upgrade oracle database from 12cR1 to 12cR2 version:
  •  Convert non-partitioned table to partitioned table online
  • Datapump New features - Oracle 12.2 Version
  • Lock Account Automatically With INACTIVE_ACCOUNT_TIME
  • PDB Lockdown Profiles in  Oracle 12.2
  • SQL*Plus History In Oracle 12.2
  • Long Identifiers In Oracle 12.2
  • DBMS_TNS  package for tnsping in  database Oracle 12.2
  • Spool CSV In Oracle 12.2
  • VARIABLE new feature  in Oracle 12.2
  • Move table online in Oracle 12.2
  • Read only Partition in Oracle 12.2
  • Split partition online oracle 12.2
ORACLE SECURITY