Re: DBMS_JOB at 1pm, 3pm and 8:30 pm ???

On Wed, 15 Aug 2001, nosp_at_m wrote:

> How do I call DBMS_JOB.SUBMIT such that my procedure runs every
> weekday at some arbitrary times? Suppose my_proc should run
> weekdays at 1PM, 3PM and 8:30PM. How could I format the
> interval string to deal with this?

I don't believe this is directly possible.

> What I DON'T want to do is submit three different jobs -- one
> for each time. While I realize that's easier, I've discovered
> there are issues if the database goes down (if it goes down for
> a whole day, then three jobs try to start at once when it comes
> back up).

Maybe, in the definition of the job queue, the job's proc can be passed the parameter of start and end time range. Then, the proc could check whether it is supposed to execute or not based on the current time. Then, an unlimited number of jobs could be created and you wouldn't run into your issue.

> Anyway, I just want one job to manage this.
>
> One thing I tried was to write an external function (call it
> get_next_runtime) that returns a date. So,
> DBMS_JOB.SUBMIT(job_id, my_proc, sysdate,
> 'get_next_runtime(sysdate)').
>
> This doesn't seem to work. Any ideas why?

It seems on the surface, that it should work.

Just checking that your function behaves like any other Oracle function. Can you use your function and return something by the SQL:         select get_next_runtime(sysdate) from dual;

If so, what does this return? Is it some date in the future?

-- 
Galen Boyer
It seems to me, I remember every single thing I know.