Re: Request for information on automated notification.

I had a specific requirement to send BLOBS (pdfs in the main) to multiple
destinations so had to develop (with much assistance from the URL in my
previous posting) the code below which seemed flexible enough to most
things with the exception of multiple attachments which I never got round
to.

Cheers,

Ian

create or replace and compile
java source named "mail"
as
import java.io.*;
import java.sql.*;
import java.util.Properties;
import java.util.Date;
import javax.activation.*;
import javax.mail.*;
import javax.mail.internet.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;
public class mail
{
   static String dftMime = "application/octet-stream";
   static String dftName = "filename.dat";

   public static oracle.sql.NUMBER
   send(String from,
        oracle.sql.ARRAY tolist,
        String subject,
        String body,
        String SMTPHost,
        oracle.sql.BLOB attachmentData,
        String attachmentType,
        String attachmentFileName)
   {
      int rc = 0;

      try
      {
         Properties props = System.getProperties();
         props.put("mail.smtp.host", SMTPHost);
         Message msg =
            new MimeMessage(Session.getDefaultInstance(props, null));

         msg.setFrom(new InternetAddress(from));

         ResultSet to = tolist.getResultSet();
         for(int i = 0; i < tolist.length();i++) {
           to.next();
           STRUCT maillist = (STRUCT)to.getObject(2);
           Object[] mlist  = maillist.getAttributes();
           String mtype    = (String)mlist[0];
           String address  = (String)mlist[1];
           if (mtype.equals("TO"))
               msg.addRecipient(Message.RecipientType.TO,
                                new InternetAddress(address,false));
           else if (mtype.equals("CC"))
               msg.addRecipient(Message.RecipientType.CC,
                                new InternetAddress(address,false));
           else if (mtype.equals("BCC"))
               msg.addRecipient(Message.RecipientType.BCC,
                                new InternetAddress(address,false));
         }

         if ( subject != null && subject.length() > 0 )
            msg.setSubject(subject);
         else msg.setSubject("(no subject)");

         msg.setSentDate(new Date());

         if (attachmentData != null)
         {
            MimeBodyPart mbp1 = new MimeBodyPart();
            mbp1.setText((body != null ? body : ""));
            mbp1.setDisposition(Part.INLINE);

            MimeBodyPart mbp2 = new MimeBodyPart();
            String type =
               (attachmentType != null ? attachmentType : dftMime);

            String fileName = (attachmentFileName != null ?
                               attachmentFileName : dftName);

            mbp2.setDisposition(Part.ATTACHMENT);
            mbp2.setFileName(fileName);

            mbp2.setDataHandler(new
               DataHandler(new BLOBDataSource(attachmentData, type))
         );

         MimeMultipart mp = new MimeMultipart();
         mp.addBodyPart(mbp1);
         mp.addBodyPart(mbp2);
         msg.setContent(mp);
      }
      else
      {
         msg.setText((body != null ? body : ""));
      }
      Transport.send(msg);
      rc = 1;
   } catch (Exception e)
   {
      e.printStackTrace();
      rc = 0;
   } finally
   {
      return new oracle.sql.NUMBER(rc);
   }
}

static class BLOBDataSource implements DataSource
{
   private BLOB   data;
   private String type;

   BLOBDataSource(BLOB data, String type)
      {
         this.type = type;
         this.data = data;
      }

      public InputStream getInputStream() throws IOException
      {
         try
         {
            if(data == null)
            throw new IOException("No data.");

            return data.getBinaryStream();
         } catch(SQLException e)
         {
            throw new
            IOException("Cannot get binary input stream from BLOB.");
         }
      }

      public OutputStream getOutputStream() throws IOException
      {
         throw new IOException("Cannot do this.");
      }

      public String getContentType()
      {
         return type;
      }

      public String getName()
      {
         return "BLOBDataSource";
      }
   }
}
/

create or replace function send(p_from                  in varchar2,
                        p_to                    in mlist,
                        p_subject               in varchar2,
                        p_body                  in varchar2,
                        p_smtp_host             in varchar2,
                        p_attachment_data       in blob,
                        p_attachment_type       in varchar2,
                        p_attachment_file_name  in varchar2)
return number
as
  language java name 'mail.send(java.lang.String,
                        oracle.sql.ARRAY,
                        java.lang.String,
                        java.lang.String,
                        java.lang.String,
                        oracle.sql.BLOB,
                        java.lang.String,
                        java.lang.String)
return oracle.sql.NUMBER';
/

N.B. mlist is a type representing the recipient_type and recipient_address




For the latest data on the economy and society 
consult National Statistics at http://www.statistics.gov.uk

**********************************************************************
Please Note:  Incoming and outgoing email messages
are routinely monitored for compliance with our policy
on the use of electronic communications
**********************************************************************
Legal Disclaimer  :  Any views expressed by
the sender of this message are not necessarily
those of the Office for National Statistics
**********************************************************************

______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
______________________________________________________________________
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: