Re: Email to Oracle - HOW ?????????

Quentin Jones wrote:

> I need to make a very large Email Database. Only problem is I don't
> know how to start. How does one take all incoming mail from a pop3
> server and place it in an oracle database ? it there a simple way to
> do this ?

Depends on what you mean by 'simple'. I have an idea, but I've never tried it and it depends on the platform you're running on. I speak Unix.

Idea #1: Remote access to mail server
The pop3 server is on telnet port 110; assuming you know all the usernames *and passwords*, you could write a shell script to telnet in, log in as each user in turn and retrieve and delete each message. Maybe create a temp file containing the commands, then use "telnet my.mail.server.com:110 < tempfile". This would be stored temporarily somewhere, then used with as input with a PL*SQL procedure along with the username, which would store the data in a table (probably a LONG field). If you need the commands that pop3 recognises, try 'strings /etc/pop3 | more' or mail me - I have a list someplace.

Idea #2: Local access to mail server (ie Oracle running on same box) For each file in the /usr/mail directory (or wherever it is on your server), use PL*SQL to read it and parse the contents. You should search for 'From:' lines, do some basic checking, and separate the messages that way. Possibly use

grep -n "^From: " <filename> | awk -F : "{print \$1}"

to give you a list of line numbers corresponding to message starts.

Both methods require access to the OS however; you'd have to do it using a combination of shell script and PL*SQL.

HTH Jamie Keir Received on Wed Apr 08 1998 - 10:34:17 CDT