CREATE TABLE PAUSE_SCHED_JOBS 
( 
  JOB_OWNER        VARCHAR2(30 CHAR)            NOT NULL, 
  JOB_NAME         VARCHAR2(30 CHAR)            NOT NULL, 
  STOP_DATE_TIME   DATE                         NOT NULL, 
  START_DATE_TIME  DATE                         NOT NULL, 
  USER_ID          VARCHAR2(30 CHAR)            NOT NULL, 
  ACTIVITY_DATE    DATE                         NOT NULL 
)

CREATE UNIQUE INDEX PAUSE_SCHED_JOBS_PK ON PAUSE_SCHED_JOBS 
(JOB_OWNER, JOB_NAME, STOP_DATE_TIME, START_DATE_TIME)

ALTER TABLE PAUSE_SCHED_JOBS ADD ( 
  CONSTRAINT PAUSE_SCHED_JOBS_PK 
  PRIMARY KEY 
  (JOB_OWNER, JOB_NAME, STOP_DATE_TIME, START_DATE_TIME) 
  USING INDEX PAUSE_SCHED_JOBS_PK 
  ENABLE VALIDATE)

create or replace package job_mgmt as 
        PROCEDURE p_pause_job; 
end job_mgmt; 

create or replace package body job_mgmt as 
        -- ########################################################################## 
        -- PROCEDURE p_pause_job 
        -- This procedure is run every minute to check to see if any jobs need to be paused. 
        -- It will add to the comment of the job the phrase 'AUTO_PAUSE'. 
        -- 
        -- Revisions 
        -- 03/06/17 (BPF) - Created job 
        -- 
        PROCEDURE p_pause_job 
        IS 
            CURSOR cur_test 
            IS 
                SELECT * FROM all_scheduler_jobs; 
     
            rec_test        cur_test%ROWTYPE; 
     
     
            -- Find jobs that need to be disabled 
            CURSOR cur_pause_job 
            IS 
                SELECT all_scheduler_jobs.owner, 
                       all_scheduler_jobs.job_name, 
                       all_scheduler_jobs.comments 
                  FROM pause_sched_jobs, all_scheduler_jobs 
                 WHERE     pause_sched_jobs.job_name = 
                           all_scheduler_jobs.job_name 
                       AND pause_sched_jobs.stop_date_time < SYSDATE 
                       AND pause_sched_jobs.start_date_time > SYSDATE 
                       AND all_scheduler_jobs.enabled = 'TRUE'; 
     
            rec_pause_job   cur_pause_job%ROWTYPE; 
     
            -- Find jobs that need to be enabled 
            CURSOR cur_start_job 
            IS 
                SELECT all_scheduler_jobs.owner, 
                       all_scheduler_jobs.job_name, 
                       all_scheduler_jobs.comments 
                  FROM pause_sched_jobs, all_scheduler_jobs 
                 WHERE     pause_sched_jobs.job_name = 
                           all_scheduler_jobs.job_name 
                       AND pause_sched_jobs.start_date_time < SYSDATE 
                       AND all_scheduler_jobs.enabled = 'FALSE' 
                       AND all_scheduler_jobs.comments LIKE ('%AUTO_PAUSE%'); 
     
            rec_start_job   cur_start_job%ROWTYPE; 
        BEGIN 
            DBMS_OUTPUT.put_line ('Made It'); 
     
            OPEN cur_test; 
     
            FETCH cur_test INTO rec_test; 
     
            IF cur_test%FOUND 
            THEN 
                DBMS_OUTPUT.put_line ('ItWorks'); 
            END IF; 
     
            CLOSE cur_test; 
     
     
            OPEN cur_pause_job; 
     
            FETCH cur_pause_job INTO rec_pause_job; 
     
            WHILE cur_pause_job%FOUND 
            LOOP 
                DBMS_SCHEDULER.set_attribute ( 
                    name => 
                        rec_pause_job.owner || '.' || rec_pause_job.job_name, 
                    attribute => 
                        'comments', 
                    VALUE => 
                        rec_pause_job.comments || ' ' || 'AUTO_PAUSE'); 
     
                DBMS_SCHEDULER.disable ( 
                    rec_pause_job.owner || '.' || rec_pause_job.job_name); 
                DBMS_OUTPUT.put_line ('Job Paused'); 
     
                FETCH cur_pause_job INTO rec_pause_job; 
            END LOOP; 
     
            CLOSE cur_pause_job; 
     
            OPEN cur_start_job; 
     
            FETCH cur_start_job INTO rec_start_job; 
     
            WHILE cur_start_job%FOUND 
            LOOP 
                DBMS_SCHEDULER.enable ( 
                    rec_start_job.owner || '.' || rec_start_job.job_name); 
     
                DBMS_SCHEDULER.set_attribute ( 
                    name => 
                        rec_start_job.owner || '.' || rec_start_job.job_name, 
                    attribute => 
                        'comments', 
                    VALUE => 
                        REPLACE (rec_start_job.comments, ' AUTO_PAUSE', '')); 
                DBMS_OUTPUT.put_line ('Job Started'); 
     
                FETCH cur_start_job INTO rec_start_job; 
            END LOOP; 
     
            CLOSE cur_start_job; 
        END;                                              -- PROCEDURE p_pause_job 
    -- ########################################################################## 
end job_mgmt;