Configure UTL_MAIL and UTL_SMTP in any Oracle Database Version

In order to send e-mail from oracle database, we must needs to install and set up UTL_MAIL and UTL_SMTP packages. To use these package, the smtp_out_server parameter must be set & “network acl” should be created for the relevant user and the relevant privileges must be defined. Here I will guide you how to set up both packages. Please follow all instructions / steps mentioned below.

·         Connect with databases

SYS/password AS SYSDBA;

Or (assume SSK is also a dba user)

SSK/password;

 

Connected

·         First, we have to check, whether UTL_MAIL and UTL_SMTP are installed or not

select * from DBA_OBJECTS where OBJECT_NAME in('UTL_MAIL','UTL_SMTP');

no rows selected.

 

* In our case, we are assuming that both utl_mail & utl_stmp are not installed. Then follow below

·         Next, Install the packages (Change the path below with your Oracle Home path)

In Windows

SQL> @C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\utlmail.sql

Package created

Synonym created

SQL> @C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\utlsmtp.sql

Package created

Synonym created

SQL> @C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\prvtmail.plb

Package body created

 

In Linux

@$ORACLE_HOME/rdbms/admin/utlmail.sql

@$ORACLE_HOME/rdbms/admin/prvtmail.plb

 

Commit;

·         Then, Grant the privilege to all the users for UTL_MAIL package

SQL> GRANT EXECUTE ON utl_mail TO PUBLIC;

SQL> GRANT EXECUTE ON utl_smtp TO PUBLIC;

Commit;

 

You can give a specific username instead of public. If you will not give the grant on UTL_MAIL / UTL_SMTP packages, then you may get the following error:

PLS-00201: identifier 'UTL_MAIL' must be declared

PLS-00201: identifier 'UTL_SMTP' must be declared

·         Check value of SMTP_OUT_SERVER parameter

SQL> select value,display_value from v$parameter where upper(name) like '%SMTP%SERVER%';

 

It will show resultant value of SMTP_OUT_SERVER. If shows wrong / null then set / correct with below.

·         Set the SMTP_OUT_SERVER parameter

Suppose our mail server address is 192.168.255.255. You have to check with your OS admin for this smtp mail server hostname/hostname.

SQL> alter system set smtp_out_server='192.168.255.255' scope=both;

System altered

Commit;

 

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

·         Create ACL and privileges: Now create ACLS

First, create an ACL as SYS (or any other user with DBMS_NETWORK_ACL_ADMIN execute granted i.e. SSK), this will hold the privileges. You can add as many privileges as you like to this file, but I would recommend to split privileges in the ACL to specific tasks or users. You must create an ACL with at least one privilege, so let’s start with the ‘connect’ & ‘resolve’ privileges for user SSK.

Create ACL with below

SQL> EXECUTE DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('ssk_utlmail.xml','Allow mail to be send','SSK',TRUE,'connect');

Commit;

 

Grant the connect and resource privilege as below

SQL> EXECUTE DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('ssk_utlmail.xml','SYS',TRUE,'connect');

SQL> EXECUTE DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('ssk_utlmail.xml','SYS',TRUE,'resolve');

SQL> EXECUTE DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('ssk_utlmail.xml','*',25);

SQL> EXECUTE DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl=>'ssk_utlmail.xml',HOST => '192.168.255.255');

Commit;

·         Test whether user SSK is able to send mail or not

SQL>connect SSK/password

Connected

 

SQL> EXECUTE UTL_MAIL.SEND(SENDER=>'sender_email@ssk.com',RECIPIENTS=>'receiver.email@ssk.com', SUBJECT => 'Test Subject', MESSAGE=>'Test Message');

If every step followed then you will receive email from the system. Otherwise send me your error message to guide you.

·         Dictionary tables for ACLS

SQL> SELECT * FROM DBA_NETWORK_ACLS;

SQL> SELECT * FROM DBA_NETWORK_ACL_PRIVILEGES;