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.
Leave a reply to ano Cancel reply
Pages
Blogroll
M | T | W | T | F | S | S |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | 5 | 6 | |
7 | 8 | 9 | 10 | 11 | 12 | 13 |
14 | 15 | 16 | 17 | 18 | 19 | 20 |
21 | 22 | 23 | 24 | 25 | 26 | 27 |
28 | 29 | 30 | 31 |
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.
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* ).
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.
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.
Awesome. Thanks mate.
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
You will have to give me a bit more information than its not working for me to help.
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
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.
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.
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.
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
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.
Thanks a Ton …
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
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.
Thanks, I found – for some reason inserting a chr(10) randomly, fixes the problem. Must have something to do with the character recongnition.
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?
Appears you are correct. I should have just initialized l_buff_size prior to looping at line 78 instead of in the declaration section.
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.
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.
Thanks! Glad you found it useful.
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.
Nice catch. Thanks.
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.
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.
Hello,
Is it possible to send two attachments in the same mail?
Regards Ola
Never minds. I solved it by reading the comments again. Thanks everyone and specially the writer 🙂
Regards Ola
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 🙂
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
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.
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.
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
This website was… how do I say it? Relevant!!
Finally I’ve found something that helped me.
Thanks a lot!