Inside Oracle DB there is a grate package called UTL_SMTP which allows to send email messages and attachments. (Based on Sending blob attachments in e-mail with utl_smtp by Rex Baldazo). However if I want to send an attachment I need to use UTL_FILE package to get the reference to the file and then I am limited to the database server only or use BLOB data which is stored in the table.
But I wanted to be able to send an email from any tier which can access the database (For example, from my PC :) ). So I decided to write a simple Java program which will do this work. As I said, my work based on “send_blob” procedure from the above link. I modified it a little to include all configuration inside the package
I am not going to explain it, since it is explained well in Rex Baldazo’s blog. I will explain Java class I wrote to use this procedure
First of all, here is simple class to wrap Driver loading logic
And now the major class
So, how does it work?
Make connection to the database by using DBConnector class
I wrapped send_blob procedure in sendmail package. The last parameter is out BLOB object
Read input file to the stream and send as a parameter to procedure
That is all . Very simple. But note!! I was not able to execute this code with Oracle 9i JDBC driver. Only after downloading 10g release2 driver it worked fine