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