67 DBMS_DATA_TOOLS_JOBS

The DBMS_DATA_TOOLS_JOBS package and subprograms are the Oracle Data Studio PL/SQL scheduling-oriented procedures and functions to define, modify, run, and stop jobs.

67.1 About the DBMS_CATALOG Package

The DBMS_DATA_TOOLS_JOBS package is the Oracle Data Studio PL/SQL API for managing jobs. It provides a set of scheduling-oriented procedures and functions to define, modify, run, and stop jobs.

Oracle Data Studio previously was available only with Oracle Autonomous AI Database. Starting with Oracle Database 26ai (23.26.2), you can now install this feature as an option for your database. For installation details see Oracle AI Database Utilities.

The following ins an overview of DBMS_DATA_TOOLS_JOBS package functions:

  • A job is a scheduled unit of work managed by the Oracle Scheduler. Jobs can be created, enabled (run automatically on schedule) or disabled (metadata exists, but the Scheduler ignores it until enabled).

  • Jobs contain Steps. A job is composed of steps stored as a JSON array (CLOB). Each step has common attributes such as:

    • type (step type): data_load, smart_table, procedure, http
    • on_errors: behavior when a step errors (stop to stop/raise error; continue to log and proceed)

    • step_name: label used in logs

    • Each type can require additional attributes. For example:

      data_load: data_load_name (mandatory; references a precreated Data Studio data load)
    • smart_table: recipe_name (mandatory; references a precreated recipe)
    • procedure: procedure (mandatory; can be schema-qualified, package method allowed) and arguments array

  • Scheduling attributes: Jobs support start_date, repeat_interval (Scheduler calendaring syntax), end_date, and job_class. If both start_date and repeat_interval are NULL, the job is scheduled to run as soon as it is enabled.

  • Reporting/notifications: Jobs can include a reporting JSON object (CLOB) to configure notifications for completed, errors, failed, and long runs. Targets can include email addresses (smtp) and Slack channels (slack). The long section can include max_runtime_seconds.

  • The main operations subprograms include the following:

    • CREATE_JOB: create a job (with steps, schedule, enabled flag, reporting)
    • DELETE_JOB: remove job from the scheduler
    • RENAME_JOB: rename an existing job
    • UPDATE_JOB: change job attributes (including steps, schedule, enabled, reporting)
    • RUN: run a job immediately (outside its schedule)
      • Can run in the current session for testing/interactive error visibility, or in a separate session requiring log review.
    • STOP_JOB: Stop a running job gracefully, or if you choose, forcibly.
      • force => TRUE: Requires MANAGE SCHEDULER system privilege. Oracle recommends that you use this only after a graceful stop fails.

For details about using this package and its subprograms, refer to Database Actions Using Guide for Oracle Cloud

Related Topics