Re: Email from oracle

Ranc wrote in message <3635867B.E2389FF0_at_hotmail.com>...

> Is there any way(script) to send email thro oracle (on unix) stored
>procedure.

KISS. I think it's great. The politically correct way is to use database pipes for communication between an Oracle client process and a Oracle server process. But then we're a bunch of whiskey swallowing lead pipe wielding Oracle rogues so why bother with elegant solutions when a simple one works better.

The simple method. Create a table that contains at least 3 columns - SENDER, SUBJECT and MSG_BODY. When you want to send an e-mail, insert the relevant data into these columns and commit.

On the Unix side you run a little shell script via cron - maybe every 15 minutes or so. The shell script runs SQL*Plus to select the first row's data into a spool file. The spool file format is your standard mail file format for the Unix mail command. SQL*Plus terminates and you use the mail command to mail the file. Oh yes, and you delete the 1st row after creating the spool file. Then the script suns again for the next row until there are no more rows left.

Lots of validation can be added. Including a column in the table for an error flag for example should the mail command fail.

Unfortunate I do not have access to a Unix box at the moment or I would have put a simple script for you together (hopefully we'll get our Unix dev box soon here). If you have any Unix shell script questions, please throw 'em this way via e-mail as it will be a bit off topic to discuss it here.

Database Pipes is a nice suggestion, but it requires all kinds of additional stuff on the backend as you need to run a dedicated server daemon. Using shell scripts via cron are much simpler. Stick to the basics IMHO.

regards,
Billy Received on Tue Oct 27 1998 - 08:07:41 CST