PL/SQL send email with attachment?

Question

we have a table with files saved as BLOB

I write a code that email these files as an attachment!

everything works fine so far, but the files (EXCEL,PDF, ... what ever) are not readable by the programs, only text files and excel will open but after some error message, where PDFs all not be opened at all!

here is the part of the code in question!

utl_smtp.write_data( l_connection, '--'|| l_boundary || utl_tcp.crlf);
utl_smtp.write_data( l_connection, 'Content-Type: application/octet-stream' || utl_tcp.crlf);
utl_smtp.write_data( l_connection, 'Content-Disposition: attachment; filename="' || V_NAME || '"' || utl_tcp.crlf);
utl_smtp.write_data( l_connection, 'Content-Transfer-Encoding: base64' || utl_tcp.crlf );
utl_smtp.write_data( l_connection, utl_tcp.crlf );   

v_length := dbms_lob.getlength(V_BLOB_CONTENT);     

while v_offset < v_length loop
       dbms_lob.read( V(i).BLOB_CONTENT, v_buffer_size, v_offset, v_raw );
       utl_smtp.write_raw_data( l_connection, utl_encode.base64_encode(v_raw) );
       utl_smtp.write_data( l_connection, utl_tcp.crlf );
       v_offset := v_offset + v_buffer_size;
end loop while_loop;     

utl_smtp.write_data( l_connection, utl_tcp.crlf );

any suggestions?

Answer 1

here is a procedure that I use to do just that

  PROCEDURE StreamAttachmentToConn( p_conn       IN OUT utl_smtp.connection
                                   ,p_boundary   IN raw
                                   ,p_FileName   IN VARCHAR2
                                   ,p_FileData   IN BLOB) PARALLEL_ENABLE
  AS
      l_len       integer := 0 ;
      l_idx       integer := 1 ;
      l_buff_size integer := 57 ;
      l_raw       raw(57) ;
  BEGIN

              -- Attachment
            utl_smtp.write_data( p_conn, '--' || p_boundary || utl_tcp.crlf );
            utl_smtp.write_data( p_conn, 'Content-Type: application/octet-stream' || utl_tcp.crlf );
            utl_smtp.write_data( p_conn, 'Content-Disposition: attachment; ' || utl_tcp.crlf );
            utl_smtp.write_data( p_conn, ' filename="' || p_FileName || '"' || utl_tcp.crlf );
            utl_smtp.write_data( p_conn, 'Content-Transfer-Encoding: base64' || utl_tcp.crlf );
            utl_smtp.write_data( p_conn, utl_tcp.crlf );
            -- Loop through the blob
            -- chuck it up into 57-byte pieces
            -- and base64 encode it and write it into the mail buffer
            l_len := dbms_lob.getlength(p_FileData);

            -- force reinit on this may change
            l_buff_size := 57 ;
            l_idx := 1;

            while l_idx < l_len loop
              dbms_lob.read( p_FileData , l_buff_size, l_idx, l_raw );

              utl_smtp.write_raw_data( p_conn, utl_encode.base64_encode(l_raw) );

              utl_smtp.write_data( p_conn, utl_tcp.crlf );

              l_idx := l_idx + l_buff_size;
            end loop;
  END StreamAttachmentToConn;

I use this to add multiple attachments to a single email. works like a champ for me.

one thing, the p_boundary is passed in as

l_boundary raw(32) := sys_guid();

I see that you already have an l_boundary and that is what you should use.

this is based on http://christopherbeck.wordpress.com/category/plsql/ as well as http://www.oracle-base.com/articles/misc/EmailFromOraclePLSQL.php#attachment .

then in your code, just pass in your smtp connection, the l_boundary (ie RAW sys_guid or whatever you are using, the name of the file (as it will appear on the email attachment), and the BLOB.

---

*EDIT --> additional information *

---

Ours are the same with the assumption of:

--> assume v_offset starts with 1 --> v_buffer_size assume 57

But I have noticed that there is an order that you must follow to get it to work (notably, put the attachments next to the END after body!!!

1. Connect LOGIC
2. add all of the recipients
3. Header LOGIC :"FROM", Subject
4. Body logic (body text etc)
5. Attachments
6. then after the attachments close the email (this is specifically what I have after my attachments:

---

utl_smtp.write_data( l_conn, utl_tcp.crlf );

-- Close Email
utl_smtp.write_data( l_conn, '--' || l_boundary || '--' || utl_tcp.crlf );
utl_smtp.write_data( l_conn, utl_tcp.crlf || '.' || utl_tcp.crlf );
utl_smtp.close_data( l_conn );
utl_smtp.quit( l_conn );