Tuesday, November 24, 2009

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

9 comments:

  1. Thx, it is very useful.

    Regards
    Babu

    ReplyDelete
  2. Oracle 10g has added over fifty new PL/SQL packages and enhanced many of the existing packages, thus expanding the Oracle DBA's toolkit once again. New capabilities include the ability to transfer files between servers, more robust e-mail features, improved compilation utilities, and character conversion utilities. This article - the final one in this series - delves into several new features presented in new and improved versions of Oracle's utility modules and illustrates their usefulness in some real-world situations.

    8GB SD Karte

    ReplyDelete
  3. Hi , is it possible to send email with multiple attachments ?

    ReplyDelete
  4. Yes, you can send email with multiple attachments.

    Regards
    Satish

    ReplyDelete
  5. Contact us if you need design for your website in affordable price we are offering low rates for your website......


    Buzz App

    ReplyDelete
  6. A good blog. Thanks for sharing the information. It is very useful for my future. keep sharing
    red ball 2 | duck life 2 | happy wheels | Red Ball | Red ball 3 | Flash Games| Tank trouble

    ReplyDelete