Re: utl_smtp Package

From: Mark <bradl001_at_hotmail.com>
Date: Fri, 13 Oct 2000 01:55:38 GMT
Message-ID: <8s5q2o$j4r$1_at_nnrp1.deja.com>


Tim,

Sorry about that. I didn't want to put attachments or waste peoples' time with a bunch of stuff if they didn't have the Java option in the database. I hope nobody minds this excessively long message. I hope it formats okay as well. It should fit within 70 columns.

Anyway, what I did was to load the JavaMail and Activation Framework JAR files (from java.sun.com) into the database. For more info on all this go visit Oracle's "Ask Tom" page. You can get to it from http://www.oracle.com/oramag. A link is near the bottom. He has an article about sending mail which has a tar file that contains the JavaMail and Activation Framework JAR files. It also covers loading the code, etc.

Then I ran the following SQL code which creates a java class and method then creates a PL/SQL wrapper function. The SQL file also includes an example which obviously won't work unless you have the DATA_FILES table.

Since this was something I quickly did for work, I only allow it to attach one file which I guess isn't too bad.

As with all free code and stuff these days, caveat emptor.

Mark


  • CUT HERE =
    set define off 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 {
// ******************************************************************
// Simple method to send a message. Will return a 1 if successful and
// a 0 otherwise. This is very basic but you could dynamically build
// the message body from data you pull from the DBMS. You can also
// make it intelligently capture the exceptions and report back to
// the caller what the exact error is (invalid recipient address, no
// transport, etc).

  //

// Since this runs in the database, I decided to not bother with
// adding JDBC stuff here. If you prefer, replace the arguments to
// this method with JDBC/SQL queries, etc. Also, if you would rather
// get the file data from a file rather than a BLOB, then change that
// code to use a FileInputStream. The point of this was to make it
// somewhat generic.
// ******************************************************************
  public static NUMBER send(String from,
                            String to,
                            String cc,
                            String bcc,
                            String subject,
                            String body,
                            String SMTPHost,
                            BLOB   attachmentData,
                            String attachmentType,
                            String attachmentFileName) {
    int rc = 0;
    boolean hasAttachments = (attachmentData != null);     String content = (body != null ? body : "");

    try {

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

      // Who is this message from???
      msg.setFrom(new InternetAddress(from));

      // Set the to/cc/bcc values if we have them
      if (to != null && to.length() > 0)
        msg.setRecipients(Message.RecipientType.TO,
                          InternetAddress.parse(to, false));
      if (cc != null && cc.length() > 0)
        msg.setRecipients(Message.RecipientType.CC,
                          InternetAddress.parse(cc, false));
      if (bcc != null && bcc.length() > 0)
        msg.setRecipients(Message.RecipientType.BCC,
                          InternetAddress.parse(bcc, false));

      // Set the subject. Default the value if necessary.
      msg.setSubject((subject != null || subject.length() > 0) ?
                     subject : "(no subject)");
      msg.setSentDate(new Date());

      if (hasAttachments) {
        // Create the first part which is the message itself.
        MimeBodyPart mbp1 = new MimeBodyPart();
        mbp1.setText(content);
        mbp1.setDisposition(Part.INLINE);

        // Create the second part. If the caller didn't indicate what
        // type of attachment this is, then default it to a generic
        // binary stream. If we didn't get a file name then default
        // that as well.
        MimeBodyPart mbp2 = new MimeBodyPart();
        String type = (attachmentType != null ?
                       attachmentType : "application/octet-stream");
        String fileName = (attachmentFileName != null ?
                           attachmentFileName : "filename.dat");
        mbp2.setDisposition(Part.ATTACHMENT);
        mbp2.setFileName(fileName);
        mbp2.setDataHandler(new DataHandler(
                            new BLOBDataSource(attachmentData, type)));

        // Create a container then add the two parts. Next, add the
        // container to the message.
        MimeMultipart mp = new MimeMultipart();
        mp.addBodyPart(mbp1);
        mp.addBodyPart(mbp2);
        msg.setContent(mp);
      }
      else {
        // No attachment so just make a simple message
        msg.setText(content);
      }

      Transport.send(msg);
      rc = 1;
    } catch (Exception e) {
      e.printStackTrace();
      rc = 0;

    } finally {
   return new NUMBER(rc);
    }
  }

// Nested class that implements a DataSource.
  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 stream from BLOB.");
      }

    }

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

    public String getContentType() {
      return type;
    }

    public String getName() {
      return "BLOBDataSource";
    }
  }
}
/
show errors
/
create or replace function send(

    p_from                  in varchar2,
    p_to                    in varchar2,
    p_cc                    in varchar2,
    p_bcc                   in varchar2,
    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,
    java.lang.String,
    java.lang.String,
    java.lang.String,
    java.lang.String,
    java.lang.String,
    java.lang.String,

    oracle.sql.BLOB,
    java.lang.String,
    java.lang.String) return oracle.sql.NUMBER'; /
show errors
/
set serveroutput on size 1000000
/
CALL dbms_java.set_output(100000)
/
declare
  ret_code number;
begin
  for i in (select name, blob_data from scott.data_files) loop     ret_code := send(
                  p_from => 'bradl001_at_hotmail.com',
                  p_to => 'someone_at_hotmail.com',
                  p_cc => NULL,
                  p_bcc => NULL,
                  p_subject => 'Urgent Notice!',
                  p_body => 'A picture of my car is attached....',
                  p_smtp_host => 'smtp.somehost.com',
                  p_attachment_data => i.blob_data,
                  p_attachment_type => 'image/gif',
                  p_attachment_file_name => i.name);
    if ret_code = 1 then
      dbms_output.put_line ('Successfully sent message...');
    else
      dbms_output.put_line ('Failed to send message...');
    end if;
  end loop;
end;
/
  • CUT HERE =

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Oct 13 2000 - 03:55:38 CEST

Original text of this message