Home > Oracle, PL/SQL, SQL > Email BLOB Attachments From The Database

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.

About these ads
  1. John
    December 1, 2009 at 7:34 pm | #1

    Why not use javamail? Would reduce the code significantly. Expert One on One has an example on page 1236 about sending a BLOB – I use it for sending external files off our file system using a slightly different java procedure than the BLOB one, but its miles ahead of utl_smtp in my opinion.

    • Christopher Beck
      December 1, 2009 at 8:25 pm | #2

      I choose to use what was already in the database and not install a third party mail package. ( Can I still claim Javamail is a third party package? *scratches head* ).

  2. ano
    December 1, 2009 at 9:43 pm | #3

    Hopefully you were aware of this before you started coding:

    http://www.oracle.com/technology/sample_code/tech/pl_sql/htdocs/Utl_Smtp_Sample.html

    Doesn’t cover compression of the files like your code, though, although that is easily added.

    • Christopher Beck
      December 1, 2009 at 10:48 pm | #4

      I was not aware of that specific code, but I have used UTL_SMTP in the past ( before UTL_MAIL and APEX_MAIL were around. ) I just dusted of some old code, reworked it a bit and that was that.

  3. jim
    December 3, 2009 at 11:23 pm | #5

    Awesome. Thanks mate.

  4. SMIT
    December 14, 2009 at 11:22 am | #6

    Hi,
    I had tried this code . eighter message body or attached file is working wih this .
    I need attach blob with message body .
    Please help. I am working in oracle 10g

    • Christopher Beck
      December 14, 2009 at 12:23 pm | #7

      You will have to give me a bit more information than its not working for me to help.

  5. Helena
    January 8, 2010 at 1:28 am | #8

    hi Chris, I am trying to use your code to attach a large csv file created dynamicaly from a query and loop within the procedure, do you think your code would work with some changes?
    I am using another UTL_SMTP code that works already with a certain ammount of data, but it is now hitting the buffer with the data growth so i need something more flexible…
    Tkx…any help is appreciated

    • Christopher Beck
      January 8, 2010 at 6:51 am | #9

      It should be quite easy to modify this code to handle attaching whatever type of file. The trick is defining the boundary properly. If you plan on zipping the CSV file then how I have the the boundary defined should work for you. If you plan on sending the CSV as a text file then I think you will want to define it as so:

      ThisIsTheLongStringThatDefinesTheDifferntBoundariesOfMyEamilMessage
      Content-Type: text/plain; name=”MyCSVFile.csv”
      Content-Transfer-Encoding: 7bit
      Content-Disposition: attachment; filename=”MyCSVFile.csv”

      [Text of the csv file goes here]

      I have not tested this myself, but I think it should work. Let me know how it goes.

  6. Greig
    March 4, 2010 at 1:38 pm | #10

    Wow, thanks Christopher. Been struggling with including both an attachment and email body text for a while, could get one but not the other. This solution works a treat for me.

    Much appreciated.

    • Christopher Beck
      March 4, 2010 at 2:01 pm | #11

      No problem. Glad to help. I was actually just updating this code for another project I am working on. Emails to include multiple attachments as well as both HTML and text versions of the message.

  7. Perry
    April 14, 2010 at 4:47 pm | #12

    Chris,

    I genuflect :)

    I have been trying to figure this out for a couple days. I was very frustrated because I couldnt get the BODY message into my emails (while sending pdf’s from BLOB’s) and the project was sinking. I found you’re solution here and it works … FINALLY! Just wanted to say thanks and great job!

    Perry

    • Christopher Beck
      April 14, 2010 at 5:32 pm | #13

      Thanks! Glad I could help. But the genuflect was a bit over the top. ;-) I’ll start to get a big ego with comments like yours.

  8. Sudhir
    May 19, 2010 at 12:18 pm | #14

    Thanks a Ton …

  9. John
    July 20, 2010 at 1:07 pm | #15

    Hi Chris, This works great, much appreciated. Question …

    I have a long text in the message body (about 2500 characters). Every 900 characters (about, its not consistent) the message gets a random “!” inserted. Its bizarre and unexplained, for example, the text I send would say “…. account last year, the money …” but in the resulting message it reads “…. account last year, t!he money”

    Any thoughts would be appreciated, I am miffed …. Thanks

    • Christopher Beck
      July 20, 2010 at 4:34 pm | #16

      Not 100% sure but you may want to look into the charset attribute set on line 42. That may have something to do with it if your database is using say UTF-8 charset? Just a thought.

  10. John
    July 22, 2010 at 2:18 pm | #17

    Thanks, I found – for some reason inserting a chr(10) randomly, fixes the problem. Must have something to do with the character recongnition.

  11. Ales
    July 26, 2010 at 4:47 pm | #18

    Hi Chris, good work! I would note, however, that if you want to use multiple attachments, you would have to adjust not only the line 48, but also the line 79. That’s because the value of l_buff_size might be changed at the end of the reading of the first attachment, so you need to set the correct value (i.e. 57) again. You would send multiple files as you write, but the file might not be send correctly (that’s actually what happened to me). Or did missed something?

    • Christopher Beck
      July 26, 2010 at 7:58 pm | #19

      Appears you are correct. I should have just initialized l_buff_size prior to looping at line 78 instead of in the declaration section.

  12. Ravi
    January 13, 2011 at 4:37 am | #20

    Hi Chris, Gr8 article. I am facing same problem as Ales above. The second attachment is getting corrupted. Tried putting l_buff_size := 57 at line#78 but didn’t help.

  13. Tom
    March 9, 2011 at 7:52 pm | #21

    Great write-up, thank you. I had been using a modified version of the sample code that comment #3 linked to, but we ran into trouble with attachments when our infrastructure was upgraded to Exchange 2007. Your code provided excellent troubleshooting help. Good work.

    • Christopher Beck
      March 9, 2011 at 8:03 pm | #22

      Thanks! Glad you found it useful.

  14. Tony
    September 14, 2011 at 5:07 pm | #23

    One thing to note here is that if the asset were to result in more than one document, then you would need to reset the index value (l_idx := 1;) inside the loop. Otherwise the second document starts getting chunked up wherever the previous document ended, likely resulting in a blank document.

    • Christopher Beck
      September 14, 2011 at 11:26 pm | #24

      Nice catch. Thanks.

  15. September 20, 2011 at 7:43 am | #25

    I’ve been surfing on-line more than 3 hours nowadays, yet I by no means found any interesting article like yours. It is pretty value sufficient for me. In my view, if all web owners and bloggers made just right content material as you probably did, the internet will be much more useful than ever before.

  16. September 23, 2011 at 9:45 am | #26

    Its like you read my thoughts! You seem to understand so much about this, such as you wrote the book in it or something. I feel that you simply could do with some percent to power the message home a little bit, however instead of that, this is great blog. An excellent read. I’ll definitely be back.

  17. Ola Adolfsson
    October 10, 2011 at 12:22 pm | #27

    Hello,

    Is it possible to send two attachments in the same mail?

    Regards Ola

  18. Ola Adolfsson
    October 10, 2011 at 12:46 pm | #28

    Never minds. I solved it by reading the comments again. Thanks everyone and specially the writer :)

    Regards Ola

  19. nayana
    December 22, 2011 at 7:35 am | #29

    Thank you so much… I had tried lot of procedures but this worked as charm. An excellant article. You have saved so much of time.thanks again :)

  20. Emir
    August 2, 2012 at 2:09 pm | #30

    Wish to thank you for sharing this. I tried your code and it works , but I have problem sending two files in one mail message, because second file is not complete (truncated or something).
    When I try send just one file works OK.

    Thank again, very nice example

    • Christopher Beck
      August 2, 2012 at 3:56 pm | #31

      Emir – this has been pointed out before. You just need to reset l_idx := 1 inside the cursor loop. Put it after line 77. That should allow you to include multiple docs.

      • Emir
        August 6, 2012 at 2:05 pm | #32

        Please can you help about this issue : I am using a little modified version to send two files in attachment every time :

        ***
        for i in 1..2 loop
        l_blob := tis.procitaj_fajl_u_varijablu(‘ERACUN_PDF_FAJLOVI’,’eracun_’||baid_i_faktura||’.pdf’);
        l_attachment_name := ‘eracun_’||baid_i_faktura||’_’||i||’.pdf’;

        — 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);
        l_idx := 1;
        while l_idx rez
        , src_lob => src_file
        , amount => dbms_lob.getLength(src_file));

        –dbms_lob.close(rez);
        dbms_lob.close(src_file);

        RETURN rez;
        else
        — ne postoji fajl na fajlsistemu
        return null;
        end if;

        END PROCITAJ_FAJL_U_VARIJABLU;

        Sorry because of this big post.

  21. Emir
    August 7, 2012 at 8:01 am | #33

    My post from yesterday has errors and should be deleted. I wanted to ask about problem I have but few minutes ago I found the solution and I want to share it.

    My problem was sending mail with multiple attachments. First time I tryed example it was working OK, but later I discovered that somethimes second attachment getting corrupted or something.

    I was trying to find soultion yesterday all day but unsuccessfully and I was loosing my patience.

    Solution is that the variable l_buff_size also needs to be reinitialized before while loop,
    that means that line l_buff_size := 57; should be placed before line 78 for example to work perfectly with multiple attachments.

    Hope this will help someone else using tis example.

    Thank you again,
    Emir

  22. January 11, 2014 at 1:37 pm | #34

    This website was… how do I say it? Relevant!!

    Finally I’ve found something that helped me.

    Thanks a lot!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: