Ask the Expert

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?

    Requires Free Membership to View

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.

This was first published in April 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: