11 Replies Latest reply: Feb 1, 2005 12:32 PM by JustinCave RSS

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

    435927
      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

          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
            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
              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
                i still cant understand the way.
                plz help????????
                • 5. Re: URGENT PLZ: IS THERE ANY WAY TO RECIEVE MAIL IN ORACLE DATABASE???
                  426372
                  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???
                    Satish Kandi
                    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
                      thnx above answer helped me little
                      thx.
                      • 8. Re: URGENT PLZ: IS THERE ANY WAY TO RECIEVE MAIL IN ORACLE DATABASE???
                        InoL
                        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
                          Is this possible with PL/SQL too?
                          • 11. Re: URGENT PLZ: IS THERE ANY WAY TO RECIEVE MAIL IN ORACLE DATABASE???
                            JustinCave
                            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