How to send email from 11g 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;
restart database ;
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 => 'oraclepitstop@wordpress.com', recipients => 'oraclepitstop@wordpress.com', subject => 'Testing UTL_MAIL Option', message => 'blah blah blah');
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;
Happy Mailing !!!
cheers,
OraclePitStop.