DBMS_SCHEDULER

https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html
Admin
DBMS_SCHEDULER is an Oracle Database PL/SQL package that provides scheduling functions and...
Tokens:87,225
Snippets:549
Trust Score:9.7
Update:8 months ago
Show doc for...
Tokens:
Raw
### Example of BYSETPOS with INTERVAL in Oracle Scheduler

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

Shows a practical example of using the `BYSETPOS` clause in conjunction with the `INTERVAL` clause for a monthly frequency. This specific example selects the second to last workday of a month and repeats this selection once per quarter, demonstrating how `INTERVAL` skips months while `BYSETPOS` operates on the generated list of timestamps within each period.

```text
FREQ=MONTHLY;INTERVAL=3;BYDAY=MON,TUE,WED,THU,FRI;BYSETPOS=-2
```

--------------------------------

### Scheduler Chain Action Examples (PL/SQL)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

Examples demonstrating the action clauses that can be used with the DEFINE_CHAIN_RULE procedure. Actions specify what happens when a rule's condition is met.

```plsql
-- Start a single step
START step_1

-- Start multiple steps with a delay
AFTER 00:05:00 START step_1, step_2

-- Stop a step
STOP step_3

-- End the chain successfully
END

-- End the chain with a specific error code
END 101

-- End the chain with the error code from a step
END step_4.error_code

```

--------------------------------

### Example: Run Chain with Initial Step States (PL/SQL)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

An example demonstrating how to use the RUN_CHAIN procedure with initial step states. It initializes a SYS.SCHEDULER$_STEP_TYPE_LIST and calls RUN_CHAIN to execute a chain named 'my_chain'.

```PL/SQL
declare
  initial_step_states sys.scheduler$_step_type_list;
begin
  initial_step_states := sys.scheduler$_step_type_list(
    sys.scheduler$_step_type('step1', 'SUCCEEDED'),
    sys.scheduler$_step_type('step2', 'FAILED 27486'),
    sys.scheduler$_step_type('step3', 'SUCCEEDED'),
    sys.scheduler$_step_type('step5', 'SUCCEEDED'));
  dbms_scheduler.run_chain('my_chain', initial_step_states);
end;
/

```

--------------------------------

### Define Retail Fiscal Year Schedule using DBMS_SCHEDULER

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

This PL/SQL example defines a fiscal year schedule for the retail industry, starting on the Sunday closest to February 1st and defining subsequent quarters. It utilizes user-defined frequencies, spans, and offsets.

```plsql
begin
 dbms_scheduler.create_schedule('year_start', repeat_interval=>
       'FREQ=YEARLY;BYDATE=0201^SPAN:1W;BYDAY=SUN');
 dbms_scheduler.create_schedule('retail_fiscal_year',
        to_timestamp_tz('15-JAN-2005 12:00:00','DD-MON-YYYY HH24:MI:SS'),
         'year_start,year_start+13w,year_start+26w,year_start+39w;periods=4');
end;
/
```

--------------------------------

### Rule Actions

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

Examples of defining actions to be performed based on rule conditions in Oracle Scheduler.

```APIDOC
## Rule Actions

### Description
Examples of defining actions to be performed based on rule conditions in Oracle Scheduler.

### Examples
```
'AFTER 01:00:00 START step1, step2'
--After an hour start steps step1 and step2

'STOP step1'
--Stop step step1

END step4.error_code'
--End the chain with the error code that step step4 finished with. If step4 has not completed, the chain will be ended unsuccessfully with error code 27435.

'END' or 'END 0'
--End the chain successfully (with error_code 0)

'END 100'
--End the chain unsuccessfully with error code 100.
```
```

--------------------------------

### Run Chain with Start Steps (PL/SQL)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

Immediately runs a chain or part of a chain by creating a run-once job. This overload allows specifying a comma-separated list of steps to initiate the chain execution. A job name can optionally be provided.

```PL/SQL
DBMS_SCHEDULER.RUN_CHAIN (
   chain_name                IN VARCHAR2,
   start_steps               IN VARCHAR2,
   job_name                  IN VARCHAR2 DEFAULT NULL);

```

--------------------------------

### Rule Conditions Using SQL Syntax

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

Examples of defining rule conditions using SQL syntax within Oracle Scheduler.

```APIDOC
## Rule Conditions Using SQL Syntax

### Description
Examples of defining rule conditions using SQL syntax within Oracle Scheduler.

### Examples
```
':step1.completed = \'TRUE\' AND :step1.end_date >SYSDATE-1/24'
--satisfied when step step1 completed less than an hour ago

':step1.duration > interval \'5\' minute'
-- satisfied when step step1 has completed and took longer than 5 minutes to complete
```
```

--------------------------------

### Create SQL Script Program in Oracle Scheduler

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

This example demonstrates creating a 'SQL_SCRIPT' program using DBMS_SCHEDULER.CREATE_PROGRAM. The program_action is the path to the SQL*Plus script. Jobs using this program type require credentials with OS username and password.

```PLSQL
BEGIN
  DBMS_SCHEDULER.CREATE_PROGRAM (
    program_name   => 'my_sql_script_program',
    program_type   => 'SQL_SCRIPT',
    program_action => '/path/to/my/sql_script.sql'
  );
END;
```

