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

Galen Boyer wrote:
>
> 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.

It is very possible...Just code your procedure to accept an in/out parameter of type date. For example.

create procedure MY_JOB_PROC(p_date in out date) is begin
  ... the code ...
  if p_date = 1pm then

      p_date := 3pm;
  elsif p_date = 3pm then

      p_date := 8:30pm;
  etc
  etc
end;

and then submit your job with:

dbms_job.submit(v_job, 'MY_JOB_PROC(next_date);');

See

http://www.jlcomp.demon.co.uk/faq/dbms_job.html

for an article I wrote on it.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."