Send Email from oracle with file attached"

[balajiyes]

hi all
is this possible in oracle "Send Email from oracle with file attached"?

Cheers!

[celebguy_dv]

[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...)

sys@NICK817.TARRY.LOCAL>
sys@NICK817.TARRY.LOCAL> 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 ...

sys@NICK817.TARRY.LOCAL>
sys@NICK817.TARRY.LOCAL> set echo on
sys@NICK817.TARRY.LOCAL>
sys@NICK817.TARRY.LOCAL> 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............

sys@NICK817.TARRY.LOCAL> 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 .....

sys@NICK817.TARRY.LOCAL> create table demo ( theBlob blob );

Table created.

[7] create a directory....

sys@NICK817.TARRY.LOCAL>
sys@NICK817.TARRY.LOCAL> 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...

sys@NICK817.TARRY.LOCAL> 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  /

sys@NICK817.TARRY.LOCAL> 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..)

[Tarry]

[9] write a pl/sql block to send that attachment, i did here sending it to my other account........

sys@NICK817.TARRY.LOCAL> set serveroutput on size 1000000
sys@NICK817.TARRY.LOCAL> exec dbms_java.set_output( 1000000 )

PL/SQL procedure successfully completed.

appdev@NICK817.TARRY.LOCAL> declare
  2    ret_code number;
  3  begin
  4    for i in (select theBlob from demo )
  5    loop
  6      ret_code := send(
  7                    p_from => 'tarry.singh@home.nl',
  8                    p_to => 'tarrysingh@hotmail.com',
  9                    p_cc => NULL,
 10                    p_bcc => NULL,
 11                    p_subject => 'Use the attached Zip file',
 12                    p_body => 'to send email with attachments....',
 13                    p_smtp_host => 'mail.home.nl',
 14                    p_attachment_data => i.theBlob,
 15                    p_attachment_type => 'application/winzip',
 16                    p_attachment_file_name => 'OTNToolbar.zip');
 17      if ret_code = 1 then
 18        dbms_output.put_line ('Successfully sent message...');
 19      else
 20        dbms_output.put_line ('Failed to send message...');
 21      end if;
 22    end loop;
 23  end;
 24  /
Successfully sent message...

PL/SQL procedure successfully completed.

And this way i received the toolbar.zip on my hotmail address.

HTH

Cheers

Tarry

[balajiyes]

Thnx a ton tarry
will check n revert
Cheers!

[trivedisushil]

hi due to the below

l_bfile := bfilename( 'MY_FILES', 'OTNToolbar.zip' );
9 dbms_lob.fileopen( l_bfile );

-------------------- you are able to execute this part of procedure -----------------
p_attachment_type => 'application/winzip',
16 p_attachment_file_name => 'OTNToolbar.zip');
-----------------------------------------------------------------------------------
In my scenario - I am not having any directory in my oracle database but i am having something like this
--------
SQL> desc quot_ole
Name Null? Type
------------------------------- -------- ----
QUOT_NUMBER VARCHAR2(30)
QUOT_OBJECT LONG RAW
-------
People save either word or pdf documents and unique quot_number like 'abc/2009' is assigned to each object inserted through FORMS 6i OLE Container
-----------------------------------------------------------------------------
How do i retrive this data for email - which is not having directory but either
mimetype=msword or mimetype=pdf and filename = as users push Word of pdf files inside.

if you can further modify this code to pull the information as per the suggested table above it will be of gr8 help for me to implement.

Otherwise i am able to receive email as per above with attachment.