Re: Email Connectivity

dekardsdesigns_at_btinternet.com wrote:

> Newbie says ignorance is bliss....

But ignorance tends to be a lot more expensive than education. :-)

> I have looked at the collaboration suite, send mail and a few other
> commercial products, but I am unable to find what I am looking for. My
> proposed system is not concerned with the actual sending and receiving,
> I just need to use the data in the out going and incoming emails for
> database purposes, therefore I would be content with any program
> handling the emails so long as oracle can access the data including
> headers.

Okay, so basically what you need is then a method to get mail into Oracle from a SMTP server and get mail out of Oracle to a POP reader.. with mininal effort?

> As a student I am limited in the systems I can use and afford.

Yes. Hazy recollections of my university days, but I'm sure the The Beer Budget was much more important. :-)

> Could anyone with experience, suggest a starting point, strategy and/or
> likely technologies to research?

The first two things to look at would be the RFCs for the SMTP and POP3 protocols. They are clear text application protocols. Only a very limited command set is needed to send e-mail (using SMTP) and retrieve e-mail (using POP3).

SMTP: http://www.faqs.org/rfcs/rfc821.html POP3: http://www.faqs.org/rfcs/rfc1939.html

My take is that you should at least understand how the delivery mechanism work, and how the payload is handled.

To send e-mail from inside Oracle, can be very easily done using UTL_SMTP. I think that on OTN you will also find a nice PL/SQL wrapper for UTL_MAIL (a PL/SQL package called MAIL_DEMO or something) that is even easier to use and also support attachments.

To get e-mail into Oracle via SMTP.. now that is a tad more complex. The UTL_TCP API in Oracle PL/SQL is a client socket implementation. It does not support server socket API calls like listen() and accept().

You can look at a simplistic Java OpenSource/freeware SMTP server (or write one yourself) and load that into the Oracle Java VM. Personal opinion is that this is technically too complex for a project like yours (you do not want to spend time on something that is essentially out-of-scope).

You could discard POP3 all together as the user interface (mail reader) protocol. Instead use HTTP (which is what Yahoo and others use). You can slap an web interface together in a few hours using HTMLDB that allows a person to enter an e-mail in Oracle (which is transmitted via a batch UTL_MAIL job). The same interface can allow the person to read his mail. Doing this in HTMLDB will be a piece of cake.

You could use something like Delphi/C-Builder to write a POP3 server interface into Oracle. Sounds more complex than what it is as there are Indy Classes (Visual Class Library components) than can be used for this. A simple drop and drag creates an POP3 server - all that you need to code is the method that fetches the mail from Oracle. But this of course requires at least some Delphi/C-Builder knowledge and a basic understanding of a POP3 server.

You could use an OpenSource/freeware POP3 server.. and somehow try to integrate that into Oracle. Unsure just how complex/easy that would be.

Personally I think HTMLDB is your best bet. It is a free companion product from Oracle. Supports 9.2 and 10G databases. Installation is fairly easy.

Once the e-mails are in the database you can do what you intend to do with it.. whatever content-based processing you have in mind. And use HTMLDB again as a web interface into your content reports and graphs.

I'm a big fan of HTMLDB. In fact, I had my own PL/SQL web framework for a while and was thinking of turning it into something similar what HTMLDB does.. only when I saw HTMLDB the first time, it exceeded my expectations and ideas I had by far. It is truly an excellent product which makes it very easy for relative web novices to design Oracle database-based web sites.. while providing enough flexibility and features for anal technical minded people like me to do some complex stuff too.

--
Billy