ORACLE: scheduled task (oracle scheduler)

ORACLE - Scheduled task (Oracle scheduler)

Oracle, PL/SQL

1. Create a program

Procedure name: READ_FILES_PROC

begin
dbms_scheduler.create_program(
       program_name => 'READ_FILES_PROG'
      ,program_type => 'STORED_PROCEDURE'
      ,program_action => 'READ_FILES_PROC'
      ,number_of_arguments => 0
      ,enabled => true
      ,comments => 'Read files from directory' );
end;

2. Create a schedule

Additional parameters info:

https://docs.oracle.com/cd/B28359_01/server.111/b28310/scheduse004.htm#ADMIN12412

BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE (
  schedule_name     => 'READ_FILES_SCH',
  start_date        => TO_TIMESTAMP('2014-06-28 06:15:00','YYYY-MM-DD HH24:Mi:SS'),
  end_date          => TO_DATE('9999-12-31','YYYY-MM-DD'),
  repeat_interval   => 'FREQ=MINUTELY; INTERVAL=15',
  comments          => 'Every 15 minutes');
END;

3. Create a job

begin
  dbms_scheduler.create_job(
       job_name => 'READ_FILES_JOB'
     , program_name =>'READ_FILES_PROG'
     , schedule_name =>'READ_FILES_SCH'
     , enabled => TRUE
     , comments => 'Read files every 15 minutes');
end;

4. Enable log

begin
DBMS_SCHEDULER.SET_ATTRIBUTE('READ_FILES_JOB','logging_level',DBMS_SCHEDULER.LOGGING_FULL);
end;

5.  Restart job and read log

Disable job

begin
DBMS_SCHEDULER.DISABLE('READ_FILES_JOB',TRUE);
end;

Enable job

begin
DBMS_SCHEDULER.ENABLE('READ_FILES_JOB');
end;

Display log

select * from dba_scheduler_jobs where owner like 'SCHEMA NAME';