Email BLOB Attachments From The Database

Recently I was developing an oracle database application and had the requirement to send out an email with an attachment. Initially it sounded pretty easy. I’ll just use the APEX_MAIL package and its all handled for me. But then I remembered, this was not an APEX application I was developing. So then I thought, no problem, I’ll just use the UTL_MAIL package. There is an API to add attachments. But wait, you can only add VARCHAR and RAW attachments and there is a max length of 32K on them. Not very useful if you want to send a multi-megabyte document as an attachment ( ie. a blob). So I had to drop down to an even lower API, UTL_SMTP and handle it all myself. Oh, and the attachment needed to be zipped as well (to save space in the tubes of our interwebs). I found the exercise interesting and thought I would share it with you.

For this example we have a table

create table assets(
  id number primary key,
  name varchar2(100),
  content blob
  mime_type varchar2(100) );

And the code

procedure mail_asset(
  p_asset_id number,
  p_from varchar2,
  p_to varchar2,
  p_subject varchar2,
  p_message varchar2 ) is
--
  l_asset assets%rowtype;
  l_blob blob := to_blob('1');
  l_conn utl_smtp.connection;
  l_raw raw(57);
  l_len integer := 0;
  l_idx integer := 1;
  l_buff_size integer := 57;
  l_boundary varchar2(32) := sys_guid();
  l_attachment_name long;
begin

  -- Connect
  l_conn := utl_smtp.open_connection( 'mail.oracle.com' );
  utl_smtp.helo( l_conn, 'oracle.com' );
  utl_smtp.mail( l_conn, p_from );
  utl_smtp.rcpt( l_conn, p_to );
  utl_smtp.open_data(l_conn);

  -- Header
  utl_smtp.write_data( l_conn, 'From: ' || p_from || utl_tcp.crlf );
  utl_smtp.write_data( l_conn, 'To: ' || p_to || utl_tcp.crlf );
  utl_smtp.write_data( l_conn, 'Subject: ' || p_subject ||
                               utl_tcp.crlf );
  utl_smtp.write_data( l_conn, 'MIME-Version: 1.0' || utl_tcp.crlf );
  utl_smtp.write_data( l_conn, 'Content-Type: multipart/mixed; ' ||
                               utl_tcp.crlf );
  utl_smtp.write_data( l_conn, ' boundary= "' || l_boundary || '"' ||
                               utl_tcp.crlf );
  utl_smtp.write_data( l_conn, utl_tcp.crlf );

  -- Body
  utl_smtp.write_data( l_conn, '--' || l_boundary || utl_tcp.crlf );
  utl_smtp.write_data( l_conn, 'Content-Type: text/plain;' ||
                               utl_tcp.crlf );
  utl_smtp.write_data( l_conn, ' charset=US-ASCII' || utl_tcp.crlf );
  utl_smtp.write_data( l_conn, utl_tcp.crlf );
  utl_smtp.write_data( l_conn, l_message || utl_tcp.crlf );
  utl_smtp.write_data( l_conn, utl_tcp.crlf );

  -- Fetch the asset info
  for c in ( select *
               from assets
              where asset_id = p_asset_id ) loop

    -- Compress if a content and not already zipped.
    if ( c.mime_type != 'application/zip' then
      utl_compress.lz_compress( src => c.content,
                                dst => l_blob );
      l_attachment_name := c.name || '.gz';
    else
      l_blob := c.content;
      l_attachment_name := c.name;
    end if;

    -- Attachment
    utl_smtp.write_data( l_conn, '--' || l_boundary || utl_tcp.crlf );
    utl_smtp.write_data( l_conn, 'Content-Type: application/octet-stream'
                                 || utl_tcp.crlf );
    utl_smtp.write_data( l_conn, 'Content-Disposition: attachment; ' ||
                                 utl_tcp.crlf );
    utl_smtp.write_data( l_conn, ' filename="' || l_attachment_name || '"'
                                 || utl_tcp.crlf );
    utl_smtp.write_data( l_conn, 'Content-Transfer-Encoding: base64' ||
                                 utl_tcp.crlf );
    utl_smtp.write_data( l_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(l_blob);
    while l_idx < l_len loop
      dbms_lob.read( l_blob, l_buff_size, l_idx, l_raw );
      utl_smtp.write_raw_data( l_conn, utl_encode.base64_encode(l_raw) );
      utl_smtp.write_data( l_conn, utl_tcp.crlf );
      l_idx := l_idx + l_buff_size;
    end loop;
    utl_smtp.write_data( l_conn, utl_tcp.crlf );

  end loop;

  -- 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 );

exception
  -- smtp errors, close connection and reraise
  when utl_smtp.transient_error or
       utl_smtp.permanent_error then
    utl_smtp.quit( l_conn );
    raise;

end mail_asset;

Just a few things about the code.

Line 15 – I use sys_guid() to generate a random 32 character string which I use as the boundary marker in the email message. You can use any character string you like.

Lines 20 and 21 – You will need to change to your smtp server.

Line 34 – You will notice that there is a space in front of boundary. That is on purpose. The reason for that is that Content-Type (Line 32) and the boundary could be on the same line like this:

Content-Type: multipart/mixed; boundary=”xxxxxxxxxxxx”

But for formatting sake, I broke it up into two lines, so it needs to be sent to the smtp server like

Content-Type: multipart/mixed;
 boundary=”xxxxxxxxxxxx”
^ (single leading space)

with a leading space on boundary. I also did it on lines 42 and 68.

Line 79 – No you can not do larger reads. Each line of the encoded attachment needs to be 57 bytes.

In my example, I will only get at most 1 attachment because I pass in the asset_id and that is defined as the primary key. But if the query at line 48 was changed to possibly return more than a single row, the code would still work and we would have an email with multiple attachments.