Send EMAIL using UTL_MAIL in Oracle 10g

The UTL_MAIL package is introduced in Oracle 10g and it is easier to use when compared to UTL_SMTP. In order to use Oracle UTL_MAIL package you need to set a new init.ora parameter “SMTP_OUT_SERVER”, set to your outgoing mailserver.

Follow the simple steps to send an email using UTL_MAIL package

Step 1: Install UTL_MAIL package
To install the UTL_MAIL package, run the below files as user "SYS"
$ORACLE_HOME/rdbms/admin/utlmail.sql
$ORACLE_HOME/rdbms/admin/prvtmail.plb

Step 2: Grant permissions
Grants the execute permission on UTL_MAIL privilege to PUBLIC or the user which will use the package. Run the beow command as user “SYS”
SQL> GRANT EXECUTE ON UTL_MAIL TO PUBLIC;
-or-
SQL> GRANT EXECUTE ON UTL_MAIL TO ;

Step 3: Set SMTP_OUT_SERVER parameter
SQL> ALTER SYSTEM SET smtp_out_server=’smtp.domain.com’ SCOPE=both;

Step 4: Create procedure to send email
CREATE OR REPLACE PROCEDURE test_email AS
BEGIN
UTL_MAIL.SEND(sender => 'xxx@oracleracexpert.com',
recipients => 'xxx@oracleracexpert.com',
cc => 'xxx@oracleracexpert.com',
bcc => 'xxx@oracleracexpert.com',
subject => 'Test Mail',
message => 'Hi, This is just a test mail');
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'The following error has occured:' ||sqlerrm);
END;

SQL> exec test_email;

Step 5: Send email using UTL_MAIL with attachments
You must set UTL_FILE_DIR to a directory, where the attachment files exists

CREATE OR REPLACE PROCEDURE test_email_attach AS
BEGIN
UTL_MAIL.SEND_ATTACH_VARCHAR2(sender => 'xxx@oracleracexpert.com',
recipients => 'xxx@oracleracexpert.com',
cc => 'xxx@oracleracexpert.com',
bcc => 'xxx@oracleracexpert.com',
subject => 'Test Mail',
message => 'Hi, This is just a test mail'
attachment => ‘text’
att_filename => ‘test_attach.txt’);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'The following error has occured: '
sqlerrm);
END;

Please note that with att_inline you can specify, whether the attachment is viewable inline with the message body or not.

You can also use the below parameters
attachment - A text attachment.
priority - The message priority, the default is NULL.
mime_type - The mime type of the message, default is ‘text/plain; charset=us-ascii‘.
att_inline - Specifies whether the attachment is viewable inline with the message body, default is TRUE.
att_mime_type - The mime type of the attachment, default is ‘text/plain; charset=us-ascii‘.
att_filename - The string specifying a filename containing the attachment, default is NULL.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com