--------------------------------

### Scheduler Chain Condition Syntax Examples (PL/SQL)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

Examples illustrating the Scheduler chain condition syntax for defining rules. These conditions are boolean expressions that determine when an action should be performed.

```plsql
-- Simple TRUE condition
TRUE

-- Step succeeded condition
stepname SUCCEEDED

-- Step failed condition with specific error codes
stepname ERROR_CODE IN (100, 200, 300)

-- Combined conditions using AND/OR
(step1 SUCCEEDED AND step2 FAILED) OR step3 COMPLETED

-- Using NOT operator
NOT (step4 STOPPED)

```

--------------------------------

### CREATE_EVENT_SCHEDULE Procedure

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

This procedure creates an event schedule, which is used to start a job when a particular event is raised. It requires a schedule name, event condition, and queue specification, with optional start date, end date, and comments.

```APIDOC
## CREATE_EVENT_SCHEDULE Procedure

### Description
Creates an event schedule that triggers a job when a specific event is raised. This schedule is defined by an event condition and a queue specification.

### Method
`DBMS_SCHEDULER.CREATE_EVENT_SCHEDULE`

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
- **schedule_name** (VARCHAR2) - Required - The unique name to assign to the schedule. Must be unique within the SQL namespace.
- **start_date** (TIMESTAMP WITH TIME ZONE) - Optional - The date and time when the schedule becomes valid. Events before this time are ignored.
- **event_condition** (VARCHAR2) - Optional - A conditional expression based on the columns of the event source queue table, following Advanced Queuing rule syntax.
- **queue_spec** (VARCHAR2) - Required - Specifies a file watcher name or the queue where events are enqueued. For secure queues, it's a pair of 'queue_name, agent name'.
- **end_date** (TIMESTAMP WITH TIME ZONE) - Optional - The date and time after which the schedule is no longer valid. Must be after `start_date`.
- **comments** (VARCHAR2) - Optional - An optional comment about the schedule.

### Request Example
```sql
BEGIN
  DBMS_SCHEDULER.CREATE_EVENT_SCHEDULE (
    schedule_name   => 'my_event_schedule',
    event_condition => 'tab.user_data.payload.status = ''processed''',
    queue_spec      => 'my_event_queue',
    start_date      => SYSTIMESTAMP,
    end_date        => SYSTIMESTAMP + INTERVAL '1' DAY'
  );
END;
/
```

### Response
#### Success Response (200)
This procedure does not return a value upon successful execution. Success is indicated by the absence of an error.

#### Response Example
(No specific response body, success is indicated by completion without error)
```

--------------------------------

### Filter Condition Example (PL/SQL)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

Provides an example of how to use the 'filter_condition' parameter in the ADD_JOB_EMAIL_NOTIFICATION procedure. This demonstrates filtering email notifications based on specific event properties, such as error codes.

```PL/SQL
-- Example filter_condition:
':event.error_code=600 or :event.error_code=700'

```

--------------------------------

### SQL WHERE Clause Syntax for Conditions (PL/SQL)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

Examples of using SQL WHERE clause syntax for conditions in DEFINE_CHAIN_RULE. This allows referencing chain step attributes.

```plsql
-- Condition based on a step's completion status
"step_name.completed" = 'TRUE'

-- Condition based on a step's state
"step_name.state" = 'SUCCEEDED'

-- Condition based on a step's error code
"step_name.error_code" > 0

-- Condition combining multiple step attributes
("step1.state" = 'SUCCEEDED' AND "step2.duration" > 3600)

```

--------------------------------

### Rule Conditions Using Scheduler Chain Syntax

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

Examples of defining rule conditions using Oracle Scheduler's chain condition syntax.

```APIDOC
## Rule Conditions Using Scheduler Chain Syntax

### Description
Examples of defining rule conditions using Oracle Scheduler's chain condition syntax.

### Examples
```
'step1 completed'
-- satisfied when step step1 has completed. (step1 completed is also TRUE when any 
-- of the following are TRUE: step1 succeeded, step1 failed, step1 stopped.)

'step1 succeeded and step2 succeeded'
-- satisfied when steps step1 and step2 have both succeeded

'step1 error_code > 100'
-- satisfied when step step1 has failed with an error_code greater than 100

'step1 error_code IN (1, 3, 5, 7)'
-- satisfied when step step1 has failed with an error_code of 1, 3, 5, or 7
```
```

--------------------------------

### Example of DBMS_SCHEDULER STOP_JOB with comma-delimited list

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

Demonstrates the behavior of the STOP_JOB procedure when provided with a comma-delimited list of job names. It highlights that the execution stops at the first encountered error, preventing subsequent jobs in the list from being processed.

```plsql
BEGIN
  DBMS_SCHEDULER.STOP_JOB ('job1, job2, job3, sys.jobclass1, sys.jobclass2, sys.jobclass3');
END;
```

--------------------------------

### Example of BYWEEKNO Clause Behavior in Oracle Scheduler

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

