UTL_MAIL package in Oracle 10g

UTL_SMTP package is introduced during Oracle 8i release, for the purpose of sending internet emails. But from the programmer perspective, it was bit difficult to use. The Programmer should really understand the details of SMTP (Simple Mail Transfer Protocol). Also it required a more of steps like establishing connection to the e-mail server using UTL_SMTP.OPEN_CONNECTION followed by which UTL_SMTP.HELO procedure begins a mail session by sending the HELO (”hello”) message to the server, then follows the UTL_STMP.MAIL to identify the sender mailbox, UTL_SMTP.RCPT to identify the recipient, and UTL_SMTP.DATA to send the text of the message. The session is terminated with UTL_SMTP.QUIT

Oracle 10g introduces the mailing package UTL_MAIL, which eliminates much of the extra work that the earlier package (UTL_SMTP) required. This new package makes it possible for a PL/SQL programmer to send programmatically composed emails from the database. It requires only the normal mental model of a user of a GUI email client rather than an understanding of the underlying protocol (SMTP) features.

Operation Notes
UTL_MAIL is not installed by default because of the SMTP_OUT_SERVER configuration requirement and the security exposure this involves. Below are the configuration steps to use UTL_MAIL package.
Logon as System DBA

CONN sys/password AS SYSDBA

and perform the below 3 steps.

Step1
The package UTL_MAIL is loaded by running the following scripts:

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

Step2
Grant the following privilege to the Database User or to the PUBLIC as below.

GRANT execute ON utl_mail TO <schema_name>;
(or)
GRANT execute ON utl_mail TO PUBLIC;

Step3
The SMTP_OUT_SERVER parameter must be set to identify the SMTP server in the init.ora rdbms initialization file.

ALTER SYSTEM SET smtp_out_server=’smtp.domain.com’ SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP

Note: The Scope is of 3 types as below

1. memory (for the current Session only)

2. spfile (permanently – but need to restart the server)

3. both

However, if SMTP_OUT_SERVER is not defined, this invokes a default of DB_DOMAIN which is guaranteed to be defined to perform appropriately.

UTL_MAIL Subprogram
Three procedures are available in the UTL_MAIL package which enables us to send an email with or without attachment.

Procedure 1: SEND Procedure
This is a single stored procedure which sends email to the recipients in the list. The syntax is

UTL_MAIL.SEND (
sender IN VARCHAR2 CHARACTER SET ANY_CS,
recipients IN VARCHAR2 CHARACTER SET ANY_CS,
cc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
bcc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
subject IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
message IN VARCHAR2 CHARACTER SET ANY_CS,
mime_type IN VARCHAR2 DEFAULT ‘text/plain; charset=us-ascii‘,
priority IN PLS_INTEGER DEFAULT NULL);

where

Example:

BEGIN
EXECUTE IMMEDIATE ‘ALTER SESSION SET smtp_out_server = 127.0.0.1′;
UTL_MAIL.send(
sender => ‘me@address.com’
,recipients => ‘you@address.com’
,cc => person1@address.com‘,’person2@address.com‘
,bcc => ‘person3@address.com’,‘person4@address.com’
,subject => ‘Test Mail’
,message => ‘Hello World’
,mime_type => ‘text; charset=us-ascii‘
,priority => 3);
END;

Procedure 2: SEND_ATTACH_RAW Procedure
This is yet another procedure available in the UTL_MAIL package using which any raw attachments can be sent in the email. The syntax is

UTL_MAIL.SEND_ATTACH_RAW (
sender IN VARCHAR2 CHARACTER SET ANY_CS,
recipients IN VARCHAR2 CHARACTER SET ANY_CS,
cc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
bcc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
subject IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
message IN VARCHAR2 CHARACTER SET ANY_CS,
mime_type IN VARCHAR2 DEFAULT ‘text/plain; charset=us-ascii‘,
priority IN PLS_INTEGER DEFAULT NULL,
attachment IN RAW,
att_inline IN BOOLEAN DEFAULT TRUE,
att_mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT’application/octet’,
att_filename IN VARCHAR2 DEFAULT NULL);

where

Example:

DECLARE
rfile RAW (32767) := HEXTORAW(‘616262646566C2AA’);
BEGIN
EXECUTE IMMEDIATE ‘ALTER SESSION SET smtp_out_server = 127.0.0.1′;
UTL_MAIL.send(
sender => ‘me@address.com’
,recipients => ‘you@address.com’
,cc => ‘person1@address.com’,‘person2@address.com’
,bcc => ‘person3@address.com’,‘person4@address.com’
,subject => ‘Test Mail’
,message => ‘Hello World’
,mime_type => ‘text; charset=us-ascii‘
,priority => 3
,attachment => rfile
,att_inline => FALSE,
,att_mime_type=> ‘application/octet’
,att_filename => ‘filename.pdf‘);
END;

Where

Example:

DECLARE
rfile VARCHAR2(255) := ‘This is a sample of a VARCHAR2 attachment!’;
BEGIN
EXECUTE IMMEDIATE ‘ALTER SESSION SET smtp_out_server = 127.0.0.1′;
UTL_MAIL.send(
sender => ‘me@address.com’
,recipients => ‘you@address.com’
,cc => ‘person1@address.com’,‘person2@address.com’
,bcc => ‘person3@address.com’,‘person4@address.com’
,subject => ‘Test Mail’
,message => ‘Hello World’
,mime_type => ‘text; charset=us-ascii‘
,priority => 3
,attachment => rfile
,att_inline => FALSE,
,att_mime_type=> ‘application/octet’
,att_filename => ‘filename.pdf‘);
END;