Wednesday, February 23, 2011

Sending Multiple attachment (BLOB) using UTL_SMTP.

This is an example of sending multiple attachment through PL/SQL using UTL_SMTP. This code is posted in OTN by me.

SQL> /* My database version */
SQL> SELECT * FROM v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> /* Creating the image store table */
SQL> CREATE TABLE image_store(id_pk INTEGER PRIMARY KEY,fname VARCHAR2(50),image BLOB);

Table created.

SQL> /* Procedure to load images in the table */
SQL> CREATE OR REPLACE PROCEDURE load_file(pi_id IN INTEGER, pfname IN VARCHAR2) IS
  2    src_file BFILE;
  3    dst_file BLOB;
  4    lgh_file BINARY_INTEGER;
  5  BEGIN
  6    src_file := bfilename('TEMP', pfname);
  7 
  8    INSERT INTO image_store
  9      (id_pk,fname,image)
 10    VALUES
 11      (pi_id,pfname, EMPTY_BLOB())
 12    RETURNING image INTO dst_file;
 13 
 14    DBMS_LOB.OPEN(src_file, dbms_lob.file_readonly);
 15    lgh_file := dbms_lob.getlength(src_file);
 16    DBMS_LOB.LOADFROMFILE(dst_file, src_file, lgh_file);
 17    dbms_lob.close(src_file);
 18    COMMIT;
 19  END load_file;
 20  /

Procedure created.

SQL> /* Loading some images in the table*/
SQL> EXECUTE load_file(1,'Waterlilies.jpg');

PL/SQL procedure successfully completed.

SQL> EXECUTE load_file(2,'Winter.jpg');

PL/SQL procedure successfully completed.

SQL> /* Checking the rows inserted*/
SQL> SELECT id_pk,fname,DBMS_LOB.getlength(image) image_length
  2  FROM image_store;

     ID_PK FNAME                                              IMAGE_LENGTH
---------- -------------------------------------------------- ------------
         1 Waterlilies.jpg                                           83794
         2 Winter.jpg                                               105542

SQL> DECLARE
  2    /*LOB operation related varriables */
  3    l_buffer   RAW(54);
  4    l_amount   BINARY_INTEGER := 54;
  5    l_pos      INTEGER := 1;
  6    l_blob     BLOB := EMPTY_BLOB;
  7    l_blob_len INTEGER;
  8    v_amount   INTEGER;
  9    v_fname VARCHAR2(50);
 10   
 11  /* This cursor will pick the two images for attachment in the mail. */ 
 12    CURSOR img_cur IS SELECT fname,image
 13    FROM image_store;
 14 
 15    /*UTL_SMTP related varriavles. */
 16    v_connection_handle  UTL_SMTP.CONNECTION;
 17    v_from_email_address VARCHAR2(30) := 'xx@xxxx.com';
 18    v_to_email_address   VARCHAR2(30) := 'xxxxxx@xxxxxx.com';
 19    v_smtp_host          VARCHAR2(30) := 'x.xxx.xxx.xxx'; --My mail server, replace it with yours