Demonstrates how the `byweekno` clause can return dates from a different year than the current one, especially when dealing with week numbers that span across year boundaries. This example shows dates returned for a yearly frequency with specific week numbers.

```text
12/29/03, 12/30/03, 12/31/03, 01/01/04, 01/02/04, 01/03/04, 01/04/04, 12/27/04, 12/28/04, 12/29/04, 12/30/04, 12/31/04, 01/01/05, 01/02/05
```

--------------------------------

### Create Stored Procedure Program in Oracle Scheduler

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

This example shows how to create a program of type 'STORED_PROCEDURE' using DBMS_SCHEDULER.CREATE_PROGRAM. The program_action should be the name of the stored procedure, optionally including the schema. Procedures with OUT or INOUT parameters are not supported.

```PLSQL
BEGIN
  DBMS_SCHEDULER.CREATE_PROGRAM (
    program_name   => 'my_stored_proc_program',
    program_type   => 'STORED_PROCEDURE',
    program_action => 'my_schema.my_procedure'
  );
END;
```

--------------------------------

### Default Repeat Interval from Start Date in Oracle Scheduler

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

This example shows how Oracle Scheduler defaults `repeat_interval` clauses from the job's start date when specific clauses are omitted. Here, a yearly frequency defaults to the month, day, hour, and minute of the start date.

```SQL
start_date:      4/15/05 9:00:00
repeat_interval: freq=yearly
```

```SQL
freq=yearly;bymonth=4;bymonthday=15;byhour=9;byminute=0;bysecond=0
```

--------------------------------

### Repeat Interval with INCLUDE Clause in Oracle Scheduler

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

This example demonstrates how the `INCLUDE` clause in `repeat_interval` overrides date-related defaults from the start date. Time-specific defaults are still applied. The schedule executes on specific dates defined by the included schedule, combined with the start date's time.

```SQL
start_date:      01/20/05 9:00:00
repeat_interval: freq=yearly;include=S1
```

```SQL
freq=yearly;byhour=9;byminute=0;bysecond=0;include=S1
```

--------------------------------

### Oracle Scheduler Chain Rule Actions

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

Examples of rule actions in Oracle Scheduler, defining operations to be performed based on chain conditions. These include starting, stopping, and ending chain steps.

```SQL
'AFTER 01:00:00 START step1, step2'
--After an hour start steps step1 and step2

'STOP step1'
--Stop step step1

END step4.error_code'
--End the chain with the error code that step step4 finished with. If step4 has not completed, the chain will be ended unsuccessfully with error code 27435.

'END' or 'END 0'
--End the chain successfully (with error_code 0)

'END 100'
--End the chain unsuccessfully with error code 100.
```

--------------------------------

### Constructing a JOB_DEFINITION Object in PL/SQL

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

This PL/SQL code snippet demonstrates the syntax for the `job_definition` constructor function. It allows for the creation of a job definition object with various parameters to configure job behavior, scheduling, and execution.

```plsql
constructor function job_definition (
    job_name                IN     VARCHAR2,
    job_style               IN     VARCHAR2 DEFAULT 'REGULAR',
    program_name            IN     VARCHAR2 DEFAULT NULL,
    job_action              IN     VARCHAR2 DEFAULT NULL,
    job_type                IN     VARCHAR2 DEFAULT NULL,
    schedule_name           IN     VARCHAR2 DEFAULT NULL,
    repeat_interval         IN     VARCHAR2 DEFAULT NULL,
    event_condition         IN     VARCHAR2 DEFAULT NULL,
    queue_spec              IN     VARCHAR2 DEFAULT NULL,
    start_date              IN     TIMESTAMP WITH TIME ZONE DEFAULT NULL,
    end_date                IN     TIMESTAMP WITH TIME ZONE DEFAULT NULL,
    number_of_arguments     IN     NATURAL DEFAULT NULL,
    arguments               IN     SYS.JOBARG_ARRAY DEFAULT NULL,
    job_class               IN     VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS',
    schedule_limit          IN     INTERVAL DAY TO SECOND DEFAULT NULL,
    job_priority            IN     NATURAL DEFAULT NULL,
    job_weight              IN     NATURAL DEFAULT NULL,
    max_run_duration        IN     INTERVAL DAY TO SECOND DEFAULT NULL,
    max_runs                IN     NATURAL DEFAULT NULL,
    max_failures            IN     NATURAL DEFAULT NULL,
    logging_level           IN     NATURALN DEFAULT 64,
    restartable             IN     BOOLEAN DEFAULT FALSE,
    stop_on_window_close    IN     BOOLEAN DEFAULT FALSE,
    raise_events            IN     NATURAL DEFAULT NULL,
    comments                IN     VARCHAR2 DEFAULT NULL,
    auto_drop               IN     BOOLEAN DEFAULT TRUE,
    enabled                 IN     BOOLEAN DEFAULT FALSE,
    follow_default_timezone IN     BOOLEAN DEFAULT FALSE,
    parallel_instances      IN     BOOLEAN DEFAULT FALSE,
    aq_job                  IN     BOOLEAN DEFAULT FALSE,
    instance_id             IN     NATURAL DEFAULT NULL,
    credential_name         IN     VARCHAR2 DEFAULT NULL,
    destination             IN     VARCHAR2 DEFAULT NULL,
    database_role           IN     VARCHAR2 DEFAULT NULL,
    allow_runs_in_restricted_mode IN BOOLEAN DEFAULT FALSE)
    RETURN SELF AS RESULT;
```

