Amazon Contextual Product Ads

Sunday, 15 May 2011

Using Oracle Database as an Email Server

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

No comments:

Post a Comment