How to send email from 10g Oracle Database (UTL_MAIL)

Heres a simple solution to send out emails from 10g Database sql prompt.

This solution will be really helpful if the OS utility (mailx, sendmail) is restricted for end users.

Steps to enable Mailing from Database

1. sqlplus '/ as sysdba'
2. @$ORACLE_HOME/rdbms/admin/utlmail.sql
3. @$ORACLE_HOME/rdbms/admin/prvtmail.plb
4. Set smtp_server information in init.ora or spfile.ora
alter system set smtp_out_server = 'SMTP_SERVER_IP_ADDRESS:SMTP_PORT' scope=both;
25 = Default SMTP Port

If instance had been started with spfile

eg: alter system set smtp_out_server = '172.25.90.165:25′ scope=both;

If instance had been started with pfile
alter system set smtp_out_server = '172.25.90.165:25′;
Also make below entry in your initSID.ora

smtp_out_server = '172.25.90.165:25′

Thats It, your database is configured to send emails ….

How to send an email

1. sqlplus '/ as sysdba'
2. exec utl_mail.send(sender => 'Mohammad.AlMasri@americanexpress.com.bh', recipients => 'sanat.basa@americanexpress.com.bh', subject => 'Testing UTL_MAIL Option', message => 'Chaloo theek hai ');
3. Check the inbox of the email id, to verify the email receipt.

To enable other DB users to use this functionality, grant execute permission on UTL_MAIL package.

eg: grant execute on utl_mail to apps;