Sending email - Oracle 8.1.6

[nickty]

I am building a 'fault call' database using WebDB 2.2.
I need to automatically send an email to one address if the fault call is 'overdue', ie should have been completed in one day and has not.
I have used the desc utl_smtp command from Sql+ - so think it is installed.
I have no idea at all where to go from here - can anyone help please?

 

[karluk]

Please start by reading faq186-231.

 

[nickty]

I have read the FAQ - but as I am a complete novice- it does not make much sense to me.
Do I create the procedure first and somehow call it by a trigger?
Any help is appreciated.

 

[karluk]

I would create the email procedure first and test it to make sure it's working. You can do that in SQL*Plus, i.e. "execute email;"

Then I would create a second stored procedure. Its purpose would be to inspect your database, looking for overdue fault calls and calling the email procedure whenever it finds one.

After you get both procedures working, the final step is to automatically schedule the emails. You can do this doing dbms_job. For the basic syntax for doing this, please check out thread186-50358. That thread discusses how to schedule a procedure at 1:55pm daily. No doubt your schedule will be different, but the syntax should be similar.

I would not try to automate this through a trigger. Triggers run based on database activity, and your emails need to get sent based on lack of activity - exactly the opposite of what a trigger does best.