--------------------------------

### DBMS_SCHEDULER CREATE_JOB Parameters

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

This section details the parameters used in the CREATE_JOB procedure for DBMS_SCHEDULER, covering job scheduling, execution, and metadata.

```APIDOC
## DBMS_SCHEDULER CREATE_JOB Parameters

### Description
This documentation outlines the various parameters available for the `CREATE_JOB` procedure within the `DBMS_SCHEDULER` package. These parameters control the scheduling, execution, and behavior of database jobs.

### Method
`DBMS_SCHEDULER.CREATE_JOB` (procedure)

### Parameters

#### Source Queue Arguments
- **`queue_spec`** (string) - Required - The source queue where events that start this job are enqueued. If secure, format is `queue_name, agent_name`. If not secure, only `queue_name` is needed. Assumed to be in the job owner's schema if not fully qualified.
- **`file_watcher_name`** (string) - Optional - A file watcher name. Refer to Oracle AI Database Administrator's Guide for details.

#### Schedule and Execution Parameters
- **`repeat_interval`** (string) - Optional - Specifies how often the job repeats. Can use calendaring or PL/SQL expressions. If not specified, the job runs once at the `start_date`. See "Calendaring Syntax" for more information.
- **`schedule_name`** (string) - Optional - The name of the schedule, window, or window group associated with this job.
- **`job_class`** (string) - Optional - The class this job is associated with.
- **`end_date`** (timestamp) - Optional - The date and time after which the job expires. If `auto_drop` is `TRUE`, the job is dropped. If `auto_drop` is `FALSE`, the job is disabled and its state is set to `COMPLETED`. If not specified, the job repeats forever unless `max_runs` or `max_failures` is set. Must be after `start_date`. If equal to `start_date`, the job will not execute.

#### Job Metadata and Style Parameters
- **`comments`** (string) - Optional - A comment about the job. Defaults to `NULL`.
- **`job_style`** (string) - Optional - The style of the job. Can be one of:
  - `'REGULAR'` (default): Creates a regular job.
  - `'LIGHTWEIGHT'`: Creates a lightweight job. Permitted only when referencing a program object. Suitable for many short-duration, frequent jobs.
  - `'IN_MEMORY_RUNTIME'`: Creates an in-memory runtime job. Based on lightweight job structures with an in-memory cache to minimize disk access.
  - `'IN_MEMORY_FULL'`: Creates an in-memory full job. Requires a program, cannot have a schedule or repeat interval. Runs automatically when enabled and is discarded after running. Keeps all job information in memory and is lost on instance reboot.

#### Destination and Credential Parameters
- **`credential_name`** (string) - Optional - The default credential to use with the job. Applicable to remote database jobs, remote external jobs, local external jobs, script jobs, and event-based jobs processing file arrival events. Must exist. For local database jobs, must be `NULL`. For local external jobs, if `NULL`, a preferred credential is selected. See Oracle AI Database Administrator's Guide.
- **`destination_name`** (string) - Optional - The database or external destination for the job. Use for remote database jobs and remote external jobs only. Must be `NULL` for local jobs. Can be a single destination name or a group of type '`EXTERNAL_DEST`' or '`DB_DEST`'. The destination or group must exist. If a database destination, created by `CREATE_DATABASE_DESTINATION`. If an external destination, implicitly created by registering a remote Scheduler agent. If a group, all members must exist, and the job runs on all destinations in the group. Cannot reference a destination group if job type is '`CHAIN`' or job style is '`LIGHTWEIGHT`', '`IN_MEMORY_RUNTIME`', or '`IN_MEMORY_FULL`'.

**Note on `destination_name` and `credential_name`:** If `credential_name` is `NULL`, each destination in `destination_name` must be preceded by a credential in the format: `credential.destination`.

### Request Example
```sql
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name          => 'my_regular_job',
    job_type          => 'PLSQL_BLOCK',
    job_action        => 'BEGIN NULL; END;',
    start_date        => SYSTIMESTAMP,
    repeat_interval   => 'FREQ=DAILY; BYHOUR=9', 
    end_date          => SYSTIMESTAMP + INTERVAL '30' DAY,
    job_class         => 'DEFAULT_JOB_CLASS',
    comments          => 'A sample regular job.',
    job_style         => 'REGULAR',
    credential_name   => NULL,
    destination_name  => NULL
  );
END;
/
```

### Response
#### Success Response (200)
- **`job_name`** (string) - The name of the created job.
- **`state`** (string) - The initial state of the job (e.g., ENABLED, DISABLED).

#### Response Example
(This is a procedural call, success is indicated by no errors. Job details can be queried using `DBMS_SCHEDULER.GET_JOB_PROPERTIES`)

```

--------------------------------

### Create Backup Script Program in Oracle Scheduler

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

This snippet shows how to create a 'BACKUP_SCRIPT' program using DBMS_SCHEDULER.CREATE_PROGRAM. The program_action is the path to the RMAN backup script. The Scheduler uses the RMAN executable from the current Oracle home.

