The Oracle (tm) Users' Co-Operative FAQ
Can Oracle call a batch job from a procedure?
|
Author's name: Keith_Jamieson Author's Email: Keih_Jamieson@hotmail.com |
Date written: 22 Sep 2003 Oracle version(s): 9.2.0.1.0 |
|
There are numerous reasons why you may want to call a batch job from a
procedure, but the problem lies really in exactly what your ultimate
objective is. The answer, to this
question, in
most cases is yes, however, depending on your requirements, you may need to
do a little more work than originally anticipated. |
Oracle itself actually submits batch jobs to schedule tasks like refreshing Materialized Views, Snapshots, and is also used extensively by tools such as Discoverer. Oracle uses the DBMS_JOB built-in PL/SQL package as its job-scheduler, and the main advantage of this is that it is platform independent. Suppose I have a procedure which I know takes 2 hours to run, and I need to run it during the evening. All I have to do is submit a JOB. Before I can do this, I need to set two parameters job_queue_interval and job_queue_processes. The job_queue interval determines how often I want to look for jobs to execute, while the job_queue_processes defines the number of processes available to execute the jobs.
DECLARE
v_job NUMBER;
BEGIN
DBMS_JOB.SUBMIT(
Jobno =>v_job,
WHAT =>’long_running_procedure;’
NEXT_DATE =>trunc(sysdate)+1+3/24,
INTERVAL =>’trunc(sysdate)+1+3/24’
);
COMMIT;
END;
Note,
that the jobno is an output parameter,
that the procedure name must be placed in quotes and the semi-colon before
the last quote is mandatory. Also the Job will not be submitted unless you
commit the Job. In the example above we have set the time of execution to be trunc(sysdate) ie midnight lastnight + 1 Day (midnight this evening) + 3/24 (3 Hours).
So Our JOB will execute at 3 am tomorrow morning. If we only wanted to submit the Job once, we
would have set out Interval to NULL (or not included the parameter at
all). The reason we have trunc’ed the sysdate is to
prevent a sliding window. Jobs do not execute exactly at the time specified. If
the Job_queue_interval has been set to inspect the
job queue every 5 minutes then our job may not be picked up until 3.05 am. If we do not trunc
the sysdate for a repeating job, the job might slip
by 5 minutes every day, and as a result, will eventually start running during
working hours.
If you want to schedule something to happen while the database is down, then DBMS_JOB is not the solution, and you will have to look at your operating system scheduler. If you want to kick off some external piece of code, then you are into the realms of Java Stored Procedures or External Procedures. In essence all you have to do is to have a calling procedure call this code, and schedule the calling procedure using DBMS_JOB.
Further reading: http://asktom.oracle.com, “ExpertOne-on-One” Thomas Kyte
