The current job schedules for this example can
be queried using the
job_queue_query.sql
script listed below.
set feedback off
alter session set
nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
alter session set
nls_timestamp_format = 'DD-MON-YYYY
HH24:MI:SS.FF';
alter session set
nls_timestamp_tz_format = 'DD-MON-YYYY
HH24:MI:SS.FF TZH:TZM';
set feedback on
set linesize 100
column created_timestamp
format a27
column next_run_date
format a34
column next_date format
a20
prompt
prompt USER_SCHEDULER_JOBS
select
job_name,
enabled,
next_run_date
from
user_scheduler_jobs
order by
job_name;
prompt USER_JOBS
select
job,
broken,
next_date
from
user_jobs
order by
job;
The output of this script along with the output
of the
job_chain_query.sql
script is listed next:
SQL> @job_chain_query.sql
no rows selected
SQL> @job_queue_query.sql
USER_SCHEDULER_JOBS
JOB_NAME
ENABL NEXT_RUN_DATE
---------------------------
----- ----------------------------
JOB_CHAIN_ENABLE_TASK_1
TRUE
08-AUG-2004 06:00:00.800000 +01:00
JOB_CHAIN_ENABLE_TASK_2
FALSE
JOB_CHAIN_ENABLE_TASK_3
FALSE
3 rows selected.
At this point, the first task is scheduled but
has not been executed, hence no results in the
job_chain table.
Rather than waiting until 6:00, it can be
forced to run immediately.
The results below show that the first
task has run and the second job has been
enabled.
SQL> exec
dbms_scheduler.run_job
('job_chain_enable_task_1');
PL/SQL procedure
successfully completed.
SQL> @job_queue_query.sql
USER_SCHEDULER_JOBS
JOB_NAME
ENABL NEXT_RUN_DATE
---------------------------
----- ----------------------------
JOB_CHAIN_ENABLE_TASK_1
TRUE
08-AUG-2004 06:00:00.800000 +01:00
JOB_CHAIN_ENABLE_TASK_2
TRUE
08-AUG-2004 12:00:00.200000 +01:00
JOB_CHAIN_ENABLE_TASK_3
FALSE
USER_JOBS
no rows selected
SQL> @job_chain_query.sql
CREATED_TIMESTAMP
TASK_NAME
---------------------------
--------------------
07-AUG-2004
13:52:28.227000 TASK_1
Next, run the second job manually.
The results below show that the second
task has run and the third job has been enabled.
SQL> exec
dbms_scheduler.run_job
('job_chain_enable_task_2');
PL/SQL procedure
successfully completed.
SQL> @job_chain_query.sql
CREATED_TIMESTAMP
TASK_NAME
---------------------------
--------------------
07-AUG-2004
13:52:28.227000 TASK_1
07-AUG-2004
13:59:16.666000 TASK_2
SQL> @job_queue_query.sql
USER_SCHEDULER_JOBS
JOB_NAME
ENABL NEXT_RUN_DATE
--------------------------
----- -----------------------------
JOB_CHAIN_ENABLE_TASK_1
TRUE
08-AUG-2004 06:00:00.800000 +01:00
JOB_CHAIN_ENABLE_TASK_2
TRUE
08-AUG-2004 12:00:00.200000 +01:00
JOB_CHAIN_ENABLE_TASK_3
TRUE
07-AUG-2004 18:00:00.700000 +01:00
USER_JOBS
no rows selected
Next, run the third job manually.
The results below show that the third
task has run successfully.
SQL> exec
dbms_scheduler.run_job
('job_chain_enable_task_3');
PL/SQL procedure
successfully completed.
SQL> @job_chain_query.sql
CREATED_TIMESTAMP
TASK_NAME
---------------------------
--------------------
07-AUG-2004
13:52:28.227000 TASK_1
07-AUG-2004
13:59:16.666000 TASK_2
07-AUG-2004
14:02:10.948000 TASK_3
SQL> @job_queue_query.sql
USER_SCHEDULER_JOBS
JOB_NAME
ENABL NEXT_RUN_DATE
---------------------------
----- ----------------------------------
JOB_CHAIN_ENABLE_TASK_1
TRUE
08-AUG-2004 06:00:00.800000 +01:00
JOB_CHAIN_ENABLE_TASK_2
TRUE
08-AUG-2004 12:00:00.200000 +01:00
JOB_CHAIN_ENABLE_TASK_3
TRUE 07-AUG-2004 18:00:00.700000 +01:00
USER_JOBS
no rows selected
Finally, run the first job again to see that the
subsequent jobs have been enabled or disabled
appropriately.
SQL> exec
dbms_scheduler.run_job
('job_chain_enable_task_1');
PL/SQL procedure
successfully completed.
SQL> @job_chain_query.sql
CREATED_TIMESTAMP
TASK_NAME
---------------------------
--------------------
07-AUG-2004
14:03:55.683000 TASK_1
SQL> @job_queue_query.sql
USER_SCHEDULER_JOBS
JOB_NAME
ENABL NEXT_RUN_DATE
---------------------------
----- ----------------------------
JOB_CHAIN_ENABLE_TASK_1
TRUE
08-AUG-2004 06:00:00.800000 +01:00
JOB_CHAIN_ENABLE_TASK_2
TRUE
08-AUG-2004 12:00:00.700000 +01:00
JOB_CHAIN_ENABLE_TASK_3
FALSE
07-AUG-2004
18:00:00.700000 +01:00
USER_JOBS
no rows selected
Care must be taken when running the pre-10g
version of this code due to the way the broken
procedure works.
When a job has its broken flag set to
FALSE, its next run date is set to the value
specified by the
next_date
parameter.
If this is not specified, it defaults to
the current datetime.
As a result, the enabled job will not run
at the expected time.
In this example, the
next_date parameter has been
specified as a two-minute interval for the
convenience of testing, but in a real example,
it must be set to an appropriate datetime value.
 |
Fo
r more details on Oracle utilities, see the book "Advanced
Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.
You can buy it direct from the publisher for 30% off directly from
Rampant TechPress.
|