```PLSQL
BEGIN
  DBMS_SCHEDULER.CREATE_PROGRAM (
    program_name   => 'my_backup_script_program',
    program_type   => 'BACKUP_SCRIPT',
    program_action => '/opt/oracle/scripts/rman_backup.rman'
  );
END;
```

--------------------------------

### Create Schedule Procedure Syntax - PL/SQL

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

This PL/SQL code snippet demonstrates the syntax for the CREATE_SCHEDULE procedure. It outlines the parameters required to define a new schedule, including its name, timing, and optional comments. This procedure is essential for defining recurring or one-time execution intervals for jobs.

```PL/SQL
DBMS_SCHEDULER.CREATE_SCHEDULE (
   schedule_name          IN VARCHAR2,
   start_date             IN TIMESTAMP WITH TIMEZONE DEFAULT NULL,
   repeat_interval        IN VARCHAR2,
   end_date               IN TIMESTAMP WITH TIMEZONE DEFAULT NULL,
   comments               IN VARCHAR2 DEFAULT NULL);

```

--------------------------------

### Create a Database Destination Group with DBMS_SCHEDULER

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

This PL/SQL code snippet demonstrates how to create a database destination group using the DBMS_SCHEDULER.CREATE_GROUP procedure. It specifies the group name, type ('DB_DEST'), a comma-separated list of members (including 'LOCAL' and specific destinations with credentials), and descriptive comments. This is useful for defining a collection of remote databases for job execution.

```PL/SQL
BEGIN
  DBMS_SCHEDULER.CREATE_GROUP(
    GROUP_NAME    => 'production_dest1',
    GROUP_TYPE    => 'DB_DEST',
    MEMBER        => 'LOCAL, oracle_cred@prodhost1, prodhost2',
    COMMENTS      => 'All sector1 production machines');
END;
```

--------------------------------

### CREATE_PROGRAM

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

This section details the creation of programs within the DBMS_SCHEDULER package. Programs define the actions to be executed by jobs, supporting external scripts, SQL scripts, and backup scripts.

```APIDOC
## CREATE_PROGRAM

### Description
This procedure allows users to create a program, which defines the action to be executed by a job. It supports different program types such as external scripts, SQL scripts, and backup scripts.

### Method
Not Applicable (This is a PL/SQL procedure call)

### Endpoint
Not Applicable (This is a PL/SQL procedure call within the DBMS_SCHEDULER package)

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
* **program_name** (VARCHAR2) - The name of the program to be created.
* **program_action** (VARCHAR2) - The action to be executed. This can be a path to a script or an inline script for external, SQL, or backup types.
* **program_type** (VARCHAR2) - The type of the program. Valid values include 'EXECUTABLE', 'SQL_SCRIPT', 'RMAN_SCRIPT', 'PLSQL_BLOCK'.
* **number_of_arguments** (NUMBER) - The number of arguments the program takes. Defaults to 0. Maximum is 255. Ignored if program_type is 'PLSQL_BLOCK'.
* **enabled** (BOOLEAN) - Flag to create the program as enabled. Defaults to FALSE.
* **comments** (VARCHAR2) - A comment about the program. Defaults to NULL.

### Request Example
```sql
BEGIN
   DBMS_SCHEDULER.CREATE_PROGRAM(
      program_name => 'my_external_script_program',
      program_action => '/usr/local/bin/my_script.sh',
      program_type => 'EXECUTABLE',
      number_of_arguments => 2,
      enabled => TRUE,
      comments => 'My external script program'
   );
END;
/
```

### Response
#### Success Response (200)
Indicates successful creation of the program.

#### Response Example
None (This is a procedure call that returns no explicit data on success, but raises an exception on failure.)

### Usage Notes
* Users need the `CREATE JOB` privilege to create a program in their own schema. Users with `CREATE ANY JOB` can create programs in any schema.
* Programs are created in a disabled state by default.
* `EXECUTE` privileges must be granted on programs for other users to execute them.
```

--------------------------------

### Job Schedule Attributes

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

Configuration options related to job scheduling, including start dates, repeat intervals, and schedule limits.

```APIDOC
## Job Schedule Attributes

### Description
Attributes that define how and when a job should be scheduled for execution.

### Attributes

- **`schedule_limit`** (INTERVAL) - Optional - Specifies the maximum delay for starting a job. If the delay exceeds this limit, the job run is skipped. Can be a value from 1 minute to 99 days. Default is null (no limit).
- **`schedule_name`** (VARCHAR2) - Optional - The name of a schedule, window, or window group to use for the job. If set, `end_date`, `start_date`, and `repeat_interval` must be NULL.
- **`start_date`** (TIMESTAMP WITH TIME ZONE) - Optional - The initial date and time for the job to start. If set, `schedule_name` must be NULL.
- **`stop_on_window_close`** (BOOLEAN) - Optional - If TRUE, the job stops when its associated window closes. Default is FALSE. This attribute only applies if the job's schedule is a window or window group.
```

--------------------------------

### Execute RMAN Backup Script in DBMS_SCHEDULER

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

