Schedule Jobs with Oracle DBMS_SCHEDULER

Like Windows Task Scheduler or Linux cron, Oracle also has a scheduler: DBMS_SCHEDULER.

Query schedulers

-- Query schedulers
SELECT JOB_NAME,
       REPEAT_INTERVAL,
       TO_CHAR(LAST_START_DATE, 'YYYY-MM-DD HH24:MI:SS'),
       TO_CHAR(NEXT_RUN_DATE, 'YYYY-MM-DD HH24:MI:SS')
FROM USER_SCHEDULER_JOBS
-- WHERE LOGGING_LEVEL = 'RUNS'
ORDER BY REPEAT_INTERVAL;

-- Query scheduler logs
SELECT *
FROM USER_SCHEDULER_JOB_LOG
WHERE JOB_NAME = 'JOB_SP_CUST_INAC_SMS';

Create a scheduler

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    JOB_NAME => 'JOB_SP_CUST_INAC_SMS',
    JOB_TYPE => 'PLSQL_BLOCK',
    JOB_CLASS => 'DEFAULT_JOB_CLASS',
    START_DATE => TO_TIMESTAMP_TZ(
      '2022/03/22 12:00:00.000000 +09:00',
      'YYYY/MM/DD HH24:MI:SS.FF TZR'
    ),
    JOB_ACTION => '
/* ---------------------------------------------
 * [Customer dormancy SMS] SP_CUST_INAC_SMS
 * Procedure : SP_CUST_INAC_SMS
 * Schedule  : every day at 03:00
 * */
DECLARE
  OUT_CODE VARCHAR2(256);  -- result code (0:Success, -1:Fail)
  OUT_MSG  VARCHAR2(4096); -- result message
BEGIN
  -- Get company codes
  DECLARE
    CURSOR COMP_DATA IS
      SELECT COMP_CD, COMP_KOR_NM
      FROM CFCOMP
      WHERE COMP_CLOSE_DT IS NULL
        AND COMP_CD = ''TEST001''
      ORDER BY BUSI_DT;
  BEGIN
    FOR CUR_COMP_DATA IN COMP_DATA LOOP
      BEGIN
        SP_CUST_INAC_SMS(
          CUR_COMP_DATA.COMP_CD,
          ''SYSTEM'',
          ''SYSTEM'',
          OUT_CODE,
          OUT_MSG
        );
      END;
    END LOOP;
  END;
END;
',
    REPEAT_INTERVAL => 'FREQ=DAILY;BYHOUR=03;BYMINUTE=00;BYSECOND=00',
    COMMENTS => 'SMS notice for customers scheduled for dormancy'
  );
END;

Options

-- Enable the scheduler
EXEC SYS.DBMS_SCHEDULER.ENABLE(NAME => 'JOB_SP_CUST_INAC_SMS');

-- Restart on error (default: FALSE)
EXEC SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
  NAME => 'JOB_SP_CUST_INAC_SMS',
  ATTRIBUTE => 'RESTARTABLE',
  VALUE => FALSE
);

-- Priority 1~5 (1 is highest, default: 3)
EXEC SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
  NAME => 'JOB_SP_CUST_INAC_SMS',
  ATTRIBUTE => 'JOB_PRIORITY',
  VALUE => 3
);

-- Auto-drop when completed or unused
EXEC SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
  NAME => 'JOB_SP_CUST_INAC_SMS',
  ATTRIBUTE => 'AUTO_DROP',
  VALUE => FALSE
);

Logging

• DBMS_SCHEDULER.LOGGING_OFF: no logs
• DBMS_SCHEDULER.LOGGING_FAILED_RUNS: log failed runs only
• DBMS_SCHEDULER.LOGGING_RUNS: log all runs (default)
• DBMS_SCHEDULER.LOGGING_FULL: log all runs and job actions such as create, enable, change, and stop

EXEC SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
  NAME => 'JOB_SP_CUST_INAC_SMS',
  ATTRIBUTE => 'LOGGING_LEVEL',
  VALUE => SYS.DBMS_SCHEDULER.LOGGING_RUNS
);

Run and drop

-- Run
EXEC DBMS_SCHEDULER.RUN_JOB('JOB_SP_CUST_INAC_SMS');

