How to Send an Email From a Oracle 10g Database (UTL_MAIL)

A big improvement arrived as far as sending email when Oracle 10G made it’s debut by creating the UTL_MAIL package.  This package makes it so much easier to send emails plus you can send an email with an attachment.

To Enable the UTL_MAIL Functionality in an Oracle 10g Database, Do the Following:

1.) sqlplus ‘/ as sysdba’

2.) @$ORACLE_HOME/rdbms/admin/utlmail.sql;

3.) @$ORACLE_HOME/rdbms/admin/prvtmail.plb;

4.) add the smtp_server information in the init.ora and spfile by executing

alter system set smtp_out_server = ‘SMTP_SERVER_IP_ADDRESS:SMTP_PORT’ scope=both;

(i.e. alter system set smtp_out_server = ‘192.168.1.10:25′ scope=both; )

5.) make sure the init.ora has the ’smtp_out_server = ‘192.168.1.10:25′ parameter in it.

6.) Grant the user priviledges to use the function by executing

grant execute on utl_mail to user;

7.) That’s it!

To Send an Email using UTL_MAIL:

1.) sqlplus ‘/ as sysdba’

2.) exec utl_mail.send(sender => ‘oracleuser@mydomain.com’, recipients => ‘me@domain.com’, subject => ‘Testing UTL_MAIL Function’, message => ‘Hello World!’);

3.) check the inbox of the recipents email account.

4.) That’s it!  Have Fun.