Re: Email from an Oracle database server

One correction here. One step #2, there would be a hitche in using use a trigger to open the pipe and send the mail_id. Since the trigger would execute before the insert was commited, the daemon or service wouldn't *see* the mail message in the table. You could insert the mail message in the table, commit it, then call a stored procedure that opens the pipe and sends the mail_id. Or, you could open the pipe and send the mail_id from the trigger, but have the daemon or service poll the table for the mail with that mail_id until it shows up (which would, of course, be after the other session performed the commit).

I know I've made this as clear as mud, but I hope it helps. Good question!

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

David Sisk wrote in message ...
>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:11:44 CDT