Tuesday, January 11, 2011

Sending binary attachment/images in mail with UTL_SMTP.

This is based on my posting on OTN for sending binary attachment using UTL_SMTP.
Here is my file (images) and DIRECTORY object setups.
C:\>dir *.jpg
 Volume in drive C has no label.
 Volume Serial Number is 6806-ABBD
 
 Directory of C:\
 
08/04/2004  10:30 AM            83,794 Waterlilies.jpg
10/11/2010  05:27 PM           105,542 Winter.jpg
               2 File(s)        189,336 bytes
               0 Dir(s)   8,408,399,872 bytes free
 
C:\>sqlplus / as sysdba
 
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jan 11 17:42:37 2011
 
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
 
SQL> /* I have already created DIRECTORY OBJECT */
SQL> SELECT directory_name,directory_path FROM dba_directories
  2  WHERE directory_name='SAUBHIK';
 
DIRECTORY_NAME
------------------------------
DIRECTORY_PATH
------------------------------------------------------------------------
 
SAUBHIK
C:\
 
 
SQL>

Now my actual code.
SQL> conn scott@ORCLSB
Enter password: *****
Connected.
SQL> DECLARE
  2    /*LOB operation related varriables */
  3    v_src_loc  BFILE := BFILENAME('SAUBHIK', 'Waterlilies.jpg');
  4    l_buffer   RAW(54);
  5    l_amount   BINARY_INTEGER := 54;
  6    l_pos      INTEGER := 1;
  7    l_blob     BLOB := EMPTY_BLOB;
  8    l_blob_len INTEGER;
  9    v_amount   INTEGER;
 10  
 11    /*UTL_SMTP related varriavles. */
 12    v_connection_handle  UTL_SMTP.CONNECTION;
 13    v_from_email_address VARCHAR2(30) := 'aaaa@bb.com';
 14    v_to_email_address   VARCHAR2(30) := 'xxxx@yy.com';
 15    v_smtp_host          VARCHAR2(30) := '9.182.156.144'; --My mail server, replace it with yours.
 16    v_subject            VARCHAR2(30) := 'Your Test Mail';
 17    l_message            VARCHAR2(200) := 'This is test mail using UTL_SMTP';
 18  
 19    /* This send_header procedure is written in the documentation */
 20    PROCEDURE send_header(pi_name IN VARCHAR2, pi_header IN VARCHAR2) AS
 21    BEGIN
 22      UTL_SMTP.WRITE_DATA(v_connection_handle,
 23                          pi_name || ': ' || pi_header || UTL_TCP.CRLF);
 24    END;
 25  
 26  BEGIN
 27    /*Preparing the LOB from file for attachment. */
 28    DBMS_LOB.OPEN(v_src_loc, DBMS_LOB.LOB_READONLY); --Read the file
 29    DBMS_LOB.CREATETEMPORARY(l_blob, TRUE); --Create temporary LOB to store the file.
 30    v_amount := DBMS_LOB.GETLENGTH(v_src_loc); --Amount to store.
 31    DBMS_LOB.LOADFROMFILE(l_blob, v_src_loc, v_amount); -- Loading from file into temporary LOB
 32    l_blob_len := DBMS_LOB.getlength(l_blob);
 33  
 34    /*UTL_SMTP related coding. */
 35    v_connection_handle := UTL_SMTP.OPEN_CONNECTION(host => v_smtp_host);
 36    UTL_SMTP.HELO(v_connection_handle, v_smtp_host);
 37    UTL_SMTP.MAIL(v_connection_handle, v_from_email_address);
 38    UTL_SMTP.RCPT(v_connection_handle, v_to_email_address);
 39    UTL_SMTP.OPEN_DATA(v_connection_handle);
 40    send_header('From', '"Sender" <'>');
 41    send_header('To', '"Recipient" <'>');
 42    send_header('Subject', v_subject);
 43  
 44    --MIME header.
 45    UTL_SMTP.WRITE_DATA(v_connection_handle,
 46                        'MIME-Version: 1.0' || UTL_TCP.CRLF);
 47    UTL_SMTP.WRITE_DATA(v_connection_handle,
 48                        'Content-Type: multipart/mixed; ' || UTL_TCP.CRLF);
 49    UTL_SMTP.WRITE_DATA(v_connection_handle,
 50                        ' boundary= "' || 'SAUBHIK.SECBOUND' || '"' ||
 51                        UTL_TCP.CRLF);
 52    UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
 53  
 54    -- Mail Body
 55    UTL_SMTP.WRITE_DATA(v_connection_handle,
 56                        '--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
 57    UTL_SMTP.WRITE_DATA(v_connection_handle,
 58                        'Content-Type: text/plain;' || UTL_TCP.CRLF);
 59    UTL_SMTP.WRITE_DATA(v_connection_handle,
 60                        ' charset=US-ASCII' || UTL_TCP.CRLF);
 61    UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
 62    UTL_SMTP.WRITE_DATA(v_connection_handle, l_message || UTL_TCP.CRLF);
 63    UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
 64  
 65    -- Mail Attachment
 66    UTL_SMTP.WRITE_DATA(v_connection_handle,
 67                        '--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
 68    UTL_SMTP.WRITE_DATA(v_connection_handle,
 69                        'Content-Type: application/octet-stream' ||
 70                        UTL_TCP.CRLF);
 71    UTL_SMTP.WRITE_DATA(v_connection_handle,
 72                        'Content-Disposition: attachment; ' || UTL_TCP.CRLF);
 73    UTL_SMTP.WRITE_DATA(v_connection_handle,
 74                        ' filename="' || 'Waterlilies.jpg' || '"' || --My filename
 75                        UTL_TCP.CRLF);
 76    UTL_SMTP.WRITE_DATA(v_connection_handle,
 77                        'Content-Transfer-Encoding: base64' || UTL_TCP.CRLF);
 78    UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
 79  /* Writing the BLOL in chunks */
 80    WHILE l_pos < l_blob_len LOOP
 81      DBMS_LOB.READ(l_blob, l_amount, l_pos, l_buffer);
 82      UTL_SMTP.write_raw_data(v_connection_handle,
 83                              UTL_ENCODE.BASE64_ENCODE(l_buffer));
 84      UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
 85      l_buffer := NULL;
 86      l_pos    := l_pos + l_amount;
 87    END LOOP;
 88    UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
 89  
 90    -- Close Email
 91    UTL_SMTP.WRITE_DATA(v_connection_handle,
 92                        '--' || 'SAUBHIK.SECBOUND' || '--' || UTL_TCP.CRLF);
 93    UTL_SMTP.WRITE_DATA(v_connection_handle,
 94                        UTL_TCP.CRLF || '.' || UTL_TCP.CRLF);
 95  
 96    UTL_SMTP.CLOSE_DATA(v_connection_handle);
 97    UTL_SMTP.QUIT(v_connection_handle);
 98    DBMS_LOB.FREETEMPORARY(l_blob);
 99    DBMS_LOB.FILECLOSE(v_src_loc);
100  
101  EXCEPTION
102    WHEN OTHERS THEN
103      UTL_SMTP.QUIT(v_connection_handle);
104      DBMS_LOB.FREETEMPORARY(l_blob);
105      DBMS_LOB.FILECLOSE(v_src_loc);
106      RAISE;
107  END;
108  / 
 
PL/SQL procedure successfully completed.
 
SQL>

6 comments :

  1. Hello. I was able to adapt this to a need for my work, but I wanted to know if there is a way to attach a file directly from a BLOB so I don't have to maintain a directory and file on the server. My preference is to make it completely internal to the database. If you have any suggestions, that would be fantastic!

    Thanks!

    Jeremy

    ReplyDelete
  2. Yes, It is highly possible. BLOB locater is assigned by the statement v_src_loc BFILE := BFILENAME('SAUBHIK', 'Waterlilies.jpg');
    Instead of this you can use a CURSOR to read the BLOB (as locater) from the table and use the rest of the part to send mail.
    Here is one example in my BLOG
    http://saubbane.blogspot.com/2011/02/sending-multiple-attachment-blob-using.html

    ReplyDelete
  3. You rock Dude!! Fixed my attachment over 32k problem!!

    ReplyDelete
  4. hi i am getting scarp when I tried to attach excel file with proper mime type

    ReplyDelete
  5. Worked for me, thank you very much. Great Job!
    Greetings from my Manager as well :)

    ReplyDelete
  6. Pl tel me how to send multiple images in the body of the email?

    ReplyDelete