DBMS_DATA_TOOLS_JOBS Package Reference

DBMS_DATA_TOOLS_JOB Package Subprograms

The following table lists the DBMS_DATA_TOOLS_JOB subprograms and briefly describes them.

Table 19-2 DBMS_DATA_TOOLS_JOB Names and description for the DBMS_DATA_TOOLS_JOB Subprograms

Subprogram	Description
Create Job procedure	This procedure creates a new job.
Delete Job procedure	Deletes the job from the scheduler.
Rename Job procedure	Renames the job with the desired name.
Update Job procedure	Modifies the attributes of an existing job.
Run procedure	Runs a job immediately regardless of the schedule you define.
Stop Job procedure	Stops a running job. This terminates a job gracefully, and if necessary, forcibly stop it.

CREATE_JOB Procedure

This procedure creates a single job. If you create the job as enabled by setting the enabled attribute to TRUE, the Scheduler automatically runs the job according to its schedule. If you create the job disabled, the job does not run until you enable it with the Enable Job feature.

Syntax

dbms_data_tools_job.create_job(
                       job_name        in varchar2,
                       description     in varchar2,
                       steps           in clob,
                       owner           in varchar2 default null,
                       start_date      IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
                       repeat_interval IN VARCHAR2                 DEFAULT NULL,
                       end_date        IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
                       job_class       IN VARCHAR2                 DEFAULT NULL,
                       enabled         IN BOOLEAN                  DEFAULT FALSE,
                       reporting       in clob default null);

CREATE_JOB Procedure Parameters

Table 19-3 CREATE_JOB Procedure Parameters

Parameter	Description
Job Name	The name to assign to the job. If `job_name` is not specified, an error is generated.
Description	This refers to additional information that is set for jobs.
Steps	This refers to various tasks the Data Studio tool performs that comprises of a job. Steps is a JSON array. Each array element is a JSON object. Here are the JSON elements which apply to all objects: `type` - string, declares the type of step that is performed: data_load, smart_table, procedure, http. `on_errors`- string `stop`: If an error is encountered, the job is stopped and the error is raised. `continue`: the error is logged, but processing continues with the next job step. `step_name` - descriptive name of the step to appear in logs Each object type has some additional elements depending on the type. `data_load`: `data_load_name` - (mandatory) the name of a previously created data load to run `smart_table`: `recipe_name` - (mandatory) the name of a previously created smart table recipe to run. `url` - (mandatory) REST URL to connect to. `method` - GET (default), POST, PUT, DELETE, or some other HTTP verb `procedure`: `procedure` - (mandatory) name of procedure to execute. It may optionally be schema qualified, and may be a bare procedure or a package method. `arguments` - array of string, number or boolean arguments to supply to the procedure.
Owner	It is the schema in which the job is created. If you create a job without specifying a schema, the owner will be the user executing the `CREATE_JOB` procedure.
Start Date	This attribute specifies the first date and time on which this job is scheduled to start. If `start_date` and `repeat_interval` are left null, then the job is scheduled to run as soon as the job is enabled. For repeating jobs that use a calendaring expression to specify the repeat interval, `start_date` is used as a reference date. The first time the job runs is the first match of the calendaring expression that is on or after the current date and time. The Scheduler cannot guarantee that a job executes on an exact time because the system may be overloaded and thus resources unavailable.
Repeat Interval	This attribute specifies how often the window repeats. It is expressed using the Scheduler calendaring syntax. See "Calendaring Syntax" for more information. A PL/SQL expression cannot be used to specify the repeat interval for a window. The expression specified is evaluated to determine the next time the window opens. If no `repeat_interval` is specified, the window opens only once at the specified start date.
End Date	This attribute specifies the date and time after which the job expires and is no longer run. The value for `end_date` must be after the value for `start_date`. If `end_date` is less than `start_date`, then an error will be generated. If `end_date` is the same as `start_date`, then the job will not execute and no error will be generated. If no value for `end_date` is specified, the job repeats forever unless you `stop job`.
Job Class	The class this job is associated with.
Enabled	This attribute specifies whether the job is created enabled or not. The possible settings are `TRUE` or `FALSE`. By default, this attribute is set to `FALSE` and, therefore, the job is created as disabled. A disabled job means that the metadata about the job has been captured, and the job exists as a database object. However, the Scheduler ignores the job and the job coordinator does not pick it for processing. In order for the job coordinator to process the job, the job must be enabled. You can enable a job by selecting the `Enable Job` icon.
Reporting	This attribute specifies job execution details via Report button in the Jobs feature. This is a JSON object similar to the reporting argument for `dbms_live_feed`. It can have the following elements: `completed`: Targets to notify when a job is completed. `errors`: Targets to notify when some step of a job encountered errors. `failed`: Targets to notify when a job had some fatal error outside of the context of a step. `long`: Targets to notify when a job ran long. Each of these four elements can specify an array of email address in the `smtp` element or slack channels in the `slack` element. The `long` element can have a `max_runtime_seconds` element which determines how long a job has to run before it is considered to have run too `long`.

DELETE_JOB Procedure

This procedure deletes a job.

Syntax

