Advanced use of DBMS_JOB.SUBMIT ‘interval’ Parameter

The DBMS_JOB.SUBMIT procedure does not have the parameters to provide any exception rules to the standard interval that you would normally provide. However, it does allow for flexibility to allow just about any interval you want.

To explain, lets first list the definition of the DBMS_JOB_SUBMIT.
=========================
From:
Oracle9i Supplied PL/SQL Packages and Types Reference
Release 2 (9.2)
———————————-

DBMS_JOB.SUBMIT (
job OUT BINARY_INTEGER,
what IN VARCHAR2,
next_date IN DATE DEFAULT sysdate,
interval IN VARCHAR2 DEFAULT ‘null’,
no_parse IN BOOLEAN DEFAULT FALSE,
instance IN BINARY_INTEGER DEFAULT any_instance,
force IN BOOLEAN DEFAULT FALSE);

Parameters explanation
————————————————–
Table 20-2 SUBMIT Procedure Parameters

Parameter Description
- job Number of the job being run.

- what PL/SQL procedure to run.

- next_date Next date when the job will be run.

- interval Date function that calculates the next time to run the job. The default is NULL. This must evaluate to a either a future point in time or NULL.

- no_parse A flag. The default is FALSE. If this is set to FALSE, then Oracle parses the procedure associated with the job. If this is set to TRUE, then Oracle parses the procedure associated with the job the first time that the job is run. For example, if you want to submit a job before you have created the tables associated with the job, then set this to TRUE.

- instance When a job is submitted, specifies which instance can run the job.

- force If this is TRUE, then any positive integer is acceptable as the job instance. If this
is FALSE (the default), then the specified instance must be running; otherwise the routine raises
an exception.

==================================================

EXPLANATION OF INTERVAL PARAMETER
————————————————–
The interval parameter can be looked as an interval (reoccurring every so many time units), but it
actually is a algorithm to produce the next actual date/time that it is to actually run. As an
example, when the job gets finishes up, it uses the string passed in to the ‘interval’ parameter
to calculate the next time to run.
‘SYSDATE + (15/1440)’ would actually calculate out to 15 minutes from now (since SYSDATE
returns the current date/time).

Adding 1 to SYSDATE would be equal to adding 1 day. So we just want to add a fraction of a day.
Since 1440 minutes exist in a day, 1/144o would equal 1 minute…and 15/1440 would equal 15 minutes.

However to change this to every 15 minutes EXCEPT Saturday would be impossible in a one line
statement since we have to deal with conditional intervals. Instead replace the interval to a
line such as
‘NextRunTime’
where NextRunTime is a FUNCTION that you have created to return a DATE.

EXAMPLE CODE
————————————————–
Here is an example code for producing an interval of every 15 minutes EXCEPT for Saturday.

——————————— begin code —————————————-

CREATE OR REPLACE FUNCTION NextRunTime RETURN DATE as
v_nextDate DATE;
v_nowDate DATE;
v_today VARCHAR2(10);
begin

— get the current date/time
v_nowDate := SYSDATE;

— get the actual day (in character format) 15 minutes from now.
v_today := TRIM(to_char( v_nowDate + (15/1440), ‘DAY’));

— if the next interval ends up on Saturday, bump it up 1 day to Sunday (plus the normal 15 minutes)
— else go ahead and just go another 15 minutes.
if (v_today = ‘SATURDAY’) then
v_nextDate := v_nowDate + 1 + (15/1440); — Bump it up a day to sunday + our normal 15 minutes.
else
v_nextDate := v_nowDate + (15/1440);
end if;

return v_nextDate;
end;
/

———————————– end code —————————————–

USE OF THE DBMS_JOB.SUBMIT
————————————————–

To see this in context, you would submit it as such:

declare
v_jobno NUMBER(5);
begin
DBMS_JOB.SUBMIT(v_jobno, ‘MyJobProc;’, TRUNC(SYSDATE + 1), ‘NextRunTime’);
commit;
DBMS_OUTPUT.PUT_LINE(‘New Job No is: ‘ v_jobno);
end;

The above would submit the job MyJobProc the next day at midnight (TRUNC truncates off any time
portion), and uses the interval (next time) returned from NextRunTime.

SUBMITTING THE JOB
————————————————–

SQL> set serveroutput on
SQL> select to_char(SYSDATE, ‘DD-MON-YY’) as CURDATE from dual;

CURDATE
———
02-JUN-05

SQL> declare
2 v_jobno NUMBER(5);
3 begin
4 DBMS_JOB.SUBMIT(v_jobno, ‘MyJobProc;’, TRUNC(SYSDATE + 1), ‘NextRunTime’);
5 commit;
6 DBMS_OUTPUT.PUT_LINE(‘New Job No is: ‘ v_jobno);
7 end;
8 /
New Job No is: 58

PL/SQL procedure successfully completed.

SQL> select job, what, next_date, next_sec from user_jobs;

JOB WHAT NEXT_DATE NEXT_SEC
—– ————– —————– ——–
58 MyJobProc; 03-JUN-05 00:00:00

Notice that the aboves NEXT_DATE, and SECOND of run is Midnight Jun 03. This is the start time
listed in the ‘next_date’ parameter. When that job finishes, it will use ‘NextRunTime’ function
to calculate out the following run.

ALTERNATIVE OPTION
===========================

Optionally, for this example, there is another way that can be easier.
It does not make use of the ‘interval’ parameter as we have explained
above by using a defined function…which was the goal of this article.

The following option incorporates the ‘time logic’ inside that actual
submitted program.

DBMS_JOB.SUBMIT(v_jobno, ‘MyJobProc;’, TRUNC(SYSDATE + 1), ‘SYSDATE + (15/1440)’);

Which means start at midnight and start every 15 minutes thereafter.

In the actual ‘MyJobProc’ procedure, have the code near the top that checks
for the condition of SATURDAY.

CREATE OR REPLACE PROCEDURE MyJobProc AS

BEGIN

IF ( TRIM(to_char( SYSDATE, ‘DAY’)) = ‘SATURDAY’) THEN
RETURN;
END IF;



END;

This method WILL submit the job EVERY 15 minutes, however if it is Saturday
the program exits, and the job will be requeued to start in the next 15 minutes.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: