Send emails with UTL_MAIL package in 10g

UTL_MAIL:
UTL_MAIL is a wrapper, which internally uses UTL_TCP and UTL_SMTP

UTL_MAIL package is much easier to use than the UTL_SMTP package.

1) The package is loaded by running the following scripts:

    CONN sys/password AS SYSDBA
    @$ORACLE_HOME/rdbms/admin/utlmail.sql
    @$ORACLE_HOME/rdbms/admin/prvtmail.plb

2) Check instance is running on pfile or spfile.

Pfile:

Shutdown the applications and database

Edit the init parameter file with below values

smtp_out_server=’mail.domain.com:25′

Start the database and applications.

Spfile:

CONN sys/password AS SYSDBA
ALTER SYSTEM SET smtp_out_server = ‘mail.domain.com:25’ SCOPE=BOTH;

Note: Check instance is running on pfile or spfile.

Sql> show parameter spfile;

If its returns value, it’s running on Spfile otherwise instance using pfile

3) Grants the execute on UTL_MAIL privilege to PUBLIC or Specific USER

GRANT EXECUTE ON UTL_MAIL TO PUBLIC;

4) Verify if you are able to send email

Replace values specific to your instance

BEGIN

UTL_MAIL.send(sender => ‘dbsmgrtst@testserv1.doyensys.com’,

recipients => ‘xyz@staging.doyensys.com’,

cc => ‘abc@staging.doyensys.com’,

bcc => ‘test@staging.doyensys.com’,

subject => ‘UTL_MAIL Test’,

message => ‘If you get this message utl_mail is working..’);

END;