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.  

Back to index of questions


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


Back to top

Back to index of questions