dbms_data_tools_job.delete_job(job_name in varchar2, owner in varchar2 default null);

DELETE_JOB Procedure Parameters

Table 19-4 DELETE_JOB Procedure Parameters

Parameter	Description
Job Name	The name to assign to the job. If `job_name` is not specified, an error is generated.
Owner	It is the schema in which the job is created. If you create a job without specifying a schema, the owner will be the user executing the `CREATE_JOB` procedure.

RENAME_JOB Procedure

This procedure renames a job.

Syntax

dbms_data_tools_job.rename_job(old_job_name in varchar2,
                       new_job_name in varchar2,
                       owner        in varchar2 default null);

RENAME_JOB Procedure Parameters

Table 19-5 RENAME_JOB Procedure Parameters

Parameter	Description
Old Job Name	The name of the existing job.
New Job Name	The name of the new job.
Owner	It is the schema in which the job is created.

UPDATE_JOB Procedure

This procedure updates any of the existing job attributes.

Syntax

dbms_data_tools_job.update_job(job_name in varchar2,
                       owner           in varchar2 default null,
                       description     in varchar2 default null,
                       steps           in clob default null,
                       start_date      IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
                       repeat_interval IN VARCHAR2                 DEFAULT NULL,
                       end_date        IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
                       job_class       IN VARCHAR2                 DEFAULT NULL,
                       enabled         IN BOOLEAN                  DEFAULT NULL,
                       reporting       in clob default null);

UPDATE_JOB Procedure Parameters

Table 19-6 UPDATE_JOB Procedure Parameters

Parameter	Description
Job Name	The name of the Job to update.
Owner	It is the schema in which the job is to be updated.
Description	The updated description to apply.
Steps	The new steps to update to.
Start Date	The new start date.
Repeat Interval	The updated repeat interval.
End Date	The updated end date.
Job Class	The updated job class.
Enabled	It could be enabled or disabled.
Reporting	The updated reporting attribute.

Run Procedure

This procedure runs a job immediately.

If a job is enabled, the Scheduler runs it automatically. It is not necessary to call RUN_JOB to run a job according to its schedule. Use RUN_JOB to run a job outside of its normal schedule.

Syntax

dbms_data_tools_job.run(job_name            in varchar2,
                owner               in varchar2 default null,
                use_current_session in number   default null,
                options             in clob     default null);

RUN Procedure Parameters

Table 19-7 RUN Procedure Parameters

Parameter	Description
Job Name	A job name or a comma-separate list of entries, where each is the name of an existing job, optionally preceded by a schema name and dot separator. If you specify a multiple-destination job, the job runs on all destinations. In this case, the `use_current_session` argument must be `FALSE`.
Owner	It is the schema in which the job is run.
Use Current Session	This specifies whether or not the job run should occur in the same session that the procedure was invoked from. When `use_current_session` is set to `TRUE`: You can test a job and see any possible errors on the command line. `RUN` can be run in parallel with a regularly scheduled job run. When `use_current_session` is set to `FALSE`: You need to check the job log to find error information. All relevant fields in `scheduler_jobs` are updated. `RUN` fails if a regularly scheduled job is running.
Options

STOP_JOB Procedure

This procedure stops currently running jobs or all jobs in a job class.

After stopping the job, the state of a one-time job is set to STOPPED, whereas the state of a repeating job is set to SCHEDULED or COMPLETED, depending on whether the next run of the job is scheduled.

Syntax

 dbms_data_tools_job.stop_job(job_name in varchar2,
                     owner    in varchar2 default null,
                     force    in boolean  default false);

STOP_JOB Procedure Parameters

Table 19-8 STOP_JOB Procedure Parameters

Parameter Description

Job Name

Parameter	Description
Job Name	Name of a job to stop. It is the name of an existing job, optionally preceded by a schema name and dot separator.
Owner	It is the schema in which the job is to be stopped.
Force	If `force` is set to `FALSE`, the Scheduler tries to gracefully stop the job using an interrupt mechanism. This method gives control back to the slave process, which can update the status of the job in the job queue to stopped. If this fails, an error is returned. If `force` is set to `TRUE`, the Scheduler immediately terminates the job slave. Oracle recommends that `STOP_JOB` with force set to `TRUE` be used only after a `STOP_JOB` with `force` set to `FALSE` has failed. Use of the `force` option requires the `MANAGE SCHEDULER` system privilege.

Name of a job to stop. It is the name of an existing job, optionally preceded by a schema name and dot separator.

Owner

It is the schema in which the job is to be stopped.

Force

If force is set to FALSE, the Scheduler tries to gracefully stop the job using an interrupt mechanism. This method gives control back to the slave process, which can update the status of the job in the job queue to stopped. If this fails, an error is returned.

If force is set to TRUE, the Scheduler immediately terminates the job slave. Oracle recommends that STOP_JOB with force set to TRUE be used only after a STOP_JOB with force set to FALSE has failed.

Use of the force option requires the MANAGE SCHEDULER system privilege.

DBMS_DATA_TOOLS_JOBS Package Reference

Summary of DBMS_DATA_TOOLS_JOB Subprograms