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.

You can also send email with Multiple attachements using UTL_SMTP, follow the steps.

Step 1: Creating the Email Attachments table
SQL> CREATE TABLE email_attachments(id_pk INTEGER PRIMARY KEY,fname VARCHAR2(50),image BLOB);

Table created.

Step 2: Use below procedure to load attachments into the table
CREATE OR REPLACE PROCEDURE load_file(pi_id IN INTEGER, pfname IN VARCHAR2) IS
src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;
BEGIN
src_file := bfilename('TEMP', pfname);

INSERT INTO email_attachments (id_pk,fname,image)
VALUES (pi_id,pfname, EMPTY_BLOB())
RETURNING image INTO dst_file;

DBMS_LOB.OPEN(src_file, dbms_lob.file_readonly);
lgh_file := dbms_lob.getlength(src_file);
DBMS_LOB.LOADFROMFILE(dst_file, src_file, lgh_file);
dbms_lob.close(src_file);
COMMIT;
END load_file;
/

Step 3: Loading some attachments using the procedure
SQL> EXECUTE load_file(1,'attachment1.zip');

Step 4: Query email_attachments table to check the attachments
SQL> SELECT id_pk,fname,DBMS_LOB.getlength(image) image_length FROM email_attachments;

Step 5: Use below procedure to send emails with multiple attachemnts

DECLARE
/*LOB operation related varriables */
l_buffer RAW(54);
l_amount BINARY_INTEGER := 54;
l_pos INTEGER := 1;
l_blob BLOB := EMPTY_BLOB;
l_blob_len INTEGER;
v_amount INTEGER;
v_fname VARCHAR2(50);

/* This cursor will pick the images for attachment in the email_attachments table. */
CURSOR img_cur IS SELECT fname,image FROM email_attachments;

/*UTL_SMTP related varriavles. */
v_connection_handle UTL_SMTP.CONNECTION;
v_from_email_address VARCHAR2(30) := 'xxxxxxx@xxx.com';
v_to_email_address VARCHAR2(30) := 'xxxxxxx@xxxxx.com';
v_smtp_host VARCHAR2(30) := 'xxx.xxx.xxx.xxx'; --Mail server
v_subject VARCHAR2(30) := 'Your Test Mail';
l_message VARCHAR2(200) := 'This is test mail using UTL_SMTP';

/* This send_header procedure is written in the documentation */
PROCEDURE send_header(pi_name IN VARCHAR2, pi_header IN VARCHAR2) AS
BEGIN
UTL_SMTP.WRITE_DATA(v_connection_handle,pi_name || ': ' || pi_header || UTL_TCP.CRLF);
END;

BEGIN

/*UTL_SMTP related coding. */
v_connection_handle := UTL_SMTP.OPEN_CONNECTION(host => v_smtp_host);
UTL_SMTP.HELO(v_connection_handle, v_smtp_host);
UTL_SMTP.MAIL(v_connection_handle, v_from_email_address);
UTL_SMTP.RCPT(v_connection_handle, v_to_email_address);
UTL_SMTP.OPEN_DATA(v_connection_handle);
send_header('From', '"Sender" <'>');
send_header('To', '"Recipient" <'>');
send_header('Subject', v_subject);

--Mail header
UTL_SMTP.WRITE_DATA(v_connection_handle,
'MIME-Version: 1.0' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
'Content-Type: multipart/mixed; ' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
' boundary= "' || 'SAUBHIK.SECBOUND' || '"' ||
UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);

-- Mail Body
UTL_SMTP.WRITE_DATA(v_connection_handle,
'--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
'Content-Type: text/plain;' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
' charset=US-ASCII' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle, l_message || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);

/* Opening the cursor to loop through the attachments*/
OPEN img_cur;
LOOP
FETCH img_cur INTO v_fname,l_blob;
EXIT WHEN img_cur%NOTFOUND;

-- Mail Attachment
UTL_SMTP.WRITE_DATA(v_connection_handle,
'--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
'Content-Type: application/octet-stream' ||
UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
'Content-Disposition: attachment; ' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
' filename="' || v_fname || '"' || --My filename
UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
'Content-Transfer-Encoding: base64' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);

/* Re-initializing the varriables. This is very important*/
l_buffer := NULL;
l_pos :=1;
l_amount:=54;
/* Writing the BLOL in chunks */
l_blob_len := DBMS_LOB.getlength(l_blob);
WHILE l_pos < l_blob_len LOOP
DBMS_LOB.READ(l_blob, l_amount, l_pos, l_buffer);
UTL_SMTP.write_raw_data(v_connection_handle,
UTL_ENCODE.BASE64_ENCODE(l_buffer));
UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
l_buffer := NULL;
l_pos := l_pos + l_amount;
END LOOP;
UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
l_blob:=EMPTY_BLOB;
END loop; --End cursor loop.

CLOSE img_cur; --Close the cursor.

-- Close Email
UTL_SMTP.WRITE_DATA(v_connection_handle,
'--' || 'SAUBHIK.SECBOUND' || '--' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
UTL_TCP.CRLF || '.' || UTL_TCP.CRLF);

UTL_SMTP.CLOSE_DATA(v_connection_handle);
UTL_SMTP.QUIT(v_connection_handle);
DBMS_LOB.FREETEMPORARY(l_blob);

EXCEPTION
WHEN OTHERS THEN
UTL_SMTP.QUIT(v_connection_handle);
DBMS_LOB.FREETEMPORARY(l_blob);
RAISE;
END;
/

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