2 Replies Latest reply: Feb 13, 2014 2:45 AM by Billy~Verreynne RSS

    how to read data from outlook email in oracle pl-sql


      I recieve a  spreadsheet(.xls) from business user on daily basis via email by 10.00 AM daily. Right now

      1. We manually download the file from email.
      2. Place it in a folder
      3. Then upload sheet data into a oracle table. (There is existing java setup application when clicked on submit upload ,it loads data into a table).


      I am looking for assistance where


      Case 1) " PL-SQL reads the attached excel file automatically around 10.30  from my outlook email and loads data into table " .

      Case 2) If Case 1 is not implementable via PL-SQL then looking for " PL-SQL reads the excel file from specified folder in my WINDOWS 2007  automatically around 10.30  and loads data into table "


      Environment details : Oracle Database 11g Enterprise Edition Release - 64bit Production

      PL/SQL Release - Production CORE

      Production TNS for Linux: Version -

      Production NLSRTL Version - Production

      *Also we dont have access to UNIX-servers to place the files and think about utilizing unix shell script flexibility to achieve this.

        • 1. Re: how to read data from outlook email in oracle pl-sql



          I guess it's not an easy problem and you have to look at all the aspects, that problem has.


          1) Outlook is an client application on a windows client that fetches mails out of the postbox.

          2) What kind of postbox is this. Is IMAP or POP enabled

          3) What is the encoding of your mail or is there an automation in outlook that could automatic store the excel file.

          4) What format does the excel file have. Is it an XML based format or an older binary format?


          I see two possible ways:


          A) Your client application outlook stores the file in an folder, the server also could see. Then you use UTL_FILE Package

          to read and process the file

          B) Your server uses DBMS_SMTP or DBMS_MAIL to directly connect your postbox and get the Emails. Than use UTL_FILE to store and process the excel attachment.


          Both, A and B needs code to process an Excel file. If the file is XML, you could use Oracle XML Functions, if not, you have to run client side PL/SQL and within this OLE Automation to automate excel application for opening and reading the excel file.


          Maybe there is a company providing a product, that all of it implements.


          By the way: Is Excel really the right format or technology to get date from one site to another?


          Good luck



          • 2. Re: how to read data from outlook email in oracle pl-sql

            You cannot "read" the e-mail from Outlook. It is a client. Not server. It is not designed to act as a sever, accepting client requests for a specific e-mail and then sending that to the caller.


            You can however read e-mail from a mail server like Exchange. There are 4 basic means to communicate with Exchange. The standards protocols are:

            - POP3 (Post Office Protocol version 3)

            - IMAP (Internet Message Access Protocol)


            Exchange supports both. However, some management/security idiot is likely to have disabled these on Exchange due to unwarranted "security" issues. Or that is my experience.


            Then there are 2 proprietary interfaces/protocols:

            - MAPI (Mail API working via NetBIOS typically)

            - OWA (Outlook Web App acces over HTTP(S) protocol)


            MAPI requires a Windows o/s. There is an Open Source implementation for Linux (using RPC NetBIOS I think). Unsure how robust this is.


            There is also an OWA Open Source driver which is fairly robust and comprehensive (it ships with the Evolution Mail Reader on Linux).


            So using one of these protocols/interfaces, allows you to write PL/SQL code that reads e-mail directly from an Exchange mailbox.


            I posted a POP3 example inRe: Read Mail Folder. An IMAP example will work in a similar fashion (using UTL_TCP).


            If your Oracle server is on Windows, then it should be relatively simple to write a DLL that uses MAPI and make a mailbox accessible that way via the EXTPROC (external procedure) interface of Oracle.


            An issue to keep in mind though. These are protocols to access a mailbox. And retrieve an e-mail. Not render, unpack, decode, or parse the contents of the e-mail. That is governed by MIME specifications.