Oracle Tips and Techniques

This is very useful Oracle supplied package which is used to run scheduled job in Oracle databases. Released with Oracle 10g this package replaces DBMS_JOB which was available with previous version of Oracle databases. DBMS_JOB still runs in Oracle 10g and even in 11g but that is used only for backward compatibility and should not be used in developing new solutions. Where possible it is advisable to convert legacy jobs which uses DBMS_JOB to use more powerful and robust DBMS_SCHEDULER.

Like DBMS_JOB package, the DBMS_SCHEDULER is a collection of functions and procedures intended to make job scheduling in Oracle more robust and easy to use.

PRIVILEGES:
===========================

To create and runs job in Oracle database you will need CREATE JOB role. To perform administrative tasks you will need SCHEDULER_ADMIN role.

Depending on the requirement and your installation environment you may like to grant the following privileges:

CREATE ANY JOBS
CREATE EXTERNAL JOBS

CREATING A SCHEDULED JOB:
===========================

The package is supplied to help perform some database tasks such as running a stored procedure or package at some given time and at some given interval.

And example is here below:

dbms_scheduler.create_job( job_name=>’Myschema.Daily_Emp_Report’,
job_type=>’STORED_PROCEDURE’,
job_action=>’Myschema.p_daily_emp_teport’,
number_of_arguments=>0,
start_date=>TRUNC(SYSDATE),         repeat_interval=>’FREQ=WEEKLY;BYDAY=SUN;BYHOUR=07;BYMINUTE=00;  BYSECOND=00′,
end_date=>NULL,
job_class=> ‘DEFAULT_JOB_CLASS’,
enabled=>TRUE,
auto_drop=>FALSE,
comments=>NULL);

In this example I am creating a scheduled Oracle job named Myschema.Daily_Emp_Report. When run, this job will execute a STORED PROCEDURE called p_daily_emp_teport. The stored procedure p_daily_emp_teport resides in a schema called Myschema. The job will run on every week Sunday at 07:00.

Continue reading →