Send Email from oracle with file attached"

[Tarry]

OK, this is less kinkier but I felt like trying the utl_smtp pkg so here's what you can do(I've used T kyte's example with a few variations here and there, for eg: with the newer version of javamail/jaf does not require unjar and rejar kind of stuff, they're are readable in the database....)

[1] First you download javamail api(http://java.sun.com/products/javamail/) and jaf--needed to activate the javamail api(http://java.sun.com/products/javabeans/glasgow/jaf.html).
Then after downloading extract the mail.jar from the javamail-1_3.zip file and activation.jar from the jaf-1_0_2.zip file into the directory of your choice.

[2] then load these 2 jar files into your database(I've used 817 here) with the foll. syntax............

loadjava -u sys/pwd@dbase -o -r -v -f -noverify -synonym -g public activation.jar
do the same for the mail.jar file..
For the parameters just type loadjava to understand what they mean...

[3] then write a java source (for explanations for the syntax(es) I'd advise you to acquire the tom kyte's book, it's excellent...)

[email protected]L>
[email protected]L> create or replace and compile
  2  java source named "mail"
  3  as
  4  import java.io.*;
  5  import java.sql.*;
  6  import java.util.Properties;
  7  import java.util.Date;
  8  import javax.activation.*;
  9  import javax.mail.*;
 10  import javax.mail.internet.*;
 11  import oracle.jdbc.driver.*;
 12  import oracle.sql.*;
 13
 14  public class mail
 15  {
 16    static String dftMime = "application/octet-stream";
 17    static String dftName = "filename.dat";
 18
 19    public static oracle.sql.NUMBER
 20                         send(String from,
 21                              String to,
 22                              String cc,
 23                              String bcc,
 24                              String subject,
 25                              String body,
 26                              String SMTPHost,
 27                              oracle.sql.BLOB attachmentData,
 28                              String attachmentType,
 29                              String attachmentFileName)
 30    {
 31      int rc = 0;
 32
 33      try
 34      {
 35        Properties props = System.getProperties();
 36        props.put("mail.smtp.host", SMTPHost);
 37        Message msg =
 38          new MimeMessage(Session.getDefaultInstance(props, null));
 39
 40        msg.setFrom(new InternetAddress(from));
 41
 42        if (to != null && to.length() > 0)
 43          msg.setRecipients(Message.RecipientType.TO,
 44                            InternetAddress.parse(to, false));
 45
 46        if (cc != null && cc.length() > 0)
 47          msg.setRecipients(Message.RecipientType.CC,
 48                            InternetAddress.parse(cc, false));
 49
 50        if (bcc != null && bcc.length() > 0)
 51          msg.setRecipients(Message.RecipientType.BCC,
 52                            InternetAddress.parse(bcc, false));
 53
 54            if ( subject != null && subject.length() > 0 )
 55             msg.setSubject(subject);
 56            else msg.setSubject("(no subject)");
 57
 58        msg.setSentDate(new Date());
 59
 60        if (attachmentData != null)
 61        {
 62          MimeBodyPart mbp1 = new MimeBodyPart();
 63          mbp1.setText((body != null ? body : ""));
 64          mbp1.setDisposition(Part.INLINE);
 65
 66          MimeBodyPart mbp2 = new MimeBodyPart();
 67          String type =
 68              (attachmentType != null ? attachmentType : dftMime);
 69
 70          String fileName = (attachmentFileName != null ?
 71                             attachmentFileName : dftName);
 72
 73          mbp2.setDisposition(Part.ATTACHMENT);
 74          mbp2.setFileName(fileName);
 75
 76          mbp2.setDataHandler(new
 77             DataHandler(new BLOBDataSource(attachmentData, type))
 78          );
 79
 80          MimeMultipart mp = new MimeMultipart();
 81          mp.addBodyPart(mbp1);
 82          mp.addBodyPart(mbp2);
 83          msg.setContent(mp);
 84        }
 85        else
 86        {
 87          msg.setText((body != null ? body : ""));
 88        }
 89        Transport.send(msg);
 90        rc = 1;
 91      } catch (Exception e)
 92      {
 93        e.printStackTrace();
 94        rc = 0;
 95      } finally
 96      {
 97        return new oracle.sql.NUMBER(rc);
 98      }
 99    }
100
101    // Nested class that implements a DataSource.
102    static class BLOBDataSource implements DataSource
103    {
104      private BLOB   data;
105      private String type;
106
107      BLOBDataSource(BLOB data, String type)
108      {
109          this.type = type;
110          this.data = data;
111      }
112
113      public InputStream getInputStream() throws IOException
114      {
115        try
116        {
117          if(data == null)
118            throw new IOException("No data.");
119
120          return data.getBinaryStream();
121        } catch(SQLException e)
122        {
123          throw new
124          IOException("Cannot get binary input stream from BLOB.");
125        }
126      }
127
128      public OutputStream getOutputStream() throws IOException
129      {
130        throw new IOException("Cannot do this.");
131      }
132
133      public String getContentType()
134      {
135        return type;
136      }
137
138      public String getName()
139      {
140        return "BLOBDataSource";
141      }
142    }
143  }
144  /

Java created.

[4] write a function ...

[email protected]L>
[email protected]L> set echo on
[email protected]L>
[email protected]L> create or replace function send(
  2      p_from                  in varchar2,
  3      p_to                    in varchar2,
  4      p_cc                    in varchar2,
  5      p_bcc                   in varchar2,
  6      p_subject               in varchar2,
  7      p_body                  in varchar2,
  8      p_smtp_host             in varchar2,
  9      p_attachment_data       in blob,
 10      p_attachment_type       in varchar2,
 11      p_attachment_file_name  in varchar2) return number
 12  as
 13  language java name 'mail.send( java.lang.String,
 14                                 java.lang.String,
 15                                 java.lang.String,
 16                                 java.lang.String,
 17                                 java.lang.String,
 18                                 java.lang.String,
 19                                 java.lang.String,
 20                                 oracle.sql.BLOB,
 21                                 java.lang.String,
 22                                 java.lang.String
 23                               ) return oracle.sql.NUMBER';
 24  /

Function created.

[5] Grant appropriate privileges to the user using the mail to send attachements............

[email protected]L> begin
2 dbms_java.grant_permission(
3 grantee => 'APPDEV',
4 permission_type => 'java.util.PropertyPermission',
5 permission_name => '*',
6 permission_action => 'read,write'
7 );
8 dbms_java.grant_permission(
9 grantee => 'APPDEV',
10 permission_type => 'java.net.SocketPermission',
11 permission_name => '*',
12 permission_action => 'connect,resolve'
13 );
14 end;
15 /

PL/SQL procedure successfully completed.

[/code]

[6] create a table .....

[email protected]L> create table demo ( theBlob blob );

Table created.

[7] create a directory....

[email protected]L>
[email protected]L> create or replace directory my_files as 'c:\temp\';

Directory created.

[8] load this table with the attachment,you need to be familier with the dbms_lob pkg...

[email protected]L> declare
  2      l_blob  blob;
  3      l_bfile bfile;
  4  begin
  5      insert into demo values ( empty_blob() )
  6      returning theBlob into l_blob;
  7
  8      l_bfile := bfilename( 'MY_FILES', 'OTNToolbar.zip' );
  9      dbms_lob.fileopen( l_bfile );
 10
 11      dbms_lob.loadfromfile( l_blob, l_bfile,
 12                             dbms_lob.getlength( l_bfile ) );
 13
 14      dbms_lob.fileclose( l_bfile );
 15  end;
 16  /

[email protected]L> commit;

Commit complete.

...........continued next(crossed the 10000 letter limit, lucky that i didn't add all that load metainf from the 2 jar files..)