Script for PL/SQL jobs in automated scheduling system

How you set up a PL/SQL job to run depends on your Oracle version.

In Oracle 8i and 9i, you first need to have your JOB_QUEUE_PROCESSES initialization parameter set to a value larger than 0. This parameter specifies the number of SNPn (snapshot) processes that are responsible for running your PL/SQL jobs. This parameter can accept values from 0 to 36.

To manage your jobs in Oracle 8i and 9i, you can use the DBMS_JOB supplied package. The SUBMIT procedure of this package is used to create a job in the job queue. The following is an example of submitting a job to the job queue in SQL*Plus. This job will execute the REORG_PROC stored procedure today, and repeat it every week.

VARIABLE jobno
EXEC DBMS_JOB.SUBMIT(:jobno, reorg_proc, - 
   SYSDATE, SYSDATE + 7);

Any stored procedure can be scheduled for execution in this manner.

The REMOVE procedure of the DBMS_JOB package can be used to remove a scheduled job from the job queue.

Oracle 10g introduced the DBMS Scheduler as a replacement for DBMS_JOB. The DBMS_SCHEDULER package has much more advanced functionality than DBMS_JOB. To schedule the same job above with DBMS_SCHEDULER, use the following command in SQL*Plus for your Oracle 10g database:

EXEC DBMS_SCHEDULER.CREATE_JOB ( -
   job_name => 'reorg_job', -
   job_type => 'STORED_PROCEDURE', -
   job_action => 'BEGIN reorg_proc(); END;' -
   start_date => SYSDATE, -
   repeat_interval => 'FREQ=WEEKLY');

Additionally, Oracle 10g has a nice method of scheduling jobs with Oracle's Enterprise Manager (OEM), a GUI tool for managing your database.