Re: DBMS_JOB.INTERVAL help needed

From: Thomas Kyte <thomas.kyte_at_oracle.com>
Date: 26 Mar 2005 07:23:02 -0800
Message-ID: <121850582.0000a156.023@drn.newsguy.com>

In article <eL81e.18443$BW5.2146_at_newssvr31.news.prodigy.com>, Randy Harris says...
>
>I'm trying to manage the interval with DBMS_JOB. I'd like to set a range of
>hours and limit the days.
>
>I tried developing my own function and using that, but couldn't get it to
>work. Is that supposed to be possible? I see in Tom Kyte's web site, he
>has an example that uses a CASE statement. I'm still working with 8i and
>have no CASE statement. Should I be able to use IF-THEN? What built-in
>functions are supposed to be available? The SQL functions? The PL/SQL
>functions? I'd appreciate a point in the right direction.
>

Well, 8i has case (not in plsql, but in SQL -- just like decode()). But if the complexity lends itself towards plsql, you have at least two approaches, one was shown by Michel Cadot, creating a standalone function that can be selected from dual (that is what the job queue process does, 'select ' || interval || ' from dual' in effect).

Or, your job can supply as an OUT parameter the next time it should run (from Expert one on one Oracle):

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 <<<== your what is here.
    :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
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation

Received on Sat Mar 26 2005 - 09:23:02 CST