29/11/2014

[Oracle DB] Create and schedule a job

To create and schedule a basic job on an Oracle DB, you'll need to create:
  • A script performing the desired action
  • A schedule
  • A job
The script can be passed as code directly to the job, a function/procedure, a package function/procedure.

Step 1: Create package with our function

CREATE OR REPLACE PACKAGE myPackage AS   
PROCEDURE myProcedure;  
END;

CREATE OR REPLACE PACKAGE BODY myPackage AS
PROCEDURE myProcedure AS
--variable declaration here
BEGIN
  --something
  NULL;--not actually needed
END myProcedure;
END;

Step 2: Create schedule and job. Refer to the linked documentation for more information regarding the various parameters

 BEGIN  
   
 --create schedule  
 DBMS_SCHEDULER.CREATE_SCHEDULE (           
      repeat_interval => 'FREQ=YOUR_FREQUENCY',     
      start_date => TO_TIMESTAMP('START_DATE', 'FORMAT'),  
      schedule_name => '"mySchedule"');  
       
 --create job on mySchedule  
 DBMS_SCHEDULER.CREATE_JOB (  
       job_name => 'myJob',  
       job_type => 'PLSQL_BLOCK',  
       schedule_name => '"mySchedule"',  
       job_action => 'BEGIN myPackage.myProcedure; END;',  
       number_of_arguments => 0,  
       job_class => 'DEFAULT_JOB_CLASS',  
       enabled => true,  
       auto_drop => true,  
       comments => 'SOME_DESCRIPTION');  
 END;  
   

No comments:

Post a Comment

With great power comes great responsibility