Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Script for PL/SQL jobs in automated scheduling system

I need to set up PL/SQL jobs in an automated job scheduling system. What does the script that I would run look like?
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.

EXEC DBMS_JOB.SUBMIT(:jobno, reorg_proc, - 
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:

   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.
This was last published in April 2005

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.