-- Drop
BEGIN
  DBMS_SCHEDULER.DROP_JOB(
    JOB_NAME => 'JOB_SP_CUST_INAC_SMS',
    FORCE => FALSE
  );
END;

Full source

-- Check schedulers
SELECT JOB_NAME,
       REPEAT_INTERVAL,
       TO_CHAR(LAST_START_DATE, 'YYYY-MM-DD HH24:MI:SS'),
       TO_CHAR(NEXT_RUN_DATE, 'YYYY-MM-DD HH24:MI:SS')
FROM USER_SCHEDULER_JOBS
-- WHERE LOGGING_LEVEL = 'RUNS'
ORDER BY REPEAT_INTERVAL;

-- Check scheduler logs
SELECT *
FROM USER_SCHEDULER_JOB_LOG
WHERE JOB_NAME = 'JOB_SP_CUST_INAC_SMS';

-- Create scheduler
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    JOB_NAME => 'JOB_SP_CUST_INAC_SMS',
    JOB_TYPE => 'PLSQL_BLOCK',
    JOB_CLASS => 'DEFAULT_JOB_CLASS',
    START_DATE => TO_TIMESTAMP_TZ(
      '2021/03/24 12:00:00.000000 +09:00',
      'YYYY/MM/DD HH24:MI:SS.FF TZR'
    ),
    JOB_ACTION => '
/* ---------------------------------------------
 * [Customer dormancy SMS] SP_CUST_INAC_SMS
 * Procedure : SP_CUST_INAC_SMS
 * Schedule  : every day at 03:00
 * */
DECLARE
  OUT_CODE VARCHAR2(256);  -- result code (0:Success, -1:Fail)
  OUT_MSG  VARCHAR2(4096); -- result message
BEGIN
  -- Get company codes
  DECLARE
    CURSOR COMP_DATA IS
      SELECT COMP_CD, COMP_KOR_NM
      FROM CFCOMP
      WHERE COMP_CLOSE_DT IS NULL
        AND COMP_CD = ''TEST001''
      ORDER BY BUSI_DT;
  BEGIN
    FOR CUR_COMP_DATA IN COMP_DATA LOOP
      BEGIN
        SP_CUST_INAC_SMS(
          CUR_COMP_DATA.COMP_CD,
          ''SYSTEM'',
          ''SYSTEM'',
          OUT_CODE,
          OUT_MSG
        );
      END;
    END LOOP;
  END;
END;
',
    REPEAT_INTERVAL => 'FREQ=DAILY;BYHOUR=03;BYMINUTE=00;BYSECOND=00',
    COMMENTS => 'SMS notice for customers scheduled for dormancy'
  );
END;

-- Enable scheduler
EXEC SYS.DBMS_SCHEDULER.ENABLE(NAME => 'JOB_SP_CUST_INAC_SMS');

-- Restart on error (default: false)
EXEC SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
  NAME => 'JOB_SP_CUST_INAC_SMS',
  ATTRIBUTE => 'RESTARTABLE',
  VALUE => FALSE
);

-- Priority 1~5 (1 is highest, default: 3)
EXEC SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
  NAME => 'JOB_SP_CUST_INAC_SMS',
  ATTRIBUTE => 'JOB_PRIORITY',
  VALUE => 3
);

/*
Logging
DBMS_SCHEDULER.LOGGING_OFF : no logs
DBMS_SCHEDULER.LOGGING_FAILED_RUNS : failed runs only
DBMS_SCHEDULER.LOGGING_RUNS : all runs (default)
DBMS_SCHEDULER.LOGGING_FULL : all runs and job actions
*/
EXEC SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
  NAME => 'JOB_SP_CUST_INAC_SMS',
  ATTRIBUTE => 'LOGGING_LEVEL',
  VALUE => SYS.DBMS_SCHEDULER.LOGGING_RUNS
);

-- Auto-drop when completed or unused
EXEC SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
  NAME => 'JOB_SP_CUST_INAC_SMS',
  ATTRIBUTE => 'AUTO_DROP',
  VALUE => FALSE
);

-- Run
EXEC DBMS_SCHEDULER.RUN_JOB('JOB_SP_CUST_INAC_SMS');

-- Drop
BEGIN
  DBMS_SCHEDULER.DROP_JOB(
    JOB_NAME => 'JOB_SP_CUST_INAC_SMS',
    FORCE => FALSE
  );
END;