2 Replies Latest reply: Feb 13, 2014 12:45 AM by BillyVerreynne RSS

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

ap90792 Newbie
Currently Being Moderated

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 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0

Production TNS for Linux: Version 11.2.0.3.0 -

Production NLSRTL Version 11.2.0.3.0 - 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
    user483197 Newbie
    Currently Being Moderated

    Hallo,

     

    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

     

    Frank

  • 2. Re: how to read data from outlook email in oracle pl-sql
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points