I am trying to determine a way where we can schedule time periods for when select dbms_scheduler jobs will automatically get "paused". I came up with a procedure (see LiveSQL Link) that works fine when run as an anonymous block. It successfully disables the job if the current date/time is greater than what is listed in a user table (PAUSE_SCHED_JOBS). When the anonymous script is run after the "start_date_time", then the job automatically gets enabled.
However, if I make the exact procedure into a package with the procedure in it (JOB_MGMT), the procedure does not seem to be able to read from the view all_scheduler_jobs.
In other words
------------------------------------------
Declare
cursor cur_test...
cursor cur_pause_job...
cursor cur_start_job...
Begin
.....
End;
------------------------------------------
will work fine. But this:
------------------------------------------
DECLARE
BEGIN
job_mgmt.p_pause_job;
END;
------------------------------------------
Will not.
When run in the package, I do see that it is running the procedure as dbms_output does report "Made It". However, the all_scheduler_jobs cursor does not ever come back as found ("ItWorks" is not in the output).
I have stumped our DBA on this, so I am hoping that you could help.
Thank you,
Bryon Freeman
Programmer/Analyst
PS - If there is another way to accomplish this same task, I'd be open to that as well.
I'm not sure what's going on with your code. But rather than fixing that, there's a better option:
Windows!
These define periods of time you want to allow jobs to run. So instead of saying "pause during these hours", you say "run during these hours".
Ensure you set the stop_on_window_close property to true so the database will kill off any jobs still running when the window closes:
BEGIN
DBMS_SCHEDULER.CREATE_WINDOW (
window_name => 'short_window',
resource_plan => null,
start_date => systimestamp,
repeat_interval => 'freq=daily; byday=mon,tue,wed,thu,fri',
duration => interval '1' minute);
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'STOPJOB',
job_type => 'PLSQL_BLOCK',
job_action => 'begin loop dbms_lock.sleep(1); end loop; end;',
schedule_name => 'sys.SHORT_WINDOW'
);
DBMS_SCHEDULER.SET_ATTRIBUTE (
name => 'STOPJOB',
attribute => 'stop_on_window_close',
value => true
);
DBMS_SCHEDULER.ENABLE ('STOPJOB');
END;
/
select active, duration, last_start_date
from dba_scheduler_windows w
where window_name = 'SHORT_WINDOW';
ACTIVE DURATION LAST_START_DATE
TRUE +00 00:01:00.000000 26-JAN-2018 03.25.55
select job_name from USER_SCHEDULER_RUNNING_JOBS;
JOB_NAME
STOPJOB
/* Wait a minute ... */
select status, run_duration, additional_info
from user_scheduler_job_run_details
where job_name = 'STOPJOB';
STATUS RUN_DURATION ADDITIONAL_INFO
STOPPED +00 00:00:57.000000 REASON="Stop job called because associated window was closed" Note that the window goes in the schedule_name for the job. Also sys owns all windows, so qualify the name with this.