This discussion is archived
11 Replies Latest reply: Feb 1, 2005 10:32 AM by Justin Cave RSS

URGENT PLZ: IS THERE ANY WAY TO RECIEVE MAIL IN ORACLE DATABASE???

435927 Newbie
Currently Being Moderated
IS THERE ANY WAY TO 'RECIEVE' MAIL IN ORACLE DATABASE???
OR ANY OTHER WAY RELATING TO ORACLE.
PLZ REPLY.
THANKS AND REGARDS
ADNUST
  • 1. Re: URGENT PLZ: IS THERE ANY WAY TO RECIEVE MAIL IN ORACLE DATABASE???
    430537 Newbie
    Currently Being Moderated

    You can send mails from database using utl_smtp package
    I did not understand what do you mean by receving a mail into database

  • 2. Re: URGENT PLZ: IS THERE ANY WAY TO RECIEVE MAIL IN ORACLE DATABASE???
    435927 Newbie
    Currently Being Moderated
    i m not talking about sending mail.
    i m talking about recieving mail in oracle , is thier method to recieve mail.
    thnks.
  • 3. Re: URGENT PLZ: IS THERE ANY WAY TO RECIEVE MAIL IN ORACLE DATABASE???
    426372 Newbie
    Currently Being Moderated
    http://www.oracle.com/collabsuite/feature_email.html

    HTH,
    Pete
  • 4. Re: URGENT PLZ: IS THERE ANY WAY TO RECIEVE MAIL IN ORACLE DATABASE???
    435927 Newbie
    Currently Being Moderated
    i still cant understand the way.
    plz help????????
  • 5. Re: URGENT PLZ: IS THERE ANY WAY TO RECIEVE MAIL IN ORACLE DATABASE???
    426372 Newbie
    Currently Being Moderated
    Oracle Database cannot act as a mail server on its own.

    If you would like to connect to your mail server from Oracle you can, but this is probably not what you're looking for.

    If you could describe in a bit more detail exactly what you're trying to do perhaps more help could be offerred. What are you trying to accomplish?

    Thanks,
    Pete
  • 6. Re: URGENT PLZ: IS THERE ANY WAY TO RECIEVE MAIL IN ORACLE DATABASE???
    SatishKandi Guru
    Currently Being Moderated
    From AskTom.Oracle.com:

    Well, the database won't be "recieving" mail. You can have a process running in
    the database (scheduled via dbms_job so that it runs every N minutes to look for
    mail) that will retrieve the mail from some server.

    You can accomplish this with a java stored procedure and the java mail api. See


    http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:255615160805

    it shows how to SEND mail -- but the java mail api fully supports reading mail
    (it just shows you the concepts you can use).

    When the database is down, your job will not run, hence the mail will be saved
    in the exchange server until the database is up and your jobs once again start
    running.
  • 7. Re: URGENT PLZ: IS THERE ANY WAY TO RECIEVE MAIL IN ORACLE DATABASE???
    435927 Newbie
    Currently Being Moderated
    thnx above answer helped me little
    thx.
  • 8. Re: URGENT PLZ: IS THERE ANY WAY TO RECIEVE MAIL IN ORACLE DATABASE???
    InoL Guru
    Currently Being Moderated
    This is sample Java code to receive emails, including attachments. The code contains some references to table names and column names that you can modify.
    Note that you have to install JavaMail first (from www.sun.com).
    set scan off
    
    CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "ReceiveMail" AS
    import javax.mail.*;
    import javax.mail.internet.*;
    import java.util.*;
    import java.io.*;
    import java.sql.*;
    import sqlj.runtime.*;
    import oracle.sql.BLOB;
    
    public class ReceiveMail
    {
    
    
      static void getAttachments(Message message, int incidentNo)
      throws MessagingException, IOException, SQLException {
    
        //String attachments = "";
        Object content = message.getContent();
    
        if (content instanceof Multipart)
        {
          // -- Multi part message which may contain attachment
    
          Multipart multipart = (Multipart)message.getContent();
    
          // -- Loop through all parts of the message
    
          for (int i=0, n=multipart.getCount(); i<n; i++) {
    
            Part part = multipart.getBodyPart(i);
    
            String disposition = part.getDisposition();
            if ((disposition != null) &&
                 (disposition.equals(Part.ATTACHMENT) ||
                  disposition.equals(Part.INLINE))) {
    
              //-- This part is a file attachment
    
              String fileName = incidentNo+"_"+part.getFileName().replace(' ','_');
              System.out.println("FILE: " + fileName);
    
              String contentType = part.getContentType();
              String mimeType = contentType.substring(0,contentType.indexOf(";"));
              System.out.println("FILETYPE: " + mimeType);
    
              InputStream is = part.getInputStream();
    
              // -- To work with a BLOB column you have to insert a record
              // -- with an emptly BLOB first.
              #sql { insert into attachment(at_file, at_mimetype, at_attachment)
                                     values (:fileName, :mimeType, empty_blob()) };
    
              // -- Retrieve the BLOB
              BLOB attachment = null;
              #sql { select at_attachment into :attachment
                     from   attachment  where at_file = :fileName };
    
              // -- Fill the BLOB
              OutputStream os = attachment.getBinaryOutputStream();
    
              int j;
              while ((j = is.read()) != -1) {
                 os.write(j);
              }
              is.close();
              os.close();
    
              // -- Set the BLOB by updating the record
              #sql { update attachment set at_attachment = :attachment
                     where at_file = :fileName };
            }
          }
        }
      }
    
    
    
    
      static String getPlainTextBody(Message message)
      throws MessagingException, IOException
      {
        Object content = message.getContent();
    
        if (message.isMimeType("text/plain")) {
          // -- Message has plain text body only
          System.out.println("SIMPLE TEXT");
          return (String) content;
    
        } else if (message.isMimeType("multipart/*")) {
            // -- Message is multipart. Loop through the message parts to retrieve
            // -- the body.
            Multipart mp = (Multipart) message.getContent();
            int numParts = mp.getCount();
            System.out.println("MULTIPART: "+numParts);
    
            for (int i = 0; i < numParts; ++i) {
    
              System.out.println("PART: "+mp.getBodyPart(i).getContentType());
              if (mp.getBodyPart(i).isMimeType("text/plain")) {
                // -- Return the plain text body
                return (String) mp.getBodyPart(i).getContent();
    
              } else if (mp.getBodyPart(i).isMimeType("multipart/*")) {
                  // -- Body is also multipart (both plain text and html).
                  // -- Loop through the body parts to retrieve plain text part.
                  MimeMultipart mmp = (MimeMultipart) mp.getBodyPart(i).getContent();
                  int numBodyParts = mmp.getCount();
                  System.out.println("MULTIBODYPART: "+numBodyParts);
    
                  for (int j = 0; j < numBodyParts; ++j) {
                    System.out.println("BODYPART: "+mmp.getBodyPart(j).getContentType());
                    if (mmp.getBodyPart(j).isMimeType("text/plain")) {
                      // -- Return the plain text body
                      return (String) mmp.getBodyPart(j).getContent();
                    }
                  }
              }
            }
    
            return "";
    
        } else {
            System.out.println("UNKNOWN: "+message.getContentType());
            return "";
        }
      }
    
    
    
    
    
      static void saveMessage(Message  message)
      throws MessagingException, IOException, SQLException
      {
        //String body = "";
        int incidentNo;
    
        // -- Get a new incident number
        #sql { select seq_incident.nextval into :incidentNo from dual };
    
        // -- Get the header information
        String from = ((InternetAddress)message.getFrom()[0]).getAddress();
        System.out.println("FROM: "+ from);
    
        String subject = message.getSubject();
        System.out.println("SUBJECT: "+subject);
    
        // -- Retrieve the plain text body
        String body = getPlainTextBody(message);
    
        // -- Store the message in the email table
        #sql { insert into email (em_incident, em_from, em_subject, em_body)
               values (:incidentNo, :from, :subject, :body) };
        // -- Retrieve the attachments
        getAttachments(message, incidentNo);
    
        #sql { commit };
    
        // -- Mark message for deletion
        message.setFlag(Flags.Flag.DELETED, true);
      }
    
    
     public static String Receive(String POP3Server, String usr, String pwd)
      {
    
        Store store = null;
        Folder folder = null;
    
        try
        {
          // -- Get hold of the default session --
          Properties props = System.getProperties();
          props.put("mail.pop3.connectiontimeout", "60000");
          Session session = Session.getDefaultInstance(props, null);
    
          // -- Get hold of a POP3 message store, and connect to it --
          store = session.getStore("pop3");
          store.connect(POP3Server, usr, pwd);
          System.out.println("Connected");
    
          // -- Try to get hold of the default folder --
          folder = store.getDefaultFolder();
          if (folder == null) throw new Exception("No default folder");
    
          // -- ...and its INBOX --
          folder = folder.getFolder("INBOX");
          if (folder == null) throw new Exception("No POP3 INBOX");
    
          // -- Open the folder for read_write (to be able to delete message) --
          folder.open(Folder.READ_WRITE);
    
          // -- Get the message wrappers and process them --
          Message[] msgs = folder.getMessages();
          for (int msgNum = 0; msgNum < msgs.length; msgNum++)
          {
            saveMessage(msgs[msgNum]);
          }
          System.out.println("No more messages");
          return ("SUCCESS");
    
        }
        catch (Exception ex)
        {
          ex.printStackTrace();
          return ex.toString();
        }
        finally
        {
          // -- Close down nicely --
          try
          {
            // close(true), to expunge deleted messages
            if (folder!=null) folder.close(true);
            if (store!=null) store.close();
          }
          catch (Exception ex)
          {
            //ex.printStackTrace();
            return ex.toString();
          }
        }
      }
    }
    /
    
    
    
    create or replace function receivemail (pop3_server  in  string
                                           ,pop3_usr     in  string
                                           ,pop3_pwd     in  string
    )
    return varchar2
    is language java name
    'ReceiveMail.Receive(java.lang.String
                        ,java.lang.String
                        ,java.lang.String) return String';
    /
  • 10. Re: URGENT PLZ: IS THERE ANY WAY TO RECIEVE MAIL IN ORACLE DATABASE???
    426432 Newbie
    Currently Being Moderated
    Is this possible with PL/SQL too?
  • 11. Re: URGENT PLZ: IS THERE ANY WAY TO RECIEVE MAIL IN ORACLE DATABASE???
    Justin Cave Oracle ACE
    Currently Being Moderated
    Technically, there is probably no reason that you couldn't use the utl_tcp package to develop your own PL/SQL POP3 client. You would have to do some rather significant development, though, to make that work. I have seen one or two implementations of an FTP client in PL/SQL using utl_tcp, though, so it's possible.

    Justin
    Distributed Database Consulting, Inc.
    http://www.ddbcinc.com/askDDBC