Steps to Configure the UTL Mail in Oracle Database

Steps to Configure the UTL Mail in Oracle 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

How to send an email

1. sqlplus ‘/ as sysdba’
2. exec utl_mail.send(sender => 'oraclepitstop@wordpress.com', recipients => 'oraclepitstop@wordpress.com', subject => 'Test Mail', message => 'Test Mail');
3. Check the email id, to verify the email receipt.

To access by other schema
grant execute permission on UTL_MAIL package

---------------------------------------------------------

About Disclaimer
How To Send Email From 10g Oracle Database (UTL_MAIL)
Posted on April 24, 2008. Filed under: Configuration | Tags: 10g, email, mail, mailx, sendmail, utl_mail |

Here is 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 => ‘varun.yadav@xxx.com’, recipients => ‘varun.yadav@xxxx.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;