Sending blob attachments in e-mail with utl_smtp

Last time, I showed how to send an HTML-formatted e-mail from an Oracle PL/SQL application using utl_smtp. I promised I would extend the package a little with a second method that allows you to send Binary Large OBject (blob) attachments in the e-mails. The most common application where I work is sending PDFs that are stored in the database to various recipients.

First, you have to add the method into the package spec so that other packages can use it:

  procedure send_blob (
      p_sender varchar2,
      p_recipient varchar2,
      p_subject varchar2,
      p_filename varchar2,
      p_blob blob);

Here is the actual method you need to insert into the package body:

  procedure send_blob (
      p_sender varchar2,
      p_recipient varchar2,
      p_subject varchar2,
      p_filename varchar2,
      p_blob blob) is     

    c utl_smtp.connection;
    v_raw raw(57);
    v_length integer := 0;
    v_buffer_size integer := 57;
    v_offset integer := 1;     

  begin     

    common(p_sender, p_recipient, p_subject, c); 

    utl_smtp.write_data( c, 'Content-Disposition: attachment; filename="' || p_filename || '"' || utl_tcp.crlf);
    utl_smtp.write_data( c, 'Content-Transfer-Encoding: base64' || utl_tcp.crlf );
    utl_smtp.write_data( c, utl_tcp.crlf );     

    v_length := dbms_lob.getlength(p_blob);     

    <<while_loop>>
    while v_offset < v_length loop
      dbms_lob.read( p_blob, v_buffer_size, v_offset, v_raw );
      utl_smtp.write_raw_data( c, utl_encode.base64_encode(v_raw) );
      utl_smtp.write_data( c, utl_tcp.crlf );
      v_offset := v_offset + v_buffer_size;
    end loop while_loop;

    utl_smtp.write_data( c, utl_tcp.crlf );
    utl_smtp.close_data( c );
    utl_smtp.quit( c );

  exception
    when utl_smtp.transient_error or utl_smtp.permanent_error then
      utl_smtp.quit( c );
      raise;
    when others then
    raise;
  end send_blob;

Notes about the code

You have to pass a filename along with the actual blob. You also need to make sure it has the right extension so that the e-mail client on the other end will be able to open it up properly. That is, if the blob is a PDF, then send a filename like abc.pdf.

You'll notice that this method doesn't send a body. I'm trying to keep it simple to focus on the actual sending of the blob. If your application requires it, you should be able to incorporate code from the other method to include an e-mail body as well.

The weird while loop in the method

    <<while_loop>>
    while v_offset < v_length loop
      dbms_lob.read( p_blob, v_buffer_size, v_offset, v_raw );
      utl_smtp.write_raw_data( c, utl_encode.base64_encode(v_raw) );
      utl_smtp.write_data( c, utl_tcp.crlf );
      v_offset := v_offset + v_buffer_size;
    end loop while_loop;

The buffer size must be 57 for utl_encode to work, which is why we loop thru the blob and chunk it into 57-byte pieces. This process converts the blob into a raw binary format and then sends the 57 bytes into the e-mail stream. It also adds an end-of-line after each 57-byte chunk.

Once you have the basics figured out in utl_smtp, it's not a huge deal to include attachments.