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
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 c := UTL_SMTP.OPEN_CONNECTION('localhost'); UTL_SMTP.HELO(c, 'localhost'); UTL_SMTP.MAIL(c, 'sender@foo.com'); UTL_SMTP.RCPT(c, 'shapira.michael@gmail.com'); UTL_SMTP.OPEN_DATA(c);
UTL_SMTP.WRITE_DATA(c, 'From' || ': ' || '"Sender" <sender@foo.com>' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(c, 'To' || ': ' || '"Recipient" <recipient@foo.com>' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(c, 'Subject' || ': ' || p_subject || UTL_TCP.CRLF);
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 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;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
package utils;import java.sql.*;import oracle.jdbc.driver.*; import oracle.sql.*;public class DBConnector
{ private String _host; private int _port;
private String _sid; private String _db_pass; private String _db_user; private static DBConnector dbConn;
private Connection connection; //Singletone constractor
private DBConnector(String host, int port, String sid,
String db_pass,
String db_user) throws SQLException { _host = host;
_port = port;
_sid = sid;
_db_pass = db_pass;
_db_user = db_user;
DriverManager.registerDriver(new OracleDriver()); String connectionString = "jdbc:oracle:thin:@"+host+":"+port+":"+sid;
connection = DriverManager.getConnection(connectionString,db_user,db_pass);
}
public static DBConnector getInstance(String host,
int port, String sid,
String db_pass,
String db_user) throws SQLException { if (dbConn == null) dbConn = new DBConnector(host,port,sid,db_pass,db_user); return dbConn; }
public Connection getConnection() { return connection; }
}
And now the major class
import java.io.BufferedInputStream;import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.sql.Connection;import java.sql.SQLException;import oracle.jdbc.OraclePreparedStatement;import utils.DBConnector;public class Email
{ public static void main(String[] args) throws SQLException, IOException
{ Connection conn =null;
DBConnector db = DBConnector.getInstance("10.0.0.65", 1555, "DVIS10", "apps", "apps");
conn = db.getConnection();
OraclePreparedStatement stmt = (OraclePreparedStatement)conn.prepareCall("begin sendmail.send_blob('michael.shapira@unitask.com','michael.shapira@unitask.com','Email with Attachment','attached.pdf',?); end;"); File file = new File("path_to_the_file"); // Create File object for the file
long filesize = file.length(); // Get size of file
FileInputStream fs = new FileInputStream(file); // Open the file
BufferedInputStream bs = new BufferedInputStream(fs, 16384); // Use file buffering
stmt.setBinaryStream(1, bs, (int)filesize); stmt.execute();
conn.close();
}
}
So, how does it work?
Make connection to the database by using DBConnector class
Connection conn =null;DBConnector db = DBConnector.getInstance("10.0.0.65", 1555, "DVIS10", "apps", "apps");conn = db.getConnection();
I wrapped send_blob procedure in sendmail package. The last parameter is out BLOB object
OraclePreparedStatement stmt = (OraclePreparedStatement)conn.prepareCall("begin sendmail.send_blob('michael.shapira@unitask.com','michael.shapira@unitask.com','Email with Attachment','attached.pdf',?); end;");Read input file to the stream and send as a parameter to procedure
File file = new File("path_to_the_file"); // Create File object for the file
long filesize = file.length(); // Get size of file
FileInputStream fs = new FileInputStream(file); // Open the file
BufferedInputStream bs = new BufferedInputStream(fs, 16384); // Use file buffering
stmt.setBinaryStream(1, bs, (int)filesize);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
