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

"Brian Y." <nosp_at_m> writes:
> 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

Two possible approaches come to my mind: - for each time of a day, make a separate job using intervals like   to_date(to_char(sysdate+1,'dd.mm.yyyy ') || '06:00:01','dd.mm.yyyy hh24:mi:ss')   for 06 am and
  to_date(to_char(sysdate+1,'dd.mm.yyyy ') || '14:30:01','dd.mm.yyyy hh24:mi:ss')   for 4:30 pm. These will of course run every day but it would be simple   to make the procedure in question to check if this is a good day to be run,   if day of a week is not correct, the procedure would simply "return;"   but this is of course ugly way, as it requires modification of the   procedure.
- create a database function that returns the next date. use this function   as 'interval' and when submitting the job, do remember to specify   the schema of the user where the function resides, otherwise dbms_job   will not find it.. (took me some time to figure this out :)   ..like something like this:

  create or replace function interval_function return date is   begin
    if to_number(to_char(sysdate,'hh24')) between 06 and 14 then

• this if evaluates true when run at 06:30 return to_date(to_char(sysdate,'dd.mm.yyyy ') || '14:30:01','dd.mm.yyyy hh24:mi:ss') ; end if ; if to_number(to_char(sysdate,'hh24')) between 14 and 23 then return to_date(to_char(sysdate+1,'dd.mm.yyyy ') || '06:00:01','dd.mm.yyyy hh24:mi:ss') ; end if ; end ; / please note that if your database is down whole day, then at 1 am you get it up and the job queued to be run at 14:30 is run at that time this function is evaluated also and as hour is 1 none of the conditions in the function is true, the function will return NULL and the job will not be run anymore so don't use this function, instead invent a better one that has no gaps.

-- 
Antti Järvinen, costello_at_iki.fi
            "concerto for two faggots and orchestra"