Any Easy Way to Email Query Results at Set Times?

[smedvid]

I have a requirement to have a query execut each morning. Then if there are results, e-mail the query results to a couple of people. I know it can be done, but I am looking for a short and simple script to execute the process... Any ideas are appreciated. tia,

Steve Steve Medvid
"IT Consultant & Web Master"
e-Mail: Stephen_Medvid@GMACM.com

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[ChrisHunt]

You can run a PL/SQL procedure, and hence a query, at set times using the DBMS_JOB built-in package (it needs to have some values set up in init.ora in order to work though). I don't know how to send an email from within a PL/SQL procedure - suspect it would depend heavily upon your particular environment.

Alternatively you could write a script that would execute some SQL through SQL*Plus, spool the output into a file and send the result in an email. This could then be emailed out. Exactly how to do this would depend on what platform you're on - it'd be pretty easy on Unix using cron (for the scheduling) and sendmail (for, well, I'll let you guess).

Hopefuly that gives you something to go on. -- Chris Hunt
Extra Connections Ltd

http://www.extracon.com

 

[jee]

You can do this with a combination of features.

First, use the DBMS_JOB package to schedule your script to be run on a daily basis. Within your script, use the UTL_SMTP package to email the results.

The "Supplied PL/SQL Packages" manual has examples of using both packages.

I would suggest writing a stored procedure to do the emailing and then invoke that in the script you schedule for periodic execution.

 

[smedvid]

thanks for the starting point... Steve Steve Medvid
"IT Consultant & Web Master"
e-Mail: Stephen_Medvid@GMACM.com

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...