Oracle Cloud Infrastructure Documentation

DBMS_DATA_TOOLS_JOBS Package Reference

This chapter provides information about the packages you use with the Jobs feature in Data Studio. The Summary of DBMS_DATA_TOOLS_JOBS Subprograms topic also covers the procedures included in the DBMS_DATA_TOOLS_JOBS package.

Topics:

Summary of DBMS_DATA_TOOLS_JOB Subprograms

Parent topic: The Jobs Feature

Summary of DBMS_DATA_TOOLS_JOB Subprograms

The DBMS_DATA_TOOLS_JOB package provides a collection of scheduling functions and procedures you use with the Jobs feature in the Data Studio suite of tools.

DBMS_DATA_TOOLS_JOB Package Subprograms

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

Table 3-11 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 3-12 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 3-13 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 3-14 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 3-15 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 3-16 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 3-17 STOP_JOB Procedure Parameters

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.