Tuesday, July 24, 2012

UTL_MAIL Or send email from Oracle Database


-->
Steps to enable Mailing from Database:
1. sqlplus ‘/ as sysdba’
$ORACLE_HOME/rdbms/admin/utlmail.sql
$ORACLE_HOME/rdbms/admin/utlsmtp.sql
$ORACLE_HOME/rdbms/admin/prvtmail.plb
  SQL> GRANT EXECUTE ON utl_smtp TO PUBLIC;
 
4. Set smtp_server information in init.ora or spfile.ora like the following you have to change with right configuration for yourself :
alter system set smtp_out_server = ‘SMTP_SERVER_IP_ADDRESS:SMTP_PORT’ scope=both;
Note : 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;
Thats It, your database is configured to send emails ….


How to send an email

1. sqlplus ‘/ as sysdba’
2. exec utl_mail.send((sender => ‘omustafa@savvytek.com’, recipients => ‘omustafa@savvytek.com’, subject => ‘database alert’, message => ‘database is corrputed’);
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 omustafa;


Enjoy 
osama mustafa





6 comments:

  1. I like your blog really.....love it Great information!

    ReplyDelete
  2. Hi
    why i did'nt found the full script utlmail.sql on server ? can you transfer it to me
    Regards
    Jamel

    ReplyDelete
    Replies
    1. Which Version you are using ? Send me your E-mail Please

      Delete