Send Email from oracle with file attached"
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Send Email from oracle with file attached"

  1. #1
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598

    Send Email from oracle with file attached"

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

    Cheers!
    Cheers!
    OraKid.

  2. #2
    celebguy_dv Guest

  3. #3
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587

    Use Javamail +jaf for this.........

    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...)
    Code:
    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 ...
    Code:
    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 .....
    Code:
    sys@NICK817.TARRY.LOCAL> create table demo ( theBlob blob );
    
    Table created.
    [7] create a directory....
    Code:
    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...
    Code:
    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..)
    Last edited by Tarry; 03-04-2003 at 11:46 AM.
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  4. #4
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587

    the last bit........

    [9] write a pl/sql block to send that attachment, i did here sending it to my other account........
    Code:
    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
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  5. #5
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Thnx a ton tarry
    will check n revert
    Cheers!
    Cheers!
    OraKid.

  6. #6
    Join Date
    Sep 2009
    Posts
    1
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width