Press "Enter" to skip to content

Oracle PL/SQL: DBMS_SCHEDULER.CREATE_JOB Example

The requirement is to create a job in Oracle to run daily at 3:00 AM and execute a stored procedure to perform a certain task. For example, the stored procedure name is PROC_DAILY_UPDATES.

To achieve this, the following example uses Oracle’s DBMS_SCHEDULER.CREATE_JOB procedure:

1. Create a Job in Oracle using DBMS_SCHEDULER.CREATE_JOB

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'JOB_DAILY_UPDATES',
   job_type           =>  'STORED_PROCEDURE',
   job_action         =>  'YOURSCHEMA.PROC_DAILY_UPDATES',
   start_date         =>  '28-SEP-19 03.00.00 AM America/New_York',
   repeat_interval    =>  'FREQ=DAILY;INTERVAL=1', 
   end_date           =>  NULL,
   auto_drop          =>   FALSE,
   job_class          =>  'SYS.DEFAULT_JOB_CLASS',
   comments           =>  'extract data');
END;
/

After creating the above job, enable it:

2. Enable the Job using DBMS_SCHEDULER.ENABLE

BEGIN 
    DBMS_SCHEDULER.enable('JOB_DAILY_UPDATES'); 
END;
/

To confirm the job created successfully use the following query:

Select * from user_scheduler_jobs 
 where job_name = 'JOB_DAILY_UPDATES';

From the above query you will get the information like last start date, next run date, and job enables status, etc.

To get the job execution history use the following query:

Select * from user_scheduler_job_run_details 
  where job_name = 'JOB_DAILY_UPDATES';

You can also notice that in the above DBMS_SCHEDULER.CREATE_JOB procedure example, I used the timezone America/New_York. You can get the timezone of a specific country using the following query:

SELECT DISTINCT tzname, TZ_OFFSET (tzname)
 FROM V$TIMEZONE_NAMES
ORDER BY tzname;

Output

America/Mexico_City -05:00 
America/Miquelon -02:00 
America/Moncton -03:00 
America/Monterrey -05:00 
America/Montevideo -03:00 
America/Montreal -04:00 
America/Montserrat -04:00 
America/Nassau -04:00 
America/New_York -04:00
...

See also: