Q

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.

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

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close