1 PACKAGE dbms_scheduler AUTHID CURRENT_USER AS
2
3 -- allowed job logging levels
4 logging_off CONSTANT PLS_INTEGER := 32;
5 logging_runs CONSTANT PLS_INTEGER := 64;
6 logging_failed_runs CONSTANT PLS_INTEGER := 128;
7 logging_full CONSTANT PLS_INTEGER := 256;
8
9 -- defaults for job e-mail notification
10 default_notification_subject CONSTANT VARCHAR2(100) :=
11 'Oracle Scheduler Job Notification - %job_owner%.%job_name%.%job_subname% %event_type%';
12
13 default_notification_body CONSTANT VARCHAR2(300) :=
14 'Job: %job_owner%.%job_name%.%job_subname%
15 Event: %event_type%
16 Date: %event_timestamp%
17 Log id: %log_id%
18 Job class: %job_class_name%
19 Run count: %run_count%
20 Failure count: %failure_count%
21 Retry count: %retry_count%
22 Error code: %error_code%
23 Error message:
24 %error_message%
25 ';
26
27 -- Program/Job types
28 -- 'PLSQL_BLOCK'
29 -- 'STORED_PROCEDURE'
30 -- 'EXECUTABLE'
31 -- 'CHAIN' (only valid for a job)
32
33 -- Metadata attributes (for a program argument)
34 -- 'JOB_NAME'
35 -- 'JOB_SUBNAME'
36 -- 'JOB_OWNER'
37 -- 'JOB_START'
38 -- 'SCHEDULED_JOB_START'
39 -- 'EVENT_MESSAGE'
40 -- 'WINDOW_START'
41 -- 'WINDOW_END'
42
43 -- Window Priorities
44 -- 'HIGH'
45 -- 'LOW'
46
47 -- Constants for raise events flags
48 job_started CONSTANT PLS_INTEGER := 1;
49 job_succeeded CONSTANT PLS_INTEGER := 2;
50 job_failed CONSTANT PLS_INTEGER := 4;
51 job_broken CONSTANT PLS_INTEGER := 8;
52 job_completed CONSTANT PLS_INTEGER := 16;
53 job_stopped CONSTANT PLS_INTEGER := 32;
54 job_sch_lim_reached CONSTANT PLS_INTEGER := 64;
55 job_disabled CONSTANT PLS_INTEGER := 128;
56 job_chain_stalled CONSTANT PLS_INTEGER := 256;
57 job_all_events CONSTANT PLS_INTEGER := 511;
58 job_over_max_dur CONSTANT PLS_INTEGER := 512;
59 job_run_completed CONSTANT PLS_INTEGER :=
60 job_succeeded + job_failed + job_stopped;
61
62
63 /* Logical standby pragma default is AUTO_WITH_COMMIT */
64 PRAGMA SUPPLEMENTAL_LOG_DATA(default, AUTO_WITH_COMMIT);
65
66 /*************************************************************
67 * Program Administration Procedures
68 *************************************************************
69 */
70
71 -- Program attributes which can be used with set_attribute/get_attribute are:
72 --
73 -- program_action - VARCHAR2
74 -- This is a string specifying the action. In case of:
75 -- 'PLSQL_BLOCK': PLSQL code
76 -- 'STORED_PROCEDURE: name of the database object
77 -- representing the type (optionally with schema).
78 -- 'EXECUTABLE': Full pathname including the name of the
79 -- executable, or shell script.
80 -- program_type - VARCHAR2
81 -- type of program. This must be one of the supported
82 -- program types. Currently these are
83 -- 'PLSQL_BLOCK', 'STORED_PROCEDURE', 'EXECUTABLE'
84 -- comments - VARCHAR2
85 -- an optional comment. This can describe what the
86 -- program does, or give usage details.
87 -- number_of_arguments- PLS_INTEGER
88 -- the number of arguments of the program that can be set
89 -- by any job using it, these arguments MUST be defined
90 -- before the program can be enabled
91 -- enabled - BOOLEAN
92 -- whether the program is enabled or not. When the program
93 -- is enabled, checks are made to ensure that the program
94 -- is valid.
95
96 -- Create a new program. The program name can be optionally qualified with a
97 -- schema. If enabled is set to TRUE, validity checks will be performed and
98 -- the program will be created in an enabled state if all are passed.
99 PROCEDURE create_program(
100 program_name IN VARCHAR2,
101 program_type IN VARCHAR2,
102 program_action IN VARCHAR2,
103 number_of_arguments IN PLS_INTEGER DEFAULT 0,
104 enabled IN BOOLEAN DEFAULT FALSE,
105 comments IN VARCHAR2 DEFAULT NULL);
106
107 -- Drops an existing program (or a comma separated list of programs).
108 -- When force is set to false the program must not be
109 -- referred to by any job. When force is set to true, any jobs referring to
110 -- this program will be disabled (same behavior as calling the disable routine
111 -- on those jobs with the force option).
112 -- Any argument information that was created for this program will be dropped
113 -- with the program.
114 PROCEDURE drop_program(
115 program_name IN VARCHAR2,
116 force IN BOOLEAN DEFAULT FALSE);
117
118 -- Define an argument of a program. All arguments of a program must be defined.
119 -- If given, the argument name must be unique for this program.
120 -- Any argument already defined at this position will be overwritten.
121 -- The argument type must be a valid Oracle or user-defined type.
122 -- out_argument is reserved for future use. The default and only valid value
123 -- is FALSE.
124 PROCEDURE define_program_argument(
125 program_name IN VARCHAR2,
126 argument_position IN PLS_INTEGER,
127 argument_name IN VARCHAR2 DEFAULT NULL,
128 argument_type IN VARCHAR2,
129 default_value IN VARCHAR2,
130 out_argument IN BOOLEAN DEFAULT FALSE);
131
132 -- Define an argument of a program without a default value.
133 -- Any job using this program must set a value to this argument.
134 -- See other notes for define_program_argument above.
135 PROCEDURE define_program_argument(
136 program_name IN VARCHAR2,
137 argument_position IN PLS_INTEGER,
138 argument_name IN VARCHAR2 DEFAULT NULL,
139 argument_type IN VARCHAR2,
140 out_argument IN BOOLEAN DEFAULT FALSE);
141
142 -- Define an argument with a default value encapsulated in an ANYDATA.
143 -- See other notes for define_program_argument above.
144 PROCEDURE define_anydata_argument(
145 program_name IN VARCHAR2,
146 argument_position IN PLS_INTEGER,
147 argument_name IN VARCHAR2 DEFAULT NULL,
148 argument_type IN VARCHAR2,
149 default_value IN SYS.ANYDATA,
150 out_argument IN BOOLEAN DEFAULT FALSE);
151
152 -- Define a special metadata argument for the program. The program developer
153 -- can retrieve specific scheduler metadata through this argument.
154 -- Jobs cannot set values for this argument.
155 -- valid metadata_attributes are: 'COMPLETION_CODE', 'JOB_SUBNAME','JOB_NAME',
156 -- 'JOB_OWNER', 'JOB_START', 'WINDOW_START', 'WINDOW_END', 'EVENT_MESSAGE'
157 -- See other notes for define_program_argument above.
158 PROCEDURE define_metadata_argument(
159 program_name IN VARCHAR2,
160 metadata_attribute IN VARCHAR2,
161 argument_position IN PLS_INTEGER,
162 argument_name IN VARCHAR2 DEFAULT NULL);
163
164 -- drop a program argument either by name or position
165 PROCEDURE drop_program_argument (
166 program_name IN VARCHAR2,
167 argument_position IN PLS_INTEGER);
168
169 PROCEDURE drop_program_argument (
170 program_name IN VARCHAR2,
171 argument_name IN VARCHAR2);
172
173 /*************************************************************
174 * Job Administration Procedures
175 *************************************************************
176 */
177
178 -- Job attributes which can be used with set_attribute/get_attribute are :
179 --
180 -- program_name - VARCHAR2
181 -- The name of a program object to use with this job.
182 -- If this is set, job_action, job_type and
183 -- number_of_arguments should be NULL
184 -- job_action - VARCHAR2
185 -- This is a string specifying the action. In case of:
186 -- 'PLSQL_BLOCK': PLSQL code
187 -- 'STORED_PROCEDURE': name of the database stored
188 -- procedure (C, Java or PL/SQL), optionally qualified
189 -- with a schema name).
190 -- 'EXECUTABLE': Name of an executable of shell script
191 -- including the full pathname and any command-line
192 -- flags to it.
193 -- If this is set, program_name should be NULL.
194 -- job_type - VARCHAR2
195 -- type of this job. Can be any of:
196 -- 'PLSQL_BLOCK', 'STORED_PROCEDURE', 'EXECUTABLE'
197 -- If this is set,program_name should be NULL
198 -- number_of_arguments- PLS_INTEGER
199 -- the number of arguments if the program is inlined. If
200 -- this is set, program_name should be NULL.
201 -- schedule_name - VARCHAR2
202 -- The name of a schedule or window or window group to use
203 -- as the schedule for this job.
204 -- If this is set, end_date, start_date and repeat_interval
205 -- should all be NULL.
206 -- repeat_interval - VARCHAR2
207 -- either a PL/SQL function returning the next date on
208 -- which to run,or calendar syntax expression.
209 -- If this is set, schedule_name should be NULL.
210 -- start_date - TIMESTAMP WITH TIME ZONE
211 -- the original date on which this job was or will be
212 -- scheduled to start.
213 -- If this is set, schedule_name should be NULL.
214 -- end_date - TIMESTAMP WITH TIME ZONE
215 -- the date after which the job will no longer run (it will
216 -- be dropped if auto_drop is set or disabled with the
217 -- state changed to 'COMPLETED' if it is)
218 -- If this is set, schedule_name should be NULL.
219 -- schedule_limit - INTERVAL DAY TO SECOND
220 -- time in minutes after the scheduled time after which a
221 -- job that has not been run will be rescheduled. This is
222 -- only valid for repeating jobs.
223 -- If this is NULL, a job will never
224 -- be rescheduled unless it has been run (failed or
225 -- successfully)
226 -- job_class - VARCHAR2
227 -- the class this job is associated with.
228 -- job_priority - PLS_INTEGER
229 -- the priority of this job relative to other jobs in the
230 -- same class. The default is 3 and values should
231 -- be 1 and 5 (1 being the highest priority)
232 -- comments - VARCHAR2
233 -- an optional comment.
234 -- max_runs - PLS_INTEGER
235 -- the maximum number of consecutive times this job will be
236 -- allowed to be run (after this number of consecurtive
237 -- times it will be disabled and its state will be changed
238 -- to 'COMPLETED'
239 -- job_weight - PLS_INTEGER
240 -- jobs which include parallel queries should set this to
241 -- the number of parallel slaves they expect to spawn
242 -- logging_level - PLS_INTEGER
243 -- represents how much logging pertaining to
244 -- this job should be done
245 -- max_run_duration - INTERVAL DAY TO SECOND
246 -- the max time for the job to run, if the job runs for
247 -- longer than this interval, a job_over_max_dur event
248 -- will be raised (the job will not be stopped)
249 -- max_failures - PLS_INTEGER
250 -- the number of times a job can fail on consecutive
251 -- scheduled runs before it is automatically disabled. If
252 -- this is set to 0 then the job will keep running no
253 -- matter how often it has failed. If a job is
254 -- automatically disabled after having failed this number
255 -- of times, its state will be changed to BROKEN.
256 -- instance_stickiness- BOOLEAN
257 -- If this option is set to TRUE, then for the first run
258 -- of the job the scheduler will choose the instance with
259 -- the lightest load to run this job on. Subsequent runs
260 -- will use the same instance that the first run used
261 -- (unless this instance is down). If this is FALSE then
262 -- the scheduler will choose the first available instance
263 -- to schedule the job on on all runs.
264 -- stop_on_window_exit - BOOLEAN
265 -- If this job has a window or window group as a schedule
266 -- it will be stopped if the associated window closes, if
267 -- this boolean attribute is set to TRUE.
268 -- enabled - BOOLEAN
269 -- whether the job is enabled or not
270 -- auto_drop - BOOLEAN
271 -- whether the job should be dropped after having
272 -- completed
273 -- restartable - BOOLEAN
274 -- whether the job can be safely restarted (and should be
275 -- restarted in case of failure). By default this is set
276 -- to FALSE.
277 -- destination_name VARCHAR2
278 -- Destination name as created with
279 -- create_database_destination
280 -- or an external agent destination name
281 -- or destination group name
282 -- credential VARCHAR2
283 -- Credential name as created with create_credential
284
285 -- create a job in a single call (without using an existing program or
286 -- schedule).
287 -- Valid values for job_type and job_action are the same as those for
288 -- program_type and program_action. If enabled is set TRUE, it will be
289 -- attempted to enable this job after creating it. If number_of_arguments is
290 -- set non-zero, values must be set for each of the arguments before enabling
291 -- the job.
292 PROCEDURE create_job(
293 job_name IN VARCHAR2,
294 job_type IN VARCHAR2,
295 job_action IN VARCHAR2,
296 number_of_arguments IN PLS_INTEGER DEFAULT 0,
297 start_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
298 repeat_interval IN VARCHAR2 DEFAULT NULL,
299 end_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
300 job_class IN VARCHAR2 DEFAULT '$SCHED_DEFAULT$',
301 enabled IN BOOLEAN DEFAULT FALSE,
302 auto_drop IN BOOLEAN DEFAULT TRUE,
303 comments IN VARCHAR2 DEFAULT NULL,
304 credential_name IN VARCHAR2 DEFAULT NULL,
305 destination_name IN VARCHAR2 DEFAULT NULL);
306
310 -- Values must be set for each argument of the program that does not have a
307 -- create a job using inlined program and inlined event schedule.
308 -- If enabled is set TRUE, it will be attempted to enable this job after
309 -- creating it.
311 -- default_value specified (before enabling the job).
312 -- Note that there are no defaults for event_condition and queue_spec. They
313 -- must be set explicitly to create an event based job.
314 PROCEDURE create_job(
315 job_name IN VARCHAR2,
316 job_type IN VARCHAR2,
317 job_action IN VARCHAR2,
318 number_of_arguments IN PLS_INTEGER DEFAULT 0,
319 start_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
320 event_condition IN VARCHAR2 DEFAULT NULL,
321 queue_spec IN VARCHAR2,
322 end_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
323 job_class IN VARCHAR2 DEFAULT '$SCHED_DEFAULT$',
324 enabled IN BOOLEAN DEFAULT FALSE,
325 auto_drop IN BOOLEAN DEFAULT TRUE,
326 comments IN VARCHAR2 DEFAULT NULL,
327 credential_name IN VARCHAR2 DEFAULT NULL,
328 destination_name IN VARCHAR2 DEFAULT NULL);
329
330 -- create a job using a named schedule object and a named program object.
331 -- If enabled is set TRUE, it will be attempted to enable this job after
332 -- creating it.
333 -- Values must be set for each argument of the program that does not have a
334 -- default_value specified (before enabling the job).
335 PROCEDURE create_job(
336 job_name IN VARCHAR2,
337 program_name IN VARCHAR2,
338 schedule_name IN VARCHAR2,
339 job_class IN VARCHAR2 DEFAULT '$SCHED_DEFAULT$',
340 enabled IN BOOLEAN DEFAULT FALSE,
341 auto_drop IN BOOLEAN DEFAULT TRUE,
342 comments IN VARCHAR2 DEFAULT NULL,
343 job_style IN VARCHAR2 DEFAULT 'REGULAR',
344 credential_name IN VARCHAR2 DEFAULT NULL,
345 destination_name IN VARCHAR2 DEFAULT NULL);
346
347 -- create a job using a named program object and an inlined schedule
348 -- If enabled is set TRUE, it will be attempted to enable this job after
349 -- creating it.
350 -- Values must be set for each argument of the program that does not have a
351 -- default_value specified (before enabling the job).
352 PROCEDURE create_job(
353 job_name IN VARCHAR2,
354 program_name IN VARCHAR2,
355 start_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
356 repeat_interval IN VARCHAR2 DEFAULT NULL,
357 end_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
358 job_class IN VARCHAR2 DEFAULT '$SCHED_DEFAULT$',
359 enabled IN BOOLEAN DEFAULT FALSE,
360 auto_drop IN BOOLEAN DEFAULT TRUE,
361 comments IN VARCHAR2 DEFAULT NULL,
362 job_style IN VARCHAR2 DEFAULT 'REGULAR',
363 credential_name IN VARCHAR2 DEFAULT NULL,
364 destination_name IN VARCHAR2 DEFAULT NULL);
365
366 -- create a job using named program and inlined event schedule.
367 -- If enabled is set TRUE, it will be attempted to enable this job after
368 -- creating it.
369 -- Values must be set for each argument of the program that does not have a
370 -- default_value specified (before enabling the job).
371 -- Note that there are no defaults for event_condition and queue_spec. They
372 -- must be set explicitly to create an event based job.
373 PROCEDURE create_job(
374 job_name IN VARCHAR2,
375 program_name IN VARCHAR2,
376 start_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
377 event_condition IN VARCHAR2 DEFAULT NULL,
378 queue_spec IN VARCHAR2,
379 end_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
380 job_class IN VARCHAR2 DEFAULT '$SCHED_DEFAULT$',
381 enabled IN BOOLEAN DEFAULT FALSE,
382 auto_drop IN BOOLEAN DEFAULT TRUE,
383 comments IN VARCHAR2 DEFAULT NULL,
384 job_style IN VARCHAR2 DEFAULT 'REGULAR',
385 credential_name IN VARCHAR2 DEFAULT NULL,
386 destination_name IN VARCHAR2 DEFAULT NULL);
387
388 -- create a job using a named schedule object and an inlined program
389 -- Valid values for job_type and job_action are the same as those for
390 -- program_type and program_action. If enabled is set TRUE, it will be
391 -- attempted to enable this job after creating it. If number_of_arguments is
392 -- set non-zero, values must be set for each of the arguments before enabling
393 -- the job.
394 PROCEDURE create_job(
395 job_name IN VARCHAR2,
396 schedule_name IN VARCHAR2,
397 job_type IN VARCHAR2,
398 job_action IN VARCHAR2,
399 number_of_arguments IN PLS_INTEGER DEFAULT 0,
400 job_class IN VARCHAR2 DEFAULT '$SCHED_DEFAULT$',
401 enabled IN BOOLEAN DEFAULT FALSE,
402 auto_drop IN BOOLEAN DEFAULT TRUE,
406
403 comments IN VARCHAR2 DEFAULT NULL,
404 credential_name IN VARCHAR2 DEFAULT NULL,
405 destination_name IN VARCHAR2 DEFAULT NULL);
407 -- Run a job immediately. If use_current_session is TRUE the job is run in the
408 -- user's current session. If use_current_session is FALSE the job is run in the
409 -- background by a dedicated job slave.
410 PROCEDURE run_job(
411 job_name IN VARCHAR2,
412 use_current_session IN BOOLEAN DEFAULT TRUE,
413 event_message IN SYS.ANYDATA DEFAULT NULL);
414
415 PRAGMA SUPPLEMENTAL_LOG_DATA(run_job, NONE);
416 -- Stop a job or several jobs that are currently running. Job name can also be
417 -- the name of a job class or a comma-separated list of jobs.
418 -- If the force option is not specified this will interrupt the job
419 -- by sending an equivalent of a Ctrl-C to the job. If this fails, an error
420 -- will be returned.
421 -- If the force option is specified the job slave will be terminated. Use of
422 -- the force option requires the MANAGE SCHEDULER system privilege
423 PROCEDURE stop_job(
424 job_name IN VARCHAR2,
425 force IN BOOLEAN DEFAULT FALSE,
426 commit_semantics IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR');
427
428 -- Copy a job. The new_job will contain all the attributes of the old_job,
429 -- except that it will be created disabled. The state of the old_job will not
430 -- be altered.
431 PRAGMA SUPPLEMENTAL_LOG_DATA(stop_job, NONE);
432 PROCEDURE copy_job(
433 old_job IN VARCHAR2,
434 new_job IN VARCHAR2);
435
436 -- Drop a job or several jobs. Job name can also be
437 -- the name of a job class or a comma-separated list of jobs.
438 -- If force is true, all running instances of the job will be stopped by
439 -- calling stop_job with force set to false. If defer is true, all running
440 -- instances of the job will be allowed to complete before the job is dropped.
441 -- If force and defer are false, dropping a job with running instances will
442 -- fail. If force and defer are both true, an error will be raised.
443 PROCEDURE drop_job(
444 job_name IN VARCHAR2,
445 force IN BOOLEAN DEFAULT FALSE,
446 defer IN BOOLEAN DEFAULT FALSE,
447 commit_semantics IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR');
448
449 -- Set a value to be passed to one of the arguments of the program (either
450 -- named, or inlined). If program is inlined, only setting by position is
451 -- supported. The passed value will override any default value set during
452 -- definition of the program argument and overwrite any value previously set
453 -- for this argument position for this job (the previous value will be lost).
454 PROCEDURE set_job_argument_value(
455 job_name IN VARCHAR2,
456 argument_position IN PLS_INTEGER,
457 argument_value IN VARCHAR2);
458
459 -- This refers to a program argument by its name. It can only be used if the
460 -- job is using a named program (i.e. program_name points to an existing
461 -- program). The argument_name used must be the same name defined by the
462 -- program.
463 PROCEDURE set_job_argument_value(
464 job_name IN VARCHAR2,
465 argument_name IN VARCHAR2,
466 argument_value IN VARCHAR2);
467
468 -- Same as above but accepts the default value encapsulated in an AnyData
469 PROCEDURE set_job_anydata_value(
470 job_name IN VARCHAR2,
471 argument_position IN PLS_INTEGER,
472 argument_value IN SYS.ANYDATA);
473
474 -- This refers to a program argument by its name. It can only be used if the
475 -- job is using a named program (i.e. program_name points to an existing
476 -- program). The argument_name used must be the same name defined by the
477 -- program.
478 PROCEDURE set_job_anydata_value(
479 job_name IN VARCHAR2,
480 argument_name IN VARCHAR2,
481 argument_value IN SYS.ANYDATA);
482
483 -- Clear a previously set job argument value. All job specific value
484 -- information for this argument is erased. The job will revert back to the
485 -- default value for this argument as defined by the program (if any).
486 PROCEDURE reset_job_argument_value(
487 job_name IN VARCHAR2,
488 argument_position IN PLS_INTEGER);
489
490 -- This refers to a program argument by its name. It can only be used if the
491 -- job is using a named program (i.e. program_name points to an existing
492 -- program). The argument_name used must be the same name defined by the
493 -- program.
494 PROCEDURE reset_job_argument_value(
495 job_name IN VARCHAR2,
496 argument_name IN VARCHAR2);
497
498 /*************************************************************
499 * Job Destination Administration Procedures
500 *************************************************************
501 */
502 -- * Create group: Create a destination group to be set as a destination of
503 -- a job.
504 -- * The namespace for groups is different from that of database objects
505 -- but we will not allow creating a group with the name of an existing
506 -- object.
507 -- * All members of the group must be of the same type. In the case of a
508 -- destination group all members must either represent destinations for
509 -- external jobs or destinations for database jobs.
510 -- * The format of destination members is
514 -- * An error will be returned if one of the members does not exist. Or in
511 -- [[schema.]credential@][schema.]destination. The credential part is
512 -- optional. If it isn't present the job instance representing this
513 -- destination will use the default credential specified with the job.
515 -- the case of destinations groups if either part of the destination
516 -- (credential or destination) does not exist.
517 -- * Groups will reside in a particular schema but there are no specific
518 -- privileges you can grant on groups. Only the owner and SYS can modify
519 -- the group (create, add, remove, drop) and everybody can see which groups
520 -- have been created. However, you will only be able to see those member
521 -- of a group that you have access to (i.e. privileges on).
522 -- Even though there are no privileges on destinations, you still will not
523 -- be able to see those destination group members that contain a credential
524 -- you have no access to.
525 -- * When groups are used in API calls, the action will only be performed on
526 -- those members of the group that you have privileges on.
527 -- * LOCAL and ALL_INSTANCES are special keywords only to be used as
528 -- destination group members. LOCAL can be used for external as well as
529 -- database jobs. In the case of a database job it represents the source
530 -- database on which the job is created. In the case of an external job it
531 -- represents the machine on which the source database runs. When the
532 -- source database is a RAC database the destination LOCAL_INSTANCES
533 -- indicates that a database job must be run on every instance of the
534 -- database and an external job must run on every machine that runs an
535 -- instance of the database. LOCAL and ALL_INSTANCES can not be used as
536 -- group names.
537 -- Arguments:
538 -- * group_name -- Name of group
539 -- * group_type -- Group type, currently only 'DESTINATION' is supported
540 -- * member -- Optional list of group members. Default is NULL.
541 -- * comments -- Comments
542 PROCEDURE create_group(
543
544 group_name IN VARCHAR2,
545 group_type IN VARCHAR2,
546 member IN VARCHAR2 DEFAULT NULL,
547 comments IN VARCHAR2 DEFAULT NULL);
548
549 -- Drop Group:
550 -- * When a group of type destination is dropped the jobs that have their
551 -- destination attribute set to this group will be disabled. All its job
552 -- instances will be removed from the *_scheduler_job_destinations view.
553 -- Arguments:
554 -- * group_name Name of group
555 -- * force Unused for now
556 PROCEDURE drop_group(
557 group_name IN VARCHAR2,
558 force IN BOOLEAN DEFAULT FALSE);
559
560 -- Add group member:
561 -- * Member is a comma separated list of new members to add.
562 -- * The members of a group must be of the same type. In the case of
563 -- destination groups the members must either be all external destinations
564 -- or all database destinations.
565 -- * Groups can not be specified in the member list not even to get them
566 -- fully expanded.
567 -- * The member will be canonicalized.
568 -- * This routine will skip a member if it is already a member of the group.
569 -- It will not error out.
570 -- * LOCAL and ALL_INSTANCES are reserved keywords only to be used as
571 -- destination group members. See create_group() for more information.
572 -- Arguments:
573 -- * group_name: Name of group
574 -- * member: Name of one or more members to add to the group
575 PROCEDURE add_group_member (
576 group_name IN VARCHAR2,
577 member IN VARCHAR2);
578
579
580 -- Remove group member:
581 -- * Member is a comma separated list of members to remove from the group.
582 -- * An error will be returned if the specified member is not part of the group.
583 -- * The member will only be removed from this group.
584 -- * If the member is a destination, any job instances that represent this
585 -- destination will be removed from the all_scheduler_job_destinations view.
586 -- Arguments:
587 -- * group_name Name of group
588 -- * member Name of the member to remove from the group
589 PROCEDURE remove_group_member(
590 group_name IN VARCHAR2,
591 member IN VARCHAR2);
592
593
594 -- Create database destination
595 -- * It's only possible to create a remote database job if first a database
596 -- destination that represents the remote database has been created.
597 -- * The agent value must be an existing external destination name.
598 -- Arguments:
599 -- * destination_name Name of destination representing the
600 -- database that you want to connect to.
601 -- * agent Name of the external destination that represents
602 -- the agent that is used to connect to the remote database.
603 -- * tns_name Name of the local tnsnames.ora entry that
604 -- points to the remote database to connect to.
605 -- * comments Comments
606 PROCEDURE create_database_destination(
607 destination_name IN VARCHAR2,
608 agent IN VARCHAR2,
609 tns_name IN VARCHAR2,
610 comments IN VARCHAR2 DEFAULT NULL);
611
612 -- Drop database destination:
613 -- * When a database destination is dropped all members of destination groups
614 -- that point to this destination will be dropped as well.
615 -- * When a database destination is dropped all the job instances in the
619 -- * destination_name: Name of destination representing the database that
616 -- scheduler_job_destinations views that point to this destination will
617 -- be dropped as well.
618 -- Arguments:
620 -- you want to connect to.
621 PROCEDURE drop_database_destination(
622 destination_name IN VARCHAR2);
623
624 -- Drop external destination:
625 -- * Emergency use only, use agent control utility on agent residing
626 -- host to drop the agent destination.
627 -- * When an agent destination is dropped all the job instances in the
628 -- scheduler_job_destinations views that point to this destination will
629 -- be dropped as well.
630 -- * all database destinations refering to the agent destination will
631 -- be dropped as well.
632 -- * Manage scheduler privilege is required for this procedure
633 -- Arguments:
634 -- * destination_name: Name of destination representing the external job
635 -- agent
636 PROCEDURE drop_agent_destination(
637 destination_name IN VARCHAR2);
638 /*************************************************************
639 * Job Class Administration Procedures
640 *************************************************************
641 */
642
643 -- Job Class attributes which can be used with set_attribute/get_attribute are:
644 --
645 -- resource_consumer_group - VARCHAR2
646 -- resource consumer group a class is associated with
647 -- service - VARCHAR2
648 -- The service the job class belongs to. Default is NULL,
649 -- which implies the default service. This should be the
650 -- name of the service database object and not the
651 -- service name as defined in tnsnames.ora .
652 -- log_purge_policy - VARCHAR2
653 -- The policy for purging of scheduler log table entries
654 -- pertaining to jobs belonging to this class. By default
655 -- log table entries are not purged.
656 -- comments - VARCHAR2
657 -- an optional comment about the class.
658
659 -- Create a job class.
660 PROCEDURE create_job_class(
661 job_class_name IN VARCHAR2,
662 resource_consumer_group IN VARCHAR2 DEFAULT NULL,
663 service IN VARCHAR2 DEFAULT NULL,
664 logging_level IN PLS_INTEGER DEFAULT DBMS_SCHEDULER.LOGGING_RUNS,
665 log_history IN PLS_INTEGER DEFAULT NULL,
666 comments IN VARCHAR2 DEFAULT NULL);
667
668 -- Drop a job class (or a comma-separated list of classes). This will return
669 -- an error if force is set to FALSE and
670 -- there are still jobs (in any state) that are part of this class.
671 -- If force is set to TRUE, all jobs that are part of this class will be
672 -- disabled and their class will be set to the default class.
673 PROCEDURE drop_job_class(
674 job_class_name IN VARCHAR2,
675 force IN BOOLEAN DEFAULT FALSE);
676
677 /*************************************************************
678 * System Window Administration Procedures
679 *************************************************************
680 */
681
682 -- System window attributes that can be used with set_attribute/get_attribute
683 -- are:
684 --
685 -- resource_plan - VARCHAR2
686 -- the resource plan to be associated with a window.
687 -- When the window opens, the system will switch to
688 -- using this resource plan. When the window closes, the
689 -- original resource plan will be restored. If a
690 -- resource plan has been made active with the force
691 -- option, no resource plan switch will occur.
692 -- window_priority - VARCHAR2
693 -- The priority of the window. Must be one of
694 -- 'LOW' (default) , 'HIGH'.
695 -- duration - INTERVAL DAY TO SECOND
696 -- The duration of the window in minutes.
697 -- schedule_name - VARCHAR2
698 -- The name of a schedule to use with this window. If
699 -- this is set, start_date, end_date and repeat_interval
700 -- must all be NULL.
701 -- repeat_interval - VARCHAR2
702 -- A string using the calendar syntax. PL/SQL date
703 -- functions are not allowed
704 -- If this is set, schedule_name must be NULL
705 -- start_date - TIMESTAMP WITH TIME ZONE
706 -- next date on which this window is scheduled to open.
707 -- If this is set, schedule_name must be NULL.
708 -- end_date - TIMESTAMP WITH TIME ZONE
709 -- the date after which the window will no longer open.
710 -- If this is set, schedule_name must be NULL.
711 -- enabled - BOOLEAN
712 -- whether the window is enabled or not
713 -- comments - VARCHAR2
714 -- an optional comment about the window.
715 -- The below attribute is only visible through the views and not to
716 -- get_attribute or set_attribute
717 -- schedule_type - VARCHAR2
718 -- will be one of: 'CALENDAR_STRING', 'NAMED'
719
720 -- Create a system window using a named schedule object. The specified
721 -- schedule must exist.
722 PROCEDURE create_window(
726 duration IN INTERVAL DAY TO SECOND,
723 window_name IN VARCHAR2,
724 resource_plan IN VARCHAR2,
725 schedule_name IN VARCHAR2,
727 window_priority IN VARCHAR2 DEFAULT 'LOW',
728 comments IN VARCHAR2 DEFAULT NULL);
729
730 -- Create a system window using an inlined schedule.
731 -- repeat_interval must use the calendar syntax. PL/SQL date functions are not
732 -- allowed.
733 PROCEDURE create_window(
734 window_name IN VARCHAR2,
735 resource_plan IN VARCHAR2,
736 start_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
737 repeat_interval IN VARCHAR2,
738 end_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
739 duration IN INTERVAL DAY TO SECOND,
740 window_priority IN VARCHAR2 DEFAULT 'LOW',
741 comments IN VARCHAR2 DEFAULT NULL);
742
743 -- Drops a scheduler system window. Window name can also be a window group (in
744 -- which case all the windows in that window group are dropped) or a
745 -- comma-separated list of windows. Dropping a window disables all jobs which
746 -- use the window as a schedule (leaving currently running jobs running). If
747 -- the window is open, dropping it will attempt to close it first
748 -- The window is also dropped from any referring window groups.
749 PROCEDURE drop_window(
750 window_name IN VARCHAR2,
751 force IN BOOLEAN DEFAULT FALSE);
752
753 -- Immediately opens a scheduler window independent of its specified schedule.
754 -- The window will be opened for the specified duration. If the duration is
755 -- null, the window will be opened for the duration as specified when the
756 -- window was created.
757 -- The next open time of the window is not updated, and will be as determined
758 -- by the regular scheduled opening.
759 -- Opening of the window will fail if the DBA has blocked the scheduler from
760 -- switching to a different resource plan.
761 -- If force option not specified and a current window is active the operation
762 -- will fail, unless the window is the current open window.
763 -- If the current open window equals window_name, the closing time is set to
764 -- the system date plus the given duration, i.e. the closing time of the
765 -- current window is moved up or down, but no jobs are stopped.
766 PROCEDURE open_window(
767 window_name IN VARCHAR2,
768 duration IN INTERVAL DAY TO SECOND,
769 force IN BOOLEAN DEFAULT FALSE);
770 PRAGMA SUPPLEMENTAL_LOG_DATA(open_window, NONE);
771
772 -- Prematurely closes the currently active window. This premature closing
773 -- of a window will have the same effect as a regular close e.g. any jobs that
774 -- have a window or a window group as their schedule and were started at the
775 -- beginning of this window because of that schedule and have indicated that
776 --they must be stopped on closing of the window, will be stopped.
777 PROCEDURE close_window(
778 window_name IN VARCHAR2);
779 PRAGMA SUPPLEMENTAL_LOG_DATA(close_window, NONE);
780
781 /*************************************************************
782 * System Window Administration Procedures
783 *************************************************************
784 */
785
786 -- enable and disable can be used on window groups. They disable/enable the
787 -- window group as a whole, not the individual windows in the group.
788 --
789 -- member_list refers to a comma-separated list of windows
790 -- Window groups cannot contain other window groups.
791
792 -- Creates a window group optionally containing windows specified in
793 -- member_list.
794 PROCEDURE create_window_group(
795 group_name IN VARCHAR2,
796 window_list IN VARCHAR2 DEFAULT NULL,
797 comments IN VARCHAR2 DEFAULT NULL);
798
799 -- Adds a window (or comma-separated list of windows) to a window group.
800 -- If a window is already in the window group, it will not be added again.
801 PROCEDURE add_window_group_member(
802 group_name IN VARCHAR2,
803 window_list IN VARCHAR2);
804
805 -- Removes a window (or comma-separated list of windows) from a window group.
806 PROCEDURE remove_window_group_member(
807 group_name IN VARCHAR2,
808 window_list IN VARCHAR2);
809
810 -- Drops a window group (does not drop windows that are members of this group)
811 -- Returns an error when force is set to false and there are jobs whose
812 -- schedule is the name of the window group. If force is set to true, any jobs
813 -- whose schedule is the name of the window group will be disabled.
814 PROCEDURE drop_window_group(
815 group_name IN VARCHAR2,
816 force IN BOOLEAN DEFAULT FALSE);
817
818 -- Get scheduler default time and timezone.
819 -- This would be used for jobs without a start time specified.
820 -- Follow default timezone can be set to simulate an object with
821 -- this attribute set (i.e system windows etc).
822 FUNCTION stime (
823 follow_default_timezone BOOLEAN DEFAULT FALSE)
824 RETURN TIMESTAMP WITH TIME ZONE;
825 PRAGMA SUPPLEMENTAL_LOG_DATA(stime, NONE);
826
827 --Get information about the running agent
828 --Possible attributes are 'ALL', 'VERSION',
829 --'RUNNING_JOBS', 'NUMBER_OF_RUNNING_JOBS',
830 --and 'UPTIME'
831 FUNCTION get_agent_info(
832 agent_name IN VARCHAR2,
836 FUNCTION get_agent_version(
833 attribute IN VARCHAR2) RETURN VARCHAR2;
834
835 -- Get the version of a Scheduler Execution Agent
837 agent_host IN VARCHAR2) RETURN VARCHAR2;
838 PRAGMA SUPPLEMENTAL_LOG_DATA(get_agent_version, NONE);
839
840 -- Internal.
841 -- Used for initializing the scheduler default timezone.
842 FUNCTION get_sys_time_zone_name RETURN VARCHAR2;
843 PRAGMA SUPPLEMENTAL_LOG_DATA(get_sys_time_zone_name, NONE);
844
845 /*************************************************************
846 * Schedule Administration Procedures
847 *************************************************************
848 */
849
850 -- Schedule attributes which can be used with set_attribute/get_attribute are :
851 --
852 -- repeat_interval - VARCHAR2
853 -- an expression using the calendar syntax
854 -- comments - VARCHAR2
855 -- an optional comment.
856 -- end_date - TIMESTAMP WITH TIME ZONE
857 -- cutoff date after which the schedule will not specify
858 -- any dates
859 -- start_date - TIMESTAMP WITH TIME ZONE
860 -- start or reference date used by the calendar syntax
861 --
862 -- Schedules cannot be enabled and disabled.
863
864 -- Create a named schedule. This must be a valid schedule.
865 PROCEDURE create_schedule(
866 schedule_name IN VARCHAR2,
867 start_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
868 repeat_interval IN VARCHAR2,
869 end_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
870 comments IN VARCHAR2 DEFAULT NULL);
871
872 --- Import helper function.
873 PROCEDURE disable1_calendar_check;
874
875 -- Create a named event schedule. This must be a valid schedule.
876 PROCEDURE create_event_schedule(
877 schedule_name IN VARCHAR2,
878 start_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
879 event_condition IN VARCHAR2 DEFAULT NULL,
880 queue_spec IN VARCHAR2,
881 end_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
882 comments IN VARCHAR2 DEFAULT NULL);
883
884 -- Drop a schedule (or comma-separated list of schedules). When force is set
885 -- to false, and there are jobs or windows
886 -- that point to this schedule an error will be raised.
887 -- If force is set to true, any jobs or windows pointing to this schedule will
888 -- be disabled before the schedule is dropped.
889 -- Schedules may refer to day calendar schedules in which case no checking
890 -- occurs. Thus for day calendar drops force is always assumed true,
891 -- even if specified as false.
892 PROCEDURE drop_schedule(
893 schedule_name IN VARCHAR2,
894 force IN BOOLEAN DEFAULT FALSE);
895
896 /*************************************************************
897 * Chain Administration Procedures
898 *************************************************************
899 */
900
901 -- Chain attributes which can be used with set_attribute/get_attribute
902 -- are :
903 --
904 -- comments - VARCHAR2
905 -- an optional comment.
906 -- evaluation_interval - INTERVAL DAY TO SECOND
907 -- interval between periodic re-evaluations of a
908 -- running chain
909 --
910
911 -- Creates a chain.
912 -- Chains are created disabled and must be enabled before use.
913 PROCEDURE create_chain(
914 chain_name IN VARCHAR2,
915 rule_set_name IN VARCHAR2 DEFAULT NULL,
916 evaluation_interval IN INTERVAL DAY TO SECOND DEFAULT NULL,
917 comments IN VARCHAR2 DEFAULT NULL);
918
919 -- adds or replaces a chain rule
920 PROCEDURE define_chain_rule(
921 chain_name IN VARCHAR2,
922 condition IN VARCHAR2,
923 action IN VARCHAR2,
924 rule_name IN VARCHAR2 DEFAULT NULL,
925 comments IN VARCHAR2 DEFAULT NULL);
926
927 -- adds or replaces a chain step and associates it with a program
928 -- or chain
929 PROCEDURE define_chain_step(
930 chain_name IN VARCHAR2,
931 step_name IN VARCHAR2,
932 program_name IN VARCHAR2);
933
934 -- adds or replaces a chain step and associates it with an event schedule
935 PROCEDURE define_chain_event_step(
936 chain_name IN VARCHAR2,
937 step_name IN VARCHAR2,
938 event_schedule_name IN VARCHAR2,
939 timeout IN INTERVAL DAY TO SECOND DEFAULT NULL);
940
941 -- adds or replaces a chain step and associates it with an inline event
942 PROCEDURE define_chain_event_step(
943 chain_name IN VARCHAR2,
944 step_name IN VARCHAR2,
945 event_condition IN VARCHAR2,
946 queue_spec IN VARCHAR2,
947 timeout IN INTERVAL DAY TO SECOND DEFAULT NULL);
948
949 -- drops a chain rule
950 PROCEDURE drop_chain_rule(
951 chain_name IN VARCHAR2,
952 rule_name IN VARCHAR2,
953 force IN BOOLEAN DEFAULT FALSE);
954
955 -- drops a chain step
956 PROCEDURE drop_chain_step(
957 chain_name IN VARCHAR2,
958 step_name IN VARCHAR2,
962 PROCEDURE alter_chain(
959 force IN BOOLEAN DEFAULT FALSE);
960
961 -- alters steps of a chain
963 chain_name IN VARCHAR2,
964 step_name IN VARCHAR2,
965 attribute IN VARCHAR2,
966 value IN BOOLEAN);
967
968 -- alters steps of a chain
969 PROCEDURE alter_chain(
970 chain_name IN VARCHAR2,
971 step_name IN VARCHAR2,
972 attribute IN VARCHAR2,
973 char_value IN VARCHAR2);
974
975 -- drops a chain
976 PROCEDURE drop_chain(
977 chain_name IN VARCHAR2,
978 force IN BOOLEAN DEFAULT FALSE);
979
980 -- analyzes a chain or a list of steps and rules and outputs a list of
981 -- chain dependencies
982 PROCEDURE analyze_chain(
983 chain_name IN VARCHAR2,
984 rules IN sys.scheduler$_rule_list,
985 steps IN sys.scheduler$_step_type_list,
986 step_pairs OUT sys.scheduler$_chain_link_list);
987
988 -- alters steps of a running chain
989 PROCEDURE alter_running_chain(
990 job_name IN VARCHAR2,
991 step_name IN VARCHAR2,
992 attribute IN VARCHAR2,
993 value IN BOOLEAN);
994
995 -- alters steps of a running chain
996 PROCEDURE alter_running_chain(
997 job_name IN VARCHAR2,
998 step_name IN VARCHAR2,
999 attribute IN VARCHAR2,
1000 value IN VARCHAR2);
1001
1002 -- forces immediate evaluation of a running chain
1003 PROCEDURE evaluate_running_chain(
1004 job_name IN VARCHAR2);
1005
1006 -- immediately runs a job pointing to a chain starting with a list of
1007 -- specified steps. The job will be started in the background.
1008 -- If start_steps is NULL, the chain is run from the beginning.
1009 PROCEDURE run_chain(
1010 chain_name IN VARCHAR2,
1011 start_steps IN VARCHAR2,
1012 job_name IN VARCHAR2 DEFAULT NULL);
1013 -- immediately runs a job pointing to a chain starting with the given
1014 -- list of step states. The job will be started in the background.
1015 -- If step_state_list is NULL, the chain is run from the beginning.
1016 PROCEDURE run_chain(
1017 chain_name IN VARCHAR2,
1018 step_state_list IN SYS.SCHEDULER$_STEP_TYPE_LIST,
1019 job_name IN VARCHAR2 DEFAULT NULL);
1020
1021 /*************************************************************
1022 * Credential Administration Procedures
1023 *************************************************************
1024 */
1025
1026 -- credential attributes which can be used with set_attribute/get_attribute:
1027 --
1028 -- username - VARCHAR2
1029 -- user to execute the job as.
1030 -- password - VARCHAR2
1031 -- password to use to authenticate the user
1032 -- comments - VARCHAR2
1033 -- an optional comment. This can describe what the
1034 -- credential is intended to be used for.
1035 -- database_role - VARCHAR2
1036 -- Database role to use when logging in (either SYSDBA or
1037 -- SYSOPER or NULL)
1038 -- windows_domain - VARCHAR2
1039 -- Windows domain to use when logging in
1040
1041 -- Create a new credential. The credential name can be optionally qualified
1042 -- with a schema.
1043 PROCEDURE create_credential(
1044 credential_name IN VARCHAR2,
1045 username IN VARCHAR2,
1046 password IN VARCHAR2,
1047 database_role IN VARCHAR2 DEFAULT NULL,
1048 windows_domain IN VARCHAR2 DEFAULT NULL,
1049 comments IN VARCHAR2 DEFAULT NULL);
1050 PRAGMA SUPPLEMENTAL_LOG_DATA(create_credential, NONE);
1051
1052 -- Drops an existing credential (or a comma separated list of credentials).
1053 -- When force is set to false the credential must not be
1054 -- referred to by any job. When force is set to true, any jobs referring to
1055 -- this credential will be disabled (same behavior as calling the disable
1056 -- routine on those jobs with the force option).
1057 PROCEDURE drop_credential(
1058 credential_name IN VARCHAR2,
1059 force IN BOOLEAN DEFAULT FALSE);
1060
1061 -- Saves a file to one or more specified destination hosts. Uses a
1062 -- specified credential to login to the given hosts. All specified remote hosts
1063 -- must have an execution agent installed and running.
1064 -- The caller must have the CREATE EXTERNAL JOB system privilege and
1065 -- have EXECUTE privileges on the credential.
1066 procedure put_file (
1067 destination_file IN VARCHAR2,
1068 destination_host IN VARCHAR2,
1069 credential_name IN VARCHAR2,
1070 file_contents IN CLOB CHARACTER SET ANY_CS,
1071 destination_permissions IN VARCHAR2 DEFAULT NULL);
1072 procedure put_file (
1073 destination_file IN VARCHAR2,
1074 destination_host IN VARCHAR2,
1075 credential_name IN VARCHAR2,
1076 file_contents IN BLOB,
1077 destination_permissions IN VARCHAR2 DEFAULT NULL);
1078 procedure put_file (
1079 destination_file IN VARCHAR2,
1080 destination_host IN VARCHAR2,
1081 credential_name IN VARCHAR2,
1082 source_file_name IN VARCHAR2,
1086 -- Retrieves a file from a specified destination host. Uses a
1083 source_directory_object IN VARCHAR2,
1084 destination_permissions IN VARCHAR2 DEFAULT NULL);
1085
1087 -- specified credential to login to the given host. Any specified remote host
1088 -- must have an execution agent installed and running.
1089 -- The caller must have the CREATE EXTERNAL JOB system privilege and
1090 -- have EXECUTE privileges on the credential.
1091 procedure get_file (
1092 source_file IN VARCHAR2,
1093 source_host IN VARCHAR2,
1094 credential_name IN VARCHAR2,
1095 file_contents IN OUT NOCOPY CLOB CHARACTER SET ANY_CS);
1096 procedure get_file (
1097 source_file IN VARCHAR2,
1098 source_host IN VARCHAR2,
1099 credential_name IN VARCHAR2,
1100 file_contents IN OUT NOCOPY BLOB);
1101 procedure get_file (
1102 source_file IN VARCHAR2,
1103 source_host IN VARCHAR2,
1104 credential_name IN VARCHAR2,
1105 destination_file_name IN VARCHAR2,
1106 destination_directory_object IN VARCHAR2,
1107 destination_permissions IN VARCHAR2 DEFAULT NULL);
1108
1109 procedure create_file_watcher (
1110 file_watcher_name IN VARCHAR2,
1111 directory_path IN VARCHAR2,
1112 file_name IN VARCHAR2,
1113 credential_name IN VARCHAR2,
1114 destination IN VARCHAR2 DEFAULT NULL,
1115 min_file_size IN PLS_INTEGER DEFAULT 0,
1116 steady_state_duration IN INTERVAL DAY TO SECOND DEFAULT NULL,
1117 comments IN VARCHAR2 DEFAULT NULL,
1118 enabled IN BOOLEAN DEFAULT TRUE);
1119
1120 procedure drop_file_watcher (
1121 file_watcher_name IN VARCHAR2,
1122 force IN BOOLEAN DEFAULT FALSE);
1123
1124 -- PROCEDURE add_job_email_notification:
1125 -- ARGUMENTS:
1126 -- job_name - Name of the job to send e-mail notifications for. Cannot be NULL
1127 -- recipients - Comma-separated list of e-mail addresses to send
1128 -- notifications to. E-mail notifications for all listed events will
1129 -- be sent to all e-mail addresses provided. This cannot be NULL.
1130 -- sender - E-mail address to use as the sender for e-mail
1131 -- notifications. If this is NULL and the scheduler attribute
1132 -- default_email_sender contains a valid e-mail address, that value will
1133 -- be used instead.
1134 -- subject - This will be used as the subject of notification e-mails. This
1135 -- can contain the following variables for which values will be
1136 -- substituted before the e-mail is sent:
1137 -- %job_owner%
1138 -- %job_name%
1139 -- %job_subname%
1140 -- %event_type%
1141 -- %event_timestamp%
1142 -- %log_id%
1143 -- %error_code%
1144 -- %error_message%
1145 -- %run_count%
1146 -- %failure_count%
1147 -- %retry_count%
1148 -- body - This will be used as the body of notification e-mails. This
1149 -- can contain any of the variables that are valid in the subject.
1150 -- events - Comma-separated list of events to e-mail notifications for.
1151 -- E-mail notifications for all specified events will be sent to all
1152 -- e-mail addresses provided. This cannot be NULL. The list of events that
1153 -- can be set is documented under the raise_events attribute of jobs.
1154 -- filter_condition - This will be used to additionally filter e-mail
1155 -- notifications that are sent. If this is NULL (the default), all listed
1156 -- events will be e-mailed to all specified recipient addresses. The
1157 -- format is a SQL where-clause with :event bound to
1158 -- a scheduler$_event_info type object.
1159 -- For example to send e-mail only when the error number is 600 or 700
1160 -- you can use the following filter_condition:
1161 -- :event.error_code=600 or :event.error_code=700
1162 --
1163 -- This will add job e-mail notifications so that e-mails will be sent to the
1164 -- specified recipient addresses whenever any of the listed events are
1165 -- generated by the job. This will automatically modify the job to raise
1166 -- these events by modifying the raise_events flag. If a filter_condition is
1167 -- given, only events which match the filter_condition will generate an
1168 -- e-mail.
1169 -- This will fail if the scheduler attribute email_server is not set or if the
1170 -- job specified does not exist.
1171 PROCEDURE add_job_email_notification
1172 (
1173 job_name IN VARCHAR2,
1174 recipients IN VARCHAR2,
1175 sender IN VARCHAR2 DEFAULT NULL,
1176 subject IN VARCHAR2
1177 DEFAULT dbms_scheduler.default_notification_subject,
1178 body IN VARCHAR2
1179 DEFAULT dbms_scheduler.default_notification_body,
1180 events IN VARCHAR2 DEFAULT
1181 'JOB_FAILED,JOB_BROKEN,JOB_SCH_LIM_REACHED,JOB_CHAIN_STALLED,JOB_OVER_MAX_DUR',
1182 filter_condition IN VARCHAR2 DEFAULT NULL);
1183
1184 -- PROCEDURE remove_job_email_notification:
1185 -- ARGUMENTS:
1186 -- job_name - Name of the job to remove e-mail notifications for. This cannot
1187 -- be NULL.
1188 -- recipients - Comma-separated list of e-mail addresses to remove
1189 -- notifications for. If this is NULL, all notifications for the given
1190 -- job and listed events will be removed.
1191 -- events - Comma-separated list of events to remove e-mail notifications for.
1192 -- If this is NULL, all notifications for the given job and the listed
1196 -- It will not modify the job to stop raising the events, but no events will
1193 -- e-mail addresses will be removed.
1194 --
1195 -- This is used to remove one or more e-mail notifications for a given job.
1197 -- be raised if there are no recipients. The user may reset the event flags
1198 -- in the raise_events job attribute if he is sure that these events are not
1199 -- required or used.
1200 -- If one or both of recipients or events are comma-separated lists,
1201 -- all matching combinations for the given job will be removed. If both are
1202 -- NULL then all e-mail notifications for the job are removed. job_name cannot
1203 -- be NULL.
1204 PROCEDURE remove_job_email_notification
1205 (
1206 job_name IN VARCHAR2,
1207 recipients IN VARCHAR2 DEFAULT NULL,
1208 events IN VARCHAR2 DEFAULT NULL
1209 );
1210
1211 /*************************************************************
1212 * Generic Procedures
1213 *************************************************************
1214 */
1215
1216 -- Disable a program, chain, job, window or window_group.
1217 -- The procedure will NOT return an error if the object was already disabled.
1218 -- It will return an error when force is set to false and:
1219 -- name points to a program and there are jobs/chains pointing to the program
1220 -- name points to a chain and there are jobs/chains pointing to the chain
1221 -- name points to a window or window group and a job has that object as its
1222 -- schedule
1223 -- The only purpose of the force option is to point out dependencies. No
1224 -- dependent objects are altered.
1225 PROCEDURE disable(
1226 name IN VARCHAR2,
1227 force IN BOOLEAN DEFAULT FALSE,
1228 commit_semantics IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR');
1229
1230 -- Enable a program, chain, job, window or window group. The procedure will NOT
1231 -- return an error if the object was already enabled.
1232 PROCEDURE enable(
1233 name IN VARCHAR2,
1234 commit_semantics IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR');
1235
1236 -- Set an attribute of a scheduler object. Name can be the name of any
1237 -- Scheduler object. The procedure is overloaded to accept
1238 -- different datatypes.
1239 -- number types are implicitly converted to varchar2
1240 PROCEDURE set_attribute(
1241 name IN VARCHAR2,
1242 attribute IN VARCHAR2,
1243 value IN BOOLEAN);
1244 PROCEDURE set_attribute(
1245 name IN VARCHAR2,
1246 attribute IN VARCHAR2,
1247 value IN VARCHAR2,
1248 value2 IN VARCHAR2 DEFAULT NULL);
1249 PROCEDURE set_attribute(
1250 name IN VARCHAR2,
1251 attribute IN VARCHAR2,
1252 value IN DATE);
1253 PROCEDURE set_attribute(
1254 name IN VARCHAR2,
1255 attribute IN VARCHAR2,
1256 value IN TIMESTAMP);
1257 PROCEDURE set_attribute(
1258 name IN VARCHAR2,
1259 attribute IN VARCHAR2,
1260 value IN TIMESTAMP WITH TIME ZONE);
1261 PROCEDURE set_attribute(
1262 name IN VARCHAR2,
1263 attribute IN VARCHAR2,
1264 value IN TIMESTAMP WITH LOCAL TIME ZONE);
1265 PROCEDURE set_attribute(
1266 name IN VARCHAR2,
1267 attribute IN VARCHAR2,
1268 value IN INTERVAL DAY TO SECOND);
1269
1270 -- Set an attribute of a scheduler program to NULL
1271 -- This is necessary because the overloading above does not allow NULL
1272 -- as a valid value.
1273 PROCEDURE set_attribute_null(
1274 name IN VARCHAR2,
1275 attribute IN VARCHAR2);
1276
1277 -- Get the value of an attribute of a Scheduler object.
1278 -- The procedure is overloaded to support different datatypes for the
1279 -- attribute values: PLS_INTEGER, BOOLEAN,VARCHAR2, all date types.
1280 PROCEDURE get_attribute(
1281 name IN VARCHAR2,
1282 attribute IN VARCHAR2,
1283 value OUT PLS_INTEGER);
1284 PROCEDURE get_attribute(
1285 name IN VARCHAR2,
1286 attribute IN VARCHAR2,
1287 value OUT BOOLEAN);
1288 PROCEDURE get_attribute(
1289 name IN VARCHAR2,
1290 attribute IN VARCHAR2,
1291 value OUT DATE);
1292 PROCEDURE get_attribute(
1293 name IN VARCHAR2,
1294 attribute IN VARCHAR2,
1295 value OUT TIMESTAMP);
1296 PROCEDURE get_attribute(
1297 name IN VARCHAR2,
1298 attribute IN VARCHAR2,
1299 value OUT TIMESTAMP WITH TIME ZONE);
1300 PROCEDURE get_attribute(
1301 name IN VARCHAR2,
1302 attribute IN VARCHAR2,
1303 value OUT TIMESTAMP WITH LOCAL TIME ZONE);
1304 PROCEDURE get_attribute(
1305 name IN VARCHAR2,
1306 attribute IN VARCHAR2,
1307 value OUT INTERVAL DAY TO SECOND);
1308 PROCEDURE get_attribute(
1309 name IN VARCHAR2,
1310 attribute IN VARCHAR2,
1311 value OUT VARCHAR2);
1312 PROCEDURE get_attribute(
1313 name IN VARCHAR2,
1314 attribute IN VARCHAR2,
1315 value OUT VARCHAR2,
1316 value2 OUT VARCHAR2);
1317
1321 */
1318 /*************************************************************
1319 * Special Scheduler Administrative Procedures
1320 *************************************************************
1322
1323 -- There are several scheduler attributes that control the behavior of the
1324 -- scheduler. These have defaults but a DBA may wish to change the default
1325 -- settings or view the current settings. These two functions are provided for
1326 -- this purpose.
1327 -- Even though the scheduler attributes have different types (e.g. strings,
1328 -- numbers) all the values are passed as string literals. The set
1329 -- procedure requires the MANAGE SCHEDULER privilege.
1330 -- This takes effect immediately, but the resulting changes may not be seen
1331 -- immediately.
1332 -- Attributes which may be set are:
1333 -- 'MAX_SLAVE_PROCESSES'(pls_integer), 'DEFAULT_LOG_PURGE_POLICY'(varchar2),
1334 -- 'LOG_HISTORY' (pls_integer)
1335
1336 -- Set the value of a scheduler attribute. This takes effect immediately,
1337 -- but the resulting changes may not be seen immediately.
1338 PROCEDURE set_scheduler_attribute(
1339 attribute IN VARCHAR2,
1340 value IN VARCHAR2);
1341
1342 -- Get the value of a scheduler attribute.
1343 PROCEDURE get_scheduler_attribute(
1344 attribute IN VARCHAR2,
1345 value OUT VARCHAR2);
1346
1347 PROCEDURE add_event_queue_subscriber(
1348 subscriber_name IN VARCHAR2 DEFAULT NULL);
1349
1350 PROCEDURE remove_event_queue_subscriber(
1351 subscriber_name IN VARCHAR2 DEFAULT NULL);
1352
1353 -- The following procedure purges from the logs based on the arguments
1354 -- The default is to purge all entries
1355 PROCEDURE purge_log(
1356 log_history IN PLS_INTEGER DEFAULT 0,
1357 which_log IN VARCHAR2 DEFAULT 'JOB_AND_WINDOW_LOG',
1358 job_name IN VARCHAR2 DEFAULT NULL);
1359
1360
1361 /*************************************************************
1362 * Auxiliary Functions and Procedures
1363 *************************************************************
1364 */
1365
1366 -- This function returns a unique name for a job.
1367 -- If prefix is NULL this will be a number from a sequence, otherwise
1368 -- it will be of the form {prefix}N where N is a number from a sequence.
1369 FUNCTION generate_job_name(
1370 prefix IN VARCHAR2 DEFAULT 'JOB$_') RETURN VARCHAR2 ;
1371 PRAGMA SUPPLEMENTAL_LOG_DATA(generate_job_name, NONE);
1372
1373 /*************************************************************
1374 * Internal Functions and Procedures
1375 *************************************************************
1376 */
1377
1378 -- These functions are for internal scheduler use. They are not intended to
1379 -- be directly called by the user.
1380
1381 FUNCTION check_sys_privs RETURN PLS_INTEGER ;
1382 PRAGMA SUPPLEMENTAL_LOG_DATA(check_sys_privs, NONE);
1383
1384 FUNCTION get_varchar2_value (a SYS.ANYDATA) RETURN VARCHAR2;
1385 PRAGMA SUPPLEMENTAL_LOG_DATA(get_varchar2_value, NONE);
1386
1387 FUNCTION get_varchar2_value (a BLOB) RETURN VARCHAR2;
1388 PRAGMA SUPPLEMENTAL_LOG_DATA(get_varchar2_value, NONE);
1389
1390 -- The following procedure purges from the logs based on class and global
1391 -- log_history
1392 PROCEDURE auto_purge;
1393
1394 -- This accepts an attribute name and returns the default value.
1395 -- If the attribute is not recognized it returns NULL.
1396 -- If the attribute is of type BOOLEAN, it will return 'TRUE' or 'FALSE'.
1397 FUNCTION get_default_value (attribute_name VARCHAR2) RETURN VARCHAR2 ;
1398 PRAGMA SUPPLEMENTAL_LOG_DATA(get_default_value, NONE);
1399
1400 -- this is used by chain views to output rule actions
1401 FUNCTION get_chain_rule_action(action_in IN re$nv_list) RETURN VARCHAR2;
1402 PRAGMA SUPPLEMENTAL_LOG_DATA(get_chain_rule_action, NONE);
1403
1404 -- this is used by chain views to output rule conditions
1405 FUNCTION get_chain_rule_condition(action_in IN re$nv_list, condition_in IN VARCHAR2)
1406 RETURN VARCHAR2;
1407 PRAGMA SUPPLEMENTAL_LOG_DATA(get_chain_rule_condition, NONE);
1408
1409 -- this is used to retrieve the canonicalized object owner or name
1410 FUNCTION resolve_name(
1411 full_name IN VARCHAR2,
1412 default_owner IN VARCHAR2,
1413 return_part IN NUMBER) RETURN VARCHAR2;
1414 PRAGMA SUPPLEMENTAL_LOG_DATA(resolve_name, NONE);
1415
1416 -- this is the execution engine for remote external jobs. It checks all
1417 -- required privileges. This can only be called from the job slave.
1418 PROCEDURE submit_remote_external_job (
1419 job_name IN VARCHAR2,
1420 job_subname IN VARCHAR2,
1421 job_owner IN VARCHAR2,
1422 command IN VARCHAR2,
1423 arguments IN ODCIVARCHAR2LIST,
1424 credential_name IN VARCHAR2,
1425 credential_owner IN VARCHAR2,
1426 destination IN VARCHAR2,
1427 destination_owner IN VARCHAR2,
1428 destination_name IN VARCHAR2,
1429 job_dest_id IN VARCHAR2,
1430 job_action IN VARCHAR2,
1431 job_scheduled_start IN TIMESTAMP WITH TIME ZONE,
1432 job_start IN TIMESTAMP WITH TIME ZONE,
1433 window_start IN TIMESTAMP WITH TIME ZONE,
1434 window_end IN TIMESTAMP WITH TIME ZONE,
1435 chainid IN VARCHAR2,
1436 request_id IN NUMBER,
1437 log_id IN NUMBER,
1438 logging_level IN NUMBER,
1439 store_output IN NUMBER,
1440 connect_credential_name IN VARCHAR2,
1441 connect_credential_owner IN VARCHAR2);
1445 /*************************************************************
1442 PRAGMA SUPPLEMENTAL_LOG_DATA(submit_remote_external_job, NONE);
1443
1444
1446 * Calendar utility functions for schedule type sched_calendar_string
1447 *************************************************************
1448 */
1449
1450 TYPE bylist IS VARRAY (256) OF PLS_INTEGER;
1451
1452 Yearly Constant Pls_Integer := 1;
1453 Monthly Constant Pls_Integer := 2;
1454 Weekly Constant Pls_Integer := 3;
1455 Daily Constant Pls_Integer := 4;
1456 Hourly Constant Pls_Integer := 5;
1457 Minutely Constant Pls_Integer := 6;
1458 Secondly Constant Pls_Integer := 7;
1459
1460
1461 Monday Constant Integer := 1;
1462 Tuesday Constant Integer := 2;
1463 Wednesday Constant Integer := 3;
1464 Thursday Constant Integer := 4;
1465 Friday Constant Integer := 5;
1466 Saturday Constant Integer := 6;
1467 Sunday Constant Integer := 7;
1468
1469 -- byday_days contains list of days
1470 -- byday_occurrence contains the corresponding monthly (or yearly)
1471 -- occurrence -5 .. -1,0, 1 .. 5 // 0 meaning any this weekday
1472
1473 -- Example BYDAY=-2MO, -1MO, 1MO, TU
1474 -- byday_day = Monday,Monday,Monday,Tuesday
1475 -- byday_orrurrence= -2,-1, 1, 0
1476
1477 Procedure create_calendar_string(
1478 frequency in pls_integer,
1479 interval in pls_integer,
1480 bysecond in bylist,
1481 byminute in bylist,
1482 byhour in bylist,
1483 byday_days in bylist,
1484 byday_occurrence in bylist,
1485 bymonthday in bylist,
1486 byyearday in bylist,
1487 byweekno in bylist,
1488 bymonth in bylist,
1489 calendar_string out Varchar2);
1490 PRAGMA SUPPLEMENTAL_LOG_DATA(create_calendar_string, NONE);
1491 --
1492 Procedure resolve_calendar_string(
1493 calendar_string in varchar2,
1494 frequency out pls_integer,
1495 interval out pls_integer,
1496 calendars_used out boolean,
1497 bysecond out scheduler$_int_array_type,
1498 byminute out scheduler$_int_array_type,
1499 byhour out scheduler$_int_array_type,
1500 byday_days out scheduler$_int_array_type,
1501 byday_occurrence out scheduler$_int_array_type,
1502 bydate_y out scheduler$_int_array_type,
1503 bydate_md out scheduler$_int_array_type,
1504 bymonthday out scheduler$_int_array_type,
1505 byyearday out scheduler$_int_array_type,
1506 byweekno out scheduler$_int_array_type,
1507 bymonth out scheduler$_int_array_type,
1508 bysetpos out scheduler$_int_array_type);
1509 PRAGMA SUPPLEMENTAL_LOG_DATA(resolve_calendar_string, NONE);
1510
1511
1512 Procedure resolve_calendar_string(
1513 calendar_string in varchar2,
1514 frequency out pls_integer,
1515 interval out pls_integer,
1516 bysecond out bylist,
1517 byminute out bylist,
1518 byhour out bylist,
1519 byday_days out bylist,
1520 byday_occurrence out bylist,
1521 bymonthday out bylist,
1522 byyearday out bylist,
1523 byweekno out bylist,
1524 bymonth out bylist);
1525 PRAGMA SUPPLEMENTAL_LOG_DATA(resolve_calendar_string, NONE);
1526
1527 -- Repeat intervals of jobs, windows or schedules are defined using the
1528 -- scheduler's calendar syntax. This procedure evaluates the calendar string
1529 -- and tells you what the next execution date of a job or window will be. This
1530 -- is very useful for testing the correct definition of the calendar string
1531 -- without having to actually schedule the job or window.
1532 --
1533 -- Parameters
1534 -- calendar_string The to be evaluated calendar string.
1535 -- start_date The date by which the calendar string becomes valid.
1536 -- It might also be used to fill in specific items that are
1537 -- missing from the calendar string. Can optionally be NULL.
1538 -- return_date_after With the start_date and the calendar string the scheduler
1539 -- has sufficient information to determine all valid
1540 -- execution dates. By setting this argument the scheduler
1541 -- determines which one of all possible matches to return.
1542 -- When a NULL value is passed for this argument the
1543 -- scheduler automatically fills in systimestamp as its
1544 -- value.
1545 -- next_run_date The first timestamp that matches the calendar string and
1546 -- start date that occurs after the value passed in for the
1547 -- return_date_after argument.
1548
1549
1550
1551 -- This procedure can also be used to get multiple steps of the repeat interval
1552 -- by passing the next_run_date returned by one invocation as the
1553 -- return_date_after argument of the next invocation of this procedure.
1554
1555 Procedure evaluate_calendar_string(
1556 calendar_string in varchar2,
1557 start_date in timestamp with time zone,
1558 return_date_after in timestamp with time zone,
1559 next_run_date OUT timestamp with time zone);
1560 PRAGMA SUPPLEMENTAL_LOG_DATA(evaluate_calendar_string, NONE);
1561
1562 -- Set the remote execution agent registration password for this database
1563 -- optionally limit the password to a limited number of uses or to before a
1564 -- specified expiry date
1565 PROCEDURE set_agent_registration_pass(
1566 registration_password IN VARCHAR2,
1567 expiration_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
1568 max_uses IN PLS_INTEGER DEFAULT NULL);
1569 PRAGMA SUPPLEMENTAL_LOG_DATA(set_agent_registration_pass, NONE);
1570
1571 -- Internal function. Do not document
1572 FUNCTION is_scheduler_created_agent(
1573 schema_name VARCHAR2,
1574 agent_name VARCHAR2) RETURN BOOLEAN;
1575 PRAGMA SUPPLEMENTAL_LOG_DATA(is_scheduler_created_agent, NONE);
1576
1577 -- Internal function. Do not document.
1578 FUNCTION get_job_step_cf
1579 (
1580 iec VARCHAR2,
1581 icn VARCHAR2,
1582 vname VARCHAR2,
1583 iev SYS.RE$NV_LIST
1584 ) RETURN SYS.RE$VARIABLE_VALUE;
1585 PRAGMA SUPPLEMENTAL_LOG_DATA(get_job_step_cf, NONE);
1586
1587 FUNCTION generate_event_list(statusvec NUMBER) return VARCHAR2;
1588 PRAGMA SUPPLEMENTAL_LOG_DATA(generate_event_list, NONE);
1589
1590 -- ###################################################################
1591 -- ###################################################################
1592 -- NEW BATCH API
1593 -- ###################################################################
1594 -- ###################################################################
1595
1596 -- In the following routines the argument 'semantics' can have one of
1597 -- the values 'STOP_ON_FIRST_ERROR', 'TRANSACTIONAL', 'ABSORB_ERRORS'.
1598 -- If the value is 'STOP_ON_FIRST_ERROR', the routine will return on
1599 -- the first error but the previous successful operations will be
1600 -- comitted to disk. If the value is 'TRANSACTIONAL', it will return
1601 -- on the first error and the previous successful operations will be
1602 -- rolled back. If the value is 'ABSORB_ERRORS' then even if errors
1603 -- occur, the routine will proceed with until either all jobs in the
1604 -- array have been handled or a "fatal" error occurs. The successful
1605 -- operations will be comitted to disk. The exact errors for each of
1606 -- the failed jobs will be stored in UGA memory - calling the
1607 -- show_errors routine will retrieve them.
1608
1609 -- Batch create job
1610 PROCEDURE create_jobs(
1611 jobdef_array IN SYS.JOB_DEFINITION_ARRAY,
1612 commit_semantics IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR');
1613
1614 PROCEDURE create_jobs(
1615 job_array IN SYS.JOB_ARRAY,
1616 commit_semantics IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR');
1617
1618 -- Batch set job attribute
1619 PROCEDURE set_job_attributes(
1620 jobattr_array IN SYS.JOBATTR_ARRAY,
1621 commit_semantics IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR');
1622
1623 -- Batch show errors
1624 PROCEDURE show_errors(
1625 error_list OUT SYS.SCHEDULER$_BATCHERR_ARRAY);
1626
1627 PROCEDURE end_detached_job_run (
1628 job_name IN VARCHAR2,
1629 error_number IN PLS_INTEGER DEFAULT 0,
1630 additional_info IN VARCHAR2 DEFAULT NULL);
1631
1632 FUNCTION file_watch_filter(
1633 sch_name IN VARCHAR2,
1634 obj_name IN VARCHAR2,
1635 obj_subname IN VARCHAR2,
1636 fw_msgid IN RAW) RETURN NUMBER;
1637 PRAGMA RESTRICT_REFERENCES(file_watch_filter, WNDS, WNPS);
1638 PRAGMA SUPPLEMENTAL_LOG_DATA(file_watch_filter, NONE);
1639
1640 PROCEDURE check_credential(
1641 credential_name IN VARCHAR2,
1642 destination_name IN VARCHAR2 DEFAULT NULL
1643 );
1644
1645
1646 END dbms_scheduler;
[Home] [Help]