I recieve a spreadsheet(.xls) from business user on daily basis via email by 10.00 AM daily. Right now
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 220.127.116.11.0 - 64bit Production
PL/SQL Release 18.104.22.168.0 - Production CORE 22.214.171.124.0
Production TNS for Linux: Version 126.96.36.199.0 -
Production NLSRTL Version 188.8.131.52.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.
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?
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.