[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;