.
 20    v_subject            VARCHAR2(30) := 'Your Test Mail';
 21    l_message            VARCHAR2(200) := 'This is test mail using UTL_SMTP';
 22 
 23    /* This send_header procedure is written in the documentation */
 24    PROCEDURE send_header(pi_name IN VARCHAR2, pi_header IN VARCHAR2) AS
 25    BEGIN
 26      UTL_SMTP.WRITE_DATA(v_connection_handle,
 27                          pi_name || ': ' || pi_header || UTL_TCP.CRLF);
 28    END;
 29 
 30  BEGIN
 31 
 32    /*UTL_SMTP related coding. */
 33    v_connection_handle := UTL_SMTP.OPEN_CONNECTION(host => v_smtp_host);
 34    UTL_SMTP.HELO(v_connection_handle, v_smtp_host);
 35    UTL_SMTP.MAIL(v_connection_handle, v_from_email_address);
 36    UTL_SMTP.RCPT(v_connection_handle, v_to_email_address);
 37    UTL_SMTP.OPEN_DATA(v_connection_handle);
 38    send_header('From', '"Sender" <' || v_from_email_address || '>');
 39    send_header('To', '"Recipient" <' || v_to_email_address || '>');
 40    send_header('Subject', v_subject);
 41 
 42    --MIME header.
 43    UTL_SMTP.WRITE_DATA(v_connection_handle,
 44                        'MIME-Version: 1.0' || UTL_TCP.CRLF);
 45    UTL_SMTP.WRITE_DATA(v_connection_handle,
 46                        'Content-Type: multipart/mixed; ' || UTL_TCP.CRLF);
 47    UTL_SMTP.WRITE_DATA(v_connection_handle,
 48                        ' boundary= "' || 'SAUBHIK.SECBOUND' || '"' ||
 49                        UTL_TCP.CRLF);
 50    UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
 51 
 52    -- Mail Body
 53    UTL_SMTP.WRITE_DATA(v_connection_handle,
 54                        '--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
 55    UTL_SMTP.WRITE_DATA(v_connection_handle,
 56                        'Content-Type: text/plain;' || UTL_TCP.CRLF);
 57    UTL_SMTP.WRITE_DATA(v_connection_handle,
 58                        ' charset=US-ASCII' || UTL_TCP.CRLF);
 59    UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
 60    UTL_SMTP.WRITE_DATA(v_connection_handle, l_message || UTL_TCP.CRLF);
 61    UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
 62 
 63  /* Opening the cursor to loop through the images*/
 64   OPEN img_cur;
 65    LOOP
 66    FETCH img_cur INTO v_fname,l_blob;
 67    EXIT WHEN img_cur%NOTFOUND;
 68 
 69    -- Mail Attachment
 70    UTL_SMTP.WRITE_DATA(v_connection_handle,
 71                        '--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
 72    UTL_SMTP.WRITE_DATA(v_connection_handle,
 73                        'Content-Type: application/octet-stream' ||
 74                        UTL_TCP.CRLF);
 75    UTL_SMTP.WRITE_DATA(v_connection_handle,
 76                        'Content-Disposition: attachment; ' || UTL_TCP.CRLF);
 77    UTL_SMTP.WRITE_DATA(v_connection_handle,
 78                        ' filename="' || v_fname || '"' || --My filename
 79                        UTL_TCP.CRLF);
 80    UTL_SMTP.WRITE_DATA(v_connection_handle,
 81                        'Content-Transfer-Encoding: base64' || UTL_TCP.CRLF);
 82    UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
 83  
 84   /* Re-initializing the varriables. This is very important*/
 85      l_buffer := NULL;
 86      l_pos    :=1;
 87      l_amount:=54;
 88    /* Writing the BLOL in chunks */
 89      l_blob_len := DBMS_LOB.getlength(l_blob);
 90  WHILE l_pos < l_blob_len LOOP
 91      DBMS_LOB.READ(l_blob, l_amount, l_pos, l_buffer);
 92      UTL_SMTP.write_raw_data(v_connection_handle,
 93                              UTL_ENCODE.BASE64_ENCODE(l_buffer));
 94      UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
 95      l_buffer := NULL;
 96      l_pos    := l_pos + l_amount;
 97    END LOOP;
 98       UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
 99         l_blob:=EMPTY_BLOB;
100   END loop; --End cursor loop.
101  
102   CLOSE img_cur; --Close the cursor.
103 
104    -- Close Email
105    UTL_SMTP.WRITE_DATA(v_connection_handle,
106                        '--' || 'SAUBHIK.SECBOUND' || '--' || UTL_TCP.CRLF);
107    UTL_SMTP.WRITE_DATA(v_connection_handle,
108                        UTL_TCP.CRLF || '.' || UTL_TCP.CRLF);
109 
110    UTL_SMTP.CLOSE_DATA(v_connection_handle);
111    UTL_SMTP.QUIT(v_connection_handle);
112    DBMS_LOB.FREETEMPORARY(l_blob);
113 
114  EXCEPTION
115    WHEN OTHERS THEN
116      UTL_SMTP.QUIT(v_connection_handle);
117      DBMS_LOB.FREETEMPORARY(l_blob);
118      RAISE;
119  END;
120  /

2 comments:

  1. i want to send a pdf attachment in mail using utl_smtp ..

    i am using oracle 11g.. sql query output will be the content

    of the pdf. please help regarding this...

    ReplyDelete
  2. It was awesome thanks ,thank you very much........

    N R Pradeep

    ReplyDelete