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