Re: Email from an Oracle database server

Hi:

Well, the database can't natively send mail (assuming you are referring to SMTP mail). One way that I can think of to do this would be:

1. Create an external daemon (unix) or service (NT) that connects to Oracle and waits for a message on a pipe. (See the docs for the DBMS_PIPE package.) The pipe would provide a blocking wait (meaning it simply sit there, not using any CPU cycles, until a message appears in the pipe).
2. Create a table (or tables) for the message, subject, to address, etc. Place an INSERT trigger on this table that opens the other end of the pipe already opened by the daemon or service and writes a message to it. Or you could use a stored procedure to do the same thing. In this case, you have to insert the row, then call the stored procedure.
3. When the daemon or service *sees* the message on the pipe (which should probably be the mail_id# or something along those lines), it then goes and SELECTs the mail message, subject, to address, etc., and uses sendmail (unix) or a MAPI call (NT) to actually send the mail. After the mail is sent then the daemon or service should UPDATE the row with perhaps a date/time stamp to indicate that is has been sent.

This would probaby scale pretty well as a single-threaded single process. If you needed additional scaleability, you could modify it to be a multi-process daemon or multi-threaded service that opens multiple Oracle sessions and creates multiple pipes with different names. Then, you would have to construct some sort of algorithm to choose which pipe to send the mail_id to. Perhaps just a simple round-robin type approach would work.

One note on what I've said above. A single-threaded single-process unix daemon is pretty easy to write. There is actually no such thing as a single-threaded NT service; all services have to be multi-threaded to fit the "NT Service Model". You *could* of course write it as a single-threaded daemon on NT, then run it in the background using AT or SOON, or the SRVANY.exe utility included in the NT resource kit. It wouldn't fit MS's model, but who cares as long as it works?

Good luck,
--
David C. Sisk
The Unofficial ORACLE on NT site
http://www.ipass.net/~davesisk/oont.htm

Ayokunle Giwa wrote in message <4TPi3.2039$cO4.66994_at_news12.ispnews.com>...
>Is anyone aware of a solution capable of handling a latrge email load
>generated from within an Oracle database,
>I can think of number of ways to do it but I seek a robust and scalable
>solution .....................
>
>Ayokunle Giwa
>
>
Received on Tue Jul 13 1999 - 21:07:01 CDT