Re: DBMS_JOB multiple schedules

If I understand you correctly, you want to schedule a number of commands - either stored procedures, SQL or anonymous PL/SQL blocks - by using a single job. Plus, you want only certain commands to run at certain times regardless of how many other times the job runs.

I recently had about 60 procedures that needed to be scheduled in jobs. 40 of those procedures needed to run every 4 hours. 18 of those procedures needed to run once a day. The final 2 needed to be run once a week.

The first thing I tried was to put all of the procedures into a large if statement which checked the day and time and only ran the procedures that were suppose to run at that time. That worked, but whenever new procedures were modified, the if statement would have to be updated and tested. And since the procedures were always being modified in some way - a few were being added, a few removed, the majority were changing times to run - I got tired of this.

What I finally did was to create a table to hold the names of the various procedures and the times they were to be ran. I then modified the job to create a cursor which would loop through all procedures that were determined needed to be ran at the current time and then run them.

The upshot to this is that I can add, delete or modify any procedure without having to change the job in any way.

I hope this helps.

Al

On Thu, 25 Jan 2001 17:42:43 GMT, casall_at_my-deja.com wrote:

>Hello!
>I was wondering if I could execute multiple DBMS_JOB commands in the
>same procedure. I want to run several scripts set at different time
>intervals preferably from the same procedure. Any comments are greatly
>appreciated.
>
>TIA casall
>
>
>Sent via Deja.com
>http://www.deja.com/
Received on Sat Jan 27 2001 - 08:55:22 CST