The 'job_action' can be a path to an RMAN script or an inline RMAN script. The script must be available on all execution nodes. Arguments must be strings and are passed positionally.

```RMAN
RUN {
  BACKUP DATABASE;
}
```

```RMAN
@/path/to/my_rman_script.rman
```

--------------------------------

### DBMS_SCHEDULER.DEFINE_CHAIN_STEP Procedure

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

Adds or replaces a chain step and associates it with a program or a nested chain. When the chain step is started, the specified program or chain is run.

```APIDOC
## DBMS_SCHEDULER.DEFINE_CHAIN_STEP Procedure

### Description
This procedure adds or replaces a chain step and associates it with a program or a nested chain. When the chain step is started, the specified program or chain is run. If a step already exists with the name supplied in the `chain_name` argument, the new step replaces the old one.

The chain owner must have `EXECUTE` privileges on the program or chain associated with the step. Only one program or chain can run during a step.

You cannot set all possible step attributes with this procedure. Use the `ALTER_CHAIN` procedure to set additional chain step attributes, such as `credential_name` and `destination_name`.

### Method
`DBMS_SCHEDULER.DEFINE_CHAIN_STEP`

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
- **chain_name** (VARCHAR2) - Required - The name of the chain to alter.
- **step_name** (VARCHAR2) - Required - The name of the step being defined. If a step already exists with this name, the new step replaces the old one.
- **program_name** (VARCHAR2) - Required - The name of a program or chain to run during this step. The chain owner must have `EXECUTE` privileges on this program or chain.

### Request Example
```sql
BEGIN
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
    chain_name   => \'my_chain\',
    step_name    => \'my_step\',
    program_name => \'my_program\');
END;
/ 
```

### Response
#### Success Response (200)
No explicit response body, operation is confirmed by successful execution of the PL/SQL block.

#### Response Example
None

### Usage Notes
Defining a chain step requires `ALTER` privileges on the chain (either as the owner, or a user with `ALTER` privileges on the chain or the `CREATE` `ANY` `JOB` system privilege).

### See Also
* "ALTER_CHAIN Procedure"
* "DEFINE_CHAIN_EVENT_STEP Procedure"
```

--------------------------------

### Create Job Class using DBMS_SCHEDULER in PL/SQL

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

This PL/SQL code snippet demonstrates how to create a job class using the DBMS_SCHEDULER.CREATE_JOB_CLASS procedure. It shows how to specify the job class name, resource consumer group, logging level, and comments. This procedure is essential for organizing and managing database jobs.

```plsql
DECLARE
  v_job_class_name VARCHAR2(30) := 'MY_JOB_CLASS';
  v_resource_consumer_group VARCHAR2(30) := 'MY_CONSUMER_GROUP';
  v_logging_level PLS_INTEGER := DBMS_SCHEDULER.LOGGING_FULL;
  v_comments VARCHAR2(200) := 'This is my custom job class.';
BEGIN
  DBMS_SCHEDULER.CREATE_JOB_CLASS(
    job_class_name            => v_job_class_name,
    resource_consumer_group   => v_resource_consumer_group,
    logging_level             => v_logging_level,
    comments                  => v_comments
  );
  DBMS_OUTPUT.PUT_LINE('Job class ' || v_job_class_name || ' created successfully.');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error creating job class: ' || SQLERRM);
END;
```

--------------------------------

### Create File Watcher Procedure Syntax - PL/SQL

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

This PL/SQL code defines the syntax for the CREATE_FILE_WATCHER procedure within the DBMS_SCHEDULER package. It outlines the parameters required to create a file watcher, including file identification, location, and optional configuration for triggering jobs.

```plsql
DBMS_SCHEDULER.CREATE_FILE_WATCHER (
   file_watcher_name            IN VARCHAR2,
   directory_path               IN VARCHAR2,
   file_name                    IN VARCHAR2,
   credential_name              IN VARCHAR2,
   destination                  IN VARCHAR2  DEFAULT NULL,
   min_file_size                IN PLS_INTEGER DEFAULT 0,
   steady_state_duration        IN INTERVAL DAY TO SECOND DEFAULT NULL,
   comments                     IN VARCHAR2 DEFAULT NULL,
   enabled                      IN BOOLEAN DEFAULT TRUE
);
```

--------------------------------

### DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP Procedure

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

This procedure adds or replaces a chain step and associates it with an event schedule or an inline event. Once started in a running chain, this step does not complete until the specified event has occurred.

```APIDOC
## DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP Procedure

### Description
Adds or replaces a chain step, associating it with an event schedule or an inline event. The step completes only after the specified event occurs. All steps must be defined before a chain can be enabled.

### Method
`DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP`

### Endpoint
None (PL/SQL procedure call)

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
**Overload 1:**
- **chain_name** (VARCHAR2) - Required - The name of the chain.
- **step_name** (VARCHAR2) - Required - The name of the step.
- **event_schedule_name** (VARCHAR2) - Required - The name of the event schedule.
- **timeout** (INTERVAL DAY TO SECOND) - Optional - Reserved for future use.

**Overload 2:**
- **chain_name** (VARCHAR2) - Required - The name of the chain.
- **step_name** (VARCHAR2) - Required - The name of the step.
- **event_condition** (VARCHAR2) - Required - See the `CREATE_EVENT_SCHEDULE` Procedure.
- **queue_spec** (VARCHAR2) - Required - See the `CREATE_EVENT_SCHEDULE` Procedure.
- **timeout** (INTERVAL DAY TO SECOND) - Optional - Reserved for future use.

### Request Example
```sql
-- Using event schedule name
BEGIN
  DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP(
    chain_name              => 'MY_CHAIN',
    step_name               => 'WAIT_FOR_FILE_EVENT',
    event_schedule_name     => 'FILE_ARRIVAL_SCHEDULE'
  );
END;
/ 

-- Using event condition and queue specification
BEGIN
  DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP(
    chain_name              => 'MY_CHAIN',
    step_name               => 'WAIT_FOR_DB_EVENT',
    event_condition         => 'tab_updat',
    queue_spec              => 'user_queue:my_queue'
  );
END;
/ 
```

### Response
#### Success Response (200)
Procedure completes successfully.

#### Response Example
None (PL/SQL procedure call)
```

--------------------------------

### ENABLE Procedure

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

Enables a Scheduler object, such as a program, job, chain, window, or destination. When enabled, the object's 'enabled' attribute is set to TRUE, and jobs will start running according to their schedule.

```APIDOC
## ENABLE Procedure

### Description
This procedure enables a program, job, chain, window, database destination, external destination, file watcher, or group. When an object is enabled, its `enabled` attribute is set to `TRUE`. Enabling a disabled job resets its `RUN_COUNT`, `FAILURE_COUNT`, and `RETRY_COUNT`.

### Method
`PROCEDURE`

### Endpoint
`DBMS_SCHEDULER.ENABLE`

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
- **name** (VARCHAR2) - Required - The name of the Scheduler object being enabled. Can be a comma-delimited list of names.
- **commit_semantics** (VARCHAR2) - Optional - The commit semantics. Supported values: `STOP_ON_FIRST_ERROR` (default), `TRANSACTIONAL`, `ABSORB_ERRORS`.

### Request Example
```sql
BEGIN
  DBMS_SCHEDULER.ENABLE (name => 'MY_JOB');
END;
/
```

### Response
#### Success Response (0)
Indicates successful execution. No specific output is returned, but the object's state is updated.

#### Response Example
None (Procedure execution)

### Usage Notes
- Window names must be preceded by `SYS`.
- Requires specific privileges for windows, groups, external jobs, and file watchers.
- Cannot be used with the `SYS` schema.
```

--------------------------------

### Run Chain with Initial Step States (PL/SQL)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

Immediately runs a chain by creating a run-once job, setting initial states for specified steps before chain evaluation. This overload uses a list of SYS.SCHEDULER$_STEP_TYPE objects to define step states and potential error numbers.

```PL/SQL
DBMS_SCHEDULER.RUN_CHAIN (
   chain_name               IN VARCHAR2, 
   step_state_list          IN SYS.SCHEDULER$_STEP_TYPE_LIST,
   job_name                 IN VARCHAR2 DEFAULT NULL);

```

--------------------------------

### DBMS_SCHEDULER.CREATE_SCHEDULE Procedure

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

This procedure creates a new schedule within the DBMS_SCHEDULER package. It allows you to define the schedule's name, start time, repetition interval, end time, and an optional comment.

```APIDOC
## DBMS_SCHEDULER.CREATE_SCHEDULE Procedure

### Description
This procedure creates a schedule. It defines the name, timing, and repetition pattern for future jobs or windows.

### Method
`PROCEDURE`

### Endpoint
`DBMS_SCHEDULER.CREATE_SCHEDULE`

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
- **schedule_name** (VARCHAR2) - Required - The name to assign to the schedule. Must be unique within the SQL namespace.
- **start_date** (TIMESTAMP WITH TIMEZONE) - Optional, Default: NULL - The first date and time the schedule becomes valid. For repeating schedules, it's a reference date to determine the first instance.
- **repeat_interval** (VARCHAR2) - Required - How often the schedule repeats, expressed using calendaring syntax. PL/SQL expressions are not allowed for named schedules.
- **end_date** (TIMESTAMP WITH TIMEZONE) - Optional, Default: NULL - The date and time after which jobs will not run or windows will not open. Must be after `start_date`.
- **comments** (VARCHAR2) - Optional, Default: NULL - An optional comment about the schedule.

### Request Example
```sql
BEGIN
  DBMS_SCHEDULER.CREATE_SCHEDULE (
    schedule_name => 'MY_WEEKLY_REPORT_SCHEDULE',
    start_date => TO_TIMESTAMP_TZ('2023-10-27 09:00:00 Europe/London', 'YYYY-MM-DD HH24:MI:SS TZR'),
    repeat_interval => 'FREQ=WEEKLY;BYDAY=MON;BYHOUR=9;BYMINUTE=0',
    comments => 'Weekly report generation every Monday at 9 AM.'
  );
END;
/
```

### Response
#### Success Response (200)
This procedure does not return a value upon successful execution. Success is indicated by the absence of an error.

#### Response Example
(No explicit response body for procedure execution. Success is indicated by completion without error.)

### Usage Notes
Requires the `CREATE JOB` privilege to create a schedule in your own schema or `CREATE ANY JOB` to create in another schema. Schedules are created with `PUBLIC` access by default.
```

--------------------------------

### Create Backup RMAN Script Program in DBMS_SCHEDULER

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

Defines a RMAN backup script program for DBMS_SCHEDULER. The program_action can be a path to a RMAN script or an inline RMAN script. Arguments must be strings or castable to strings and are passed positionally.

```PL/SQL
BEGIN
   DBMS_SCHEDULER.CREATE_PROGRAM(
      program_name   => 'my_rman_program',
      program_type   => 'BACKUP_SCRIPT',
      program_action => 'BACKUP DATABASE;',
      number_of_arguments => 0,
      enabled            => FALSE,
      comments           => 'My RMAN backup script program'
   );
END;
/
```

--------------------------------

### SQL Rule Conditions for Scheduler Chains

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

Examples of rule conditions using SQL syntax for Oracle Scheduler chains. These conditions allow for more complex evaluations, including date comparisons and duration checks.

```SQL
':step1.completed = ''TRUE'' AND :step1.end_date >SYSDATE-1/24'
--satisfied when step step1 completed less than an hour ago

':step1.duration > interval ''5'' minute'
-- satisfied when step step1 has completed and took longer than 5 minutes to complete
```

--------------------------------

### Scheduler Chain Rule Conditions

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

Examples of rule conditions using Oracle Scheduler's chain condition syntax. These conditions evaluate the completion status or error codes of previous steps in a chain.

```SQL
'step1 completed'
-- satisfied when step step1 has completed. (step1 completed is also TRUE when any 
-- of the following are TRUE: step1 succeeded, step1 failed, step1 stopped.)

'step1 succeeded and step2 succeeded'
-- satisfied when steps step1 and step2 have both succeeded

'step1 error_code > 100'
-- satisfied when step step1 has failed with an error_code greater than 100

'step1 error_code IN (1, 3, 5, 7)'
-- satisfied when step step1 has failed with an error_code of 1, 3, 5, or 7
```

--------------------------------

### DBMS_SCHEDULER Set Position Specification Syntax

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

Specifies the position within a set of occurrences. For example, to run on the first or last day of a period. Supports numbers from 1 to 9999, with optional negative values.

```plaintext
bysetpos_clause = "BYSETPOS" "=" setpos_list
   setpos_list = setpos ("," setpos)*
   setpos = [minus] setpos_num
   setpos_num = 1 through 9999
```

--------------------------------

### Create Database Destination Procedure Syntax - PL/SQL

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

This PL/SQL code snippet defines the syntax for the CREATE_DATABASE_DESTINATION procedure. It outlines the required and optional parameters used to establish a connection to a remote Oracle AI Database for executing scheduled jobs.

```sql
DBMS_SCHEDULER.CREATE_DATABASE_DESTINATION (
   destination_name        IN VARCHAR2,
   agent                   IN VARCHAR2,
   tns_name                IN VARCHAR2,
   comments                IN VARCHAR2 DEFAULT NULL);

```

--------------------------------

### Define Schedule with BYSETPOS

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

This example shows how to define a recurring schedule using the BYSETPOS clause to specify specific occurrences within a frequency period. The Scheduler determines the set of dates from the beginning of the frequency period.

```sql
FREQ=MONTHLY;BYDAY=MON,TUE,FRI;BYSETPOS=1,3
```

--------------------------------

### Create Incompatibility Definition (PL/SQL)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

This PL/SQL snippet demonstrates how to use the CREATE_INCOMPATIBILITY procedure to define a new incompatibility rule. It specifies the names of the objects involved, the constraint level, and whether the incompatibility should be initially enabled. The `object_name` can be a comma-separated list of programs or jobs that cannot run simultaneously.

```sql
DECLARE
  v_incompatibility_name VARCHAR2(100) := 'MY_INCOMPATIBILITY';
  v_object_name          VARCHAR2(200) := 'PROGRAM1,PROGRAM2';
  v_constraint_level     VARCHAR2(20)  := 'PROGRAM_LEVEL';
  v_enabled              BOOLEAN       := TRUE;
BEGIN
  DBMS_SCHEDULER.CREATE_INCOMPATIBILITY(
    incompatibility_name => v_incompatibility_name,
    object_name          => v_object_name,
    constraint_level     => v_constraint_level,
    enabled              => v_enabled
  );
  DBMS_OUTPUT.PUT_LINE('Incompatibility ''' || v_incompatibility_name || ''' created.');
END;
```

--------------------------------

### Define Schedule with EXCLUDE Clause

Source: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_SCHEDULER.html/index

This example demonstrates how to exclude specific dates from a schedule using the EXCLUDE clause. Dates without a time component are treated as 24-hour periods, and all timestamps falling on these excluded dates are removed.

```sql
freq=monthly;bymonthday=15,30;byhour=8,13,18;byminute=0;bysecond=0;
     exclude=jan_fifteenth
```
Tokens:
Raw