Configure and Send EMAIL from Oracle Database

Configure and Send EMAIL from Oracle Database

           

Applies to:

            Oracle Enterprise Linux – Version: 7

            Oracle Database 11gR2

           

Description:

            How to configure EMAIL from Oracle Database and Send Email from PL/SQL.

Solution:

Following the simple steps to configure EMAIL from Oracle Database:

1.    Connect Oracle Database as sysdba

2.    Install the following two packages:

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

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

3.    To set SMTP parameter:

SQL> show parameter SMTP_OUT_SERVER;

SQL> Alter system set SMTP_OUT_SERVER=<enter your smtp server address>;

SQL> shutdown immediate;

SQL> startup;

SQL> show parameter SMTP_OUT_SERVER;

If the SMTP_OUT_SERVER parameter is set then you go to next step.

4.    Connect Oracle Database as sysdba

5.    SQL>grant execute on UTL_TCP TO <SCHEMA_NAME>;
SQL> grant execute on UTL_SMTP TO <SCHEMA_NAME>;
SQL> grant execute on UTL_MAIL TO <SCHEMA_NAME>;
SQL> grant execute on DBMS_NETWORK_ACL_ADMIN TO <SCHEMA_NAME>;

6.    Connect to schema which is granted

SQL> connect <SCHEMA_NAME>

7.    Begin
UTL_MAIL.SEND (
sender      =>  ‘dba.admin@pakistan.com’,
recipients  =>    ‘abdul.wahid@pakistan.com’,
subject    =>    ‘TEST EMAIL’,
message     =>    ‘Salam, This EMAIL is sent from Oracle Database’);
end;

/

Your comments, especially which will help us improve the functionality, will be greatly appreciatedJ