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

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 15 Aug 2001 18:10:03 -0700
Message-ID: <9lf6hb01cmf@drn.newsguy.com>

In article <yCDe7.18029$NK1.1070125_at_bin3.nnrp.aus1.giganews.com>, "Brian says...
>
>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? 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). 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?
>
>Thanks in advance.
>
>B~
>
>

This can be done easily. It is not commonly known that INTERVAL is optional -- you can have the job itself compute procedurally the next time it wants to run!

Here is a short extract from a 13 page chapter in my book (hence references to things like "the above", "previously", etc) on dbms_job, it shows how to do this:

...
Custom Scheduling

There are times, such as the above, where the NEXT_DATE is hard to compute in a simple SQL statement or where the next time the job runs is dependent on some complex procedural set of rules. In this case, we can have the job itself set the next date to run.

If you recall from above, the PLSQL block that runs a job is:

DECLARE
   job BINARY_INTEGER := :job;
   next_date DATE := :mydate;
   broken BOOLEAN := FALSE;
BEGIN
    WHAT
    :mydate := next_date;
    IF broken THEN :b := 1; ELSE :b := 0; END IF; END; We have already seen how we can make use of the fact that JOB is available there in the ?running a job once? section. We can use it as a primary key into a parameter table to make maximum use of Shared SQL. Well, we can also make use of the NEXT_DATE variable as well. As you can see in the above block of code, Oracle uses the bind variable :mydate as an INPUT into the routine ? to set the NEXT_DATE variable ? but it also retrieves this value after WHAT (your procedure) executes. If your procedure happens to modify this value ? the value of NEXT_DATE ? Oracle will use that as the next date to run the job. As an example, we?ll set up a small procedure P that will write some informative message to a table T and set its NEXT_DATE:

tkyte_at_TKYTE816> create table t ( msg varchar2(80) ); Table created.

tkyte_at_TKYTE816> create or replace
  2 procedure p( p_job in number, p_next_date in OUT date )   3 as
  4 l_next_date date default p_next_date;   5 begin
  6 p_next_date := trunc(sysdate)+1+3/24;   7

  8      insert into t values
  9      ( 'Next date was "' ||
 10         to_char(l_next_date,'dd-mon-yyyy hh24:mi:ss') ||
 11        '" Next date IS ' ||
 12         to_char(p_next_date,'dd-mon-yyyy hh24:mi:ss') );

13 end;
14 /
Procedure created.

Now, we will schedule this job using the method from ?running a job once? ? that is, without an INTERVAL:

tkyte_at_TKYTE816> variable n number

tkyte_at_TKYTE816> exec dbms_job.submit( :n, 'p(JOB,NEXT_DATE);' ); PL/SQL procedure successfully completed.

tkyte_at_TKYTE816> select what, interval,

  2         to_char(last_date,'dd-mon-yyyy hh24:mi:ss') last_date,
  3         to_char(next_date,'dd-mon-yyyy hh24:mi:ss') next_date

  4 from user_jobs
  5 where job = :n
  6 /

WHAT                      INTERVAL LAST_DATE            NEXT_DATE
------------------------- -------- -------------------- --------------------
p(JOB,NEXT_DATE);         null                          28-apr-2001 18:23:01

In this case, we send the JOB and the NEXT_DATE as parameters to our procedure ? these will be supplied by the job queue at runtime. As you can see ? this job has not yet run (LAST_DATE is NULL), the INTERVAL is set to null so that the NEXT_DATE will be computed as ?select null from dual?. Normally that means the job would run once and be removed from the job queue. However, when this job runs, we?ll discover:

tkyte_at_TKYTE816> exec dbms_job.run( :n ); PL/SQL procedure successfully completed.

tkyte_at_TKYTE816> select * from t;

MSG

Next date was "" Next date IS 29-apr-2001 03:00:00

tkyte_at_TKYTE816> select what, interval,

  2         to_char(last_date,'dd-mon-yyyy hh24:mi:ss') last_date,
  3         to_char(next_date,'dd-mon-yyyy hh24:mi:ss') next_date

  4 from user_jobs
  5 where job = :n
  6 /

WHAT                      INTERVAL LAST_DATE            NEXT_DATE
------------------------- -------- -------------------- --------------------
p(JOB,NEXT_DATE);         null     28-apr-2001 18:23:01 29-apr-2001 03:00:00

That the NEXT_DATE is filled in ? it is the NEXT_DATE computed in the procedure itself ? and the job is still in the queue. As long as this job continues to fill in the NEXT_DATE field, it will remain in the job queue. If it ever exits successfully without setting NEXT_DATE ? it will be removed from the queue.

This is very useful for those jobs with hard to compute NEXT_DATE values or NEXT_DATE values that depend on data found in other database tables.

............

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Wed Aug 15 2001 - 20:10:03 CDT