Home > Oracle > how to Send mail from oracle

how to Send mail from oracle

Run The following packages

$ORACLE_HOME/rdbms/admin/utlmail.sql
$ORACLE_HOME/rdbms/admin/prvtmail.plb


SQL> connect / as sysdba
Connected.
SQL> @utlmail.sql

Package created.

Synonym created.

SQL>
SQL> @prvtmail.plb

Package created.

Package body created.

Grant succeeded.

Package body created.

No errors.

set the smtp_server information in init.ora or spfile.ora

If the database is started using an spfile


SQL> alter system set smtp_out_server ='11.221.10.89:25' scope=BOTH;

If the database is started using an pfile then enter the following in the init.ora

smtp_out_server = ‘11.221.10.89:25′

That is it!

To send an email


sqlplus ‘/ as sysdba’
sql>  exec utl_mail.send((sender => ‘oracle@nesdb.com’, recipients => ‘mike@desouza.com’, subject => ‘Testing Mail’, message => ‘oracle is working’);

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

SQL>  grant execute on utl_mail to apps;
CREATE OR REPLACE PROCEDURE email_alert AS
msg varchar2(20000) := 'Realm violation occurred for the ALTER TABLE Command Security Policy rule set. The time is: ';
BEGIN
msg := msg||to_char(SYSDATE, 'Day DD MON, YYYY HH24:MI:SS');
UTL_MAIL.SEND (
sender => 'dba@gcom.co.uk',
recipients => 'michael.dsouza@gcom.co.uk',
subject => 'Table modification attempted outside maintenance!',
message => msg);
END email_alert;
/

Advertisements
Categories: Oracle
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: