Loading Oracle data from e-mail attachments
Oracle Database Tips by Donald Burleson
Loading Oracle data via EDI feeds can be cumbersome and expensive,
and many small businesses desire a way to share data between
companies using standard e-mail.
The trick is to write an intelligent server-side process that
will wait for an e-mail to appear in a specific inbox and then
perform the tasks required to extract the attachment, load the data
into an Oracle tables, and verify successful completion.
You've got mail
In UNIX, this would be a daemon process which invokes a shell
script that would wake up every minute (or at the same frequency as
the server sendmail) to check for any new e-mails with data feeds
attached. The psuedocode might be like this:
if ("ls -alt|head
command detects a new e-mail")
All e-mail would be routed into a specific e-mail account (i.e.
firstname.lastname@example.org ), and the UNIX directory for the
sendmail account would have the incoming file.
Where's the data?
At this point we must understand how our external client is
sending the data to us. The data could be in the e-mail as
comma-delimited text, a spreadsheet attachment, of a host of other
methods. The most common EDI transfer method for small-scale
EDI is spreadsheets, and provided that the client uses the same
template for each transmission, the whole process can easily be
- CSV File
- This type of spreadsheet file is
essentially a coma-delimited flat file, already in a format
easily used by the Oracle SQL*Loader utility.
- XLS File
- This is a standard MS-Excel
spreadsheet and loading into Oracle requires a specialized API
(such as Excel-DB)
or the external invocation of a utility to extract the
spreadsheet data into a plain, column-delimited flat file.
The trick is to always have a resulting flat file that can be
read by SQL*Loader in a consistent fashion.
Loading the data into Oracle
The standard rules for use the
utility apply here and the developer must ensure that there are
no rejected rows by programmatically inspecting the SQL*Loader
In order to fully automate this type of e-mail EDI to Oracle it
is critical that you make an affirmative check to ensure that
SQL*Loader successfully loaded your data.
In a shell script this is a simple matter of redirecting the
sqlldr output to a flat file. The flat file can then be
checked for an affirmative message, something like this:
sqlldr parfile=xxx > checkme.txt
TEMP=`grep -i checkme.txt Successfully Completed.|wc -l`
tmp=`expr TEMP` # Convert string to number
if [ $tmp -le 1 ]
echo "Were not in Kansas anymore, Toto"|
mailx -s "Bad EDI Feed Detected" \
Once in place, this can be a fully automated e-mail based system
for shipping data into a centralized Oracle database from remote
locations all over the world.
Get the Complete
Oracle Utility Information
The landmark book
Utilities The Definitive Reference" contains over 600 pages of
filled with valuable information on Oracle's secret utilities.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.