Chapter 31. DBMS_SCHEDULER
Prev   Next

Chapter 31. DBMS_SCHEDULER

Table of Contents

31.1. Overview
31.2. Procedures
31.2.1. CREATE_CHAIN
31.2.2. CREATE_JOB
31.2.3. CREATE_PROGRAM
31.2.4. DEFINE_CHAIN_RULE
31.2.5. DEFINE_CHAIN_STEP
31.2.6. DISABLE
31.2.7. DROP_CHAIN
31.2.8. DROP_CHAIN_RULE
31.2.9. DROP_CHAIN_STEP
31.2.10. DROP_JOB
31.2.11. DROP_PROGRAM
31.2.12. ENABLE
31.2.13. RUN_JOB
31.2.14. SET_ATTRIBUTE
31.2.15. STOP_JOB

This chapter briefly introduces the DBMS_SCHEDULER package, and describes how to use the procedures of the package.

31.1. Overview

Tibero periodically checks jobs added to the database through DBMS_SCHEDULER, and executes a job when the user-scheduled time is reached or a condition is satisfied.

DBMS_SCHEDULER registers a statement that can be used in PSM as a job and provides operations to execute the job. The procedures in this package can be used to add a job to the database to execute immediately or according to the scheduled time and condition.

DBMS_SCHEDULER has the following characteristics:

  • The DBA privilege is not required to use DBMS_SCHEDULER. A registered job can only be executed or modified by its owner.

  • When a job is added or modified, it is committed automatically. The operations performed in the job are also committed automatically.

  • A job added to the database can be checked through the [DBA | ALL | USER]_SCHEDULER_JOBS view. The following views are also supported.

    [DBA | ALL | USER]_SCHEDULER_PROGRAMS              
[DBA | ALL | USER]_SCHEDULER_CHAINS
[DBA | ALL | USER]_SCHEDULER_RUNNING_JOBS
[DBA | ALL | USER]_SCHEDULER_STEPS
[DBA | ALL | USER]_SCHEDULER_RULES
[DBA | ALL | USER]_SCHEDULER_STEP_CTX
[DBA | ALL | USER]_SCHEDULER_RULE_CTX

  • A CRON expression used in UNIX series is used to set the schedule time.

  • A registered job is executed internally through the DBMS_JOB package.

  • The package does not provide a stop function for a running job.

31.2. Procedures

This section describes the procedures provided by the DBMS_SCHEDULER package, in alphabetical order.

31.2.1. CREATE_CHAIN

Creates a new job chain. Since a chain is initially in disabled state, it must be enabled explicitly using the ENABLED procedure before use.

Details about the CREATE_CHAIN procedure are as follows:

  • Prototype

    DBMS_SCHEDULER.CREATE_CHAIN
(
    chain_name          IN    VARCHAR2,
    rule_set_name       IN    VARCHAR2 DEFAULT NULL,
    evaluation_interval IN    INTERVAL DAY TO SECOND DEFAULT NULL,
    comments            IN    VARCHAR2 DEFAULT NULL
);

  • Parameter

    ParameterDescription
    chain_nameName of the chain to create. Cannot be a duplicate object name.
    rule_set_nameIn general, this parameter does not need to be set explicitly. It is added or removed using DEFINE_CHAIN_RULE and DROP_CHAIN_RULE procedures. Currently unused.
    evaluation_intervalIf set to NULL, the rule is re-evaluated when a job starts or a step completes. Currently, only NULL is supported.
    commentsAdditional description including the purpose of the chain, etc.

  • Example

    BEGIN
    /* Create a chain named chain_name. */
    DBMS_SCHEDULER.CREATE_CHAIN(chain_name          => 'chain_name',
                                rule_set_name       => NULL,
                                evaluation_interval => NULL,
                                comments            => 'my first job chain');
END; 
/

31.2.2. CREATE_JOB

Creates a new job. If the 'enabled' parameter is set to TRUE, a newly created job is automatically executed by the scheduler according to the set schedule. If the parameter is set to FALSE, the job is not executed until it is enabled explicitly using the SET_ATTRIBUTE procedure.

Details about the CREATE_JOB procedure are as follows:

  • Prototype

    DBMS_SCHEDULER.CREATE_JOB
(
    job_name            IN VARCHAR2,                                                                             
    job_type            IN VARCHAR2,                                                                             
    job_action          IN VARCHAR2,                                                                           
    program_name        IN VARCHAR2 DEFAULT NULL,                                                            
    schedule_name       IN VARCHAR2 DEFAULT NULL,                                                           
    number_of_arguments IN BINARY_INTEGER DEFAULT 0,                                                  
    start_date          IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,                                              
    repeat_interval     IN VARCHAR2 DEFAULT NULL,                                                         
    event_condition     IN VARCHAR2 DEFAULT NULL,                                                         
    queue_spec          IN VARCHAR2 DEFAULT NULL,                                                              
    end_date            IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,                                                
    job_class           IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS',                                                
    enabled             IN BOOLEAN DEFAULT FALSE,                                                                 
    auto_drop           IN BOOLEAN DEFAULT TRUE,                                                                
    comments            IN VARCHAR2 DEFAULT NULL,                                                                
    credential_name     IN VARCHAR2 DEFAULT NULL,                                                         
    destination_name    IN VARCHAR2 DEFAULT NULL
);

  • Parameter

    ParameterDescription
    job_name

    Name of the job to create. Cannot be a duplicate object name.

    job_type

    Type of a job to create.

    Options are:

    • PSM_BLOCK: job that creates PSM code.

    • CHAIN: chain job.

    Since a parameter cannot be passed to the job, the number_of_arguments parameter must be set to 0.

    job_action

    Action performed by the job.

    Options according to the job type:

    • PSM_BLOCK: PSM code. The code must be terminated with a semicolon.

      Example:

      BEGIN my_proc(); 
END;

      Or

      DECLARE arg pls_integer:= 10; 
BEGIN my_proc2(arg); 
END;

    • CHAIN: Name of the chain to execute when the job type is CHAIN.

    program_nameName of the program that the job executes. This parameter is available only when the job type is CHAIN.
    schedule_nameUnsupported. This parameter is ignored.
    number_of_argumentsUnsupported. This parameter is ignored.
    start_date

    Job start date and time.

    If set to NULL, the current time is used. The start time may be delayed according to the system status. An expression specified in the repeat_interval parameter refers to this parameter value to determine the next execution schedule.

    repeat_interval

    Interval to execute the job. If set to NULL, the job executes only once.

    Can be set using a CRON expression. For example, if set to '0-59 * * * * *', the job executes every second. The expression is composed in the order of second (0-59), minute (0-59), hour (0-23), day (1-31), month (1-12), and week (0-7).

    Supported special characters are:

    • Hyphen (-): specifies a range. For example, '0-59' means values between 0 and 59 inclusive.

    • Asterisk (*): specifies all available values. For example, '*' in the second field is the same as '0-59'.

    • Comma (,): specifies multiple values as a list. For example, '1, 4, 7, 10' in the month field means the months of January, April, July, and October.

    • Slash (/): specifies an interval. For example, '1-12/3' is the same as '1, 4, 7, 10'. Note that '1/3' is the same as '1' because the range only includes '1'. If an asterisk (*) and a slash (/) are used together, the slash is ignored.

    event_conditionUnsupported. This parameter is ignored.
    queue_specUnsupported. This parameter is ignored.
    end_dateUnsupported. This parameter is ignored.
    job_classUnsupported. This parameter is ignored.
    enabled

    Enables or disables the job when it is created. Set to TRUE or FALSE (default value).

    This value can be changed using the ENABLE and DISABLE procedures.

    auto_dropUnsupported. This parameter is ignored.
    commentsComments about the job.
    credential_nameUnsupported. This parameter is ignored.
    destination_nameUnsupported. This parameter is ignored.

  • Example

    BEGIN
DBMS_SCHEDULER.CREATE_JOB (
      job_name        => 'job_name',
      job_type        => 'PSM_BLOCK',
      job_action      => 'begin my_proc; end;',
      start_date      => SYSTIMESTAMP,
      repeat_interval => '30 0 1-3 * * *',
      enabled         => TRUE,
      comments        => 'Job defined entirely by the CREATE JOB procedure.');
END;
/

31.2.3. CREATE_PROGRAM

Creates a program. Currently, this procedure is only used when creating a job chain.

Details about the CREATE_PROGRAM procedure are as follows:

  • Prototype

    DBMS_SCHEDULER.INTERVAL
(
    program_name             IN VARCHAR2,
    program_type             IN VARCHAR2,
    program_action           IN VARCHAR2,
    number_of_arguments      IN PLS_INTEGER DEFAULT 0,
    enabled                  IN BOOLEAN DEFAULT FALSE,
    comments                 IN VARCHAR2 DEFAULT NULL);
);

  • Parameter

    ParameterDescription
    program_nameName of a program to create.
    program_typeType of a program to create. Currently, only 'PSM_BLOCK' is supported.
    program_action

    Action performed by the program.

    Options according to the program type:

    • PSM_BLOCK: PSM code. The code must be terminated with a semicolon.

      Example:

      BEGIN my_proc(); 
END;  
      DECLARE arg pls_integer:= 10; 
BEGIN my_proc2(arg); 
END;
    number_of_argumentsUnsupported. This parameter is ignored.
    enabled

    Enables or disables the program when it is created. Set to TRUE or FALSE (default value).

    This value can be changed using the ENABLE and DISABLE procedures.

    commentsComments about the program.

  • Example

    BEGIN
    DBMS_SCHEDULER.CREATE_PROGRAM(program_name   => 'program_name',
                                  program_type   => 'PSM_BLOCK',
                                  program_action => 'my_job;');
END; 
/

31.2.4. DEFINE_CHAIN_RULE

Adds a new rule to an existing chain using a condition-action pair parameter. The condition parameter uses SQL WHERE clause syntax, and the action parameter is specified with a list of comma delimited steps to execute with the START and END commands. An action is performed if its condition is satisfied during a job chain execution.

Details about the DEFINE_CHAIN_RULE procedure are as follows:

  • Prototype

    DBMS_SCHEDULER.DEFINE_CHAIN_RULE
(
    chain_name              IN VARCHAR2,
    condition               IN VARCHAR2,
    action                  IN VARCHAR2,
    rule_name               IN VARCHAR2 DEFAULT NULL,
    comments                IN VARCHAR2 DEFAULT NULL
);

  • Parameter

    ParameterDescription
    chain_nameName of a chain to apply the rule to.
    condition

    Conditional statement, which is set for a step attribute, that will be used to configure the job chain.

    Use a boolean expression. Action is performed only when it evaluates to TRUE. Every chain must have a rule that evaluates to TRUE to start the chain. For example, since this parameter supports SQL WHERE clause syntax, specify an expression like '1=1'.

    To express a condition with SQL, a select statement where clause syntax must be used. A chain step attribute can be referenced by using the chain step name as a bind variable (syntax: step_name.attribute).

    The state attribute can be 'STARTED', 'RUNNING', 'SUCCEEDED', or 'FAILED'. If the state attribute is in one of these states, the completed attribute is 'TRUE'. Otherwise, it is 'FALSE'.

    action

    Action performed when the rule evaluates to TRUE.

    The following syntax is supported:

    •  START step_1[, step_2 ..]

      If the condition is true, the steps in the list are executed.

    •  END

      If the condition is true, the chain terminates.

    rule_nameName of the rule to create.
    commentsComments about the rule.

  • Example

    BEGIN
    /* Create a rule to start the chain. */
    DBMS_SCHEDULER.DEFINE_CHAIN_RULE('chain_name',
                                     '1=1',
                                     'start step1',
                                     'rule1');

    /* When step3 is complete, end the chain. */
    DBMS_SCHEDULER.DEFINE_CHAIN_RULE('chain_name',
                                     'step3.completed =''TRUE''',
                                     'end',
                                     'rule2');

    /* If step1 is completed successfully, start step3. */ 
    DBMS_SCHEDULER.DEFINE_CHAIN_RULE('chain_name',
                                     'step1.state=''SUCCEEDED''',
                                     'start step3',
                                     'rule3');
END; 
/

31.2.5. DEFINE_CHAIN_STEP

Specifies a step for a chain.

Details about the DEFINE_CHAIN_STEP procedure are as follows:

  • Prototype

    DBMS_SCHEDULER.DEFINE_CHAIN_STEP
(
    chain_name              IN VARCHAR2,
    step_name               IN VARCHAR2,
    program_name            IN VARCHAR2
);

  • Parameter

    ParameterDescription
    chain_nameName of the step's chain.
    step_nameStep name.
    program_nameName of the program to execute during the step.

  • Example

    BEGIN
    DBMS_SCHEDULER.DEFINE_CHAIN_STEP('chain_name', 'step1', 'program_name');
END; 
/

31.2.6. DISABLE

Disables a program, job, or chain. This procedure sets an object's enabled attribute to 'FALSE'.

Details about the DISABLE procedure are as follows:

  • Prototype

    DBMS_SCHEDULER.DISABLE
(
    name              IN VARCHAR2,
    force             IN BOOLEAN DEFAULT FALSE,
    commit_semantics  IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR'
);

  • Parameter

    ParameterDescription
    nameObject name of the attribute to change.
    forceUnsupported. This parameter is ignored.
    commit_semanticsUnsupported. This parameter is ignored.

  • Example

    BEGIN
    DBMS_SCHEDULER.DISABLE('my_job'); 
END; 
/

31.2.7. DROP_CHAIN

Drops a chain from the database.

Details about the DROP_CHAIN procedure are as follows:

  • Prototype

    DBMS_SCHEDULER.DROP_CHAIN 
(
    chain_name              IN VARCHAR2,
    force                   IN BOOLEAN DEFAULT FALSE
);

  • Parameter

    ParameterDescription
    chain_nameName of the chain to drop.
    forceUnsupported. This parameter is ignored.

  • Example

    BEGIN
    DBMS_SCHEDULER.DROP_CHAIN('my_chain'); 
END; 
/

31.2.8. DROP_CHAIN_RULE

Drops a rule from a chain in the database.

Details about the DROP_CHAIN_RULE procedure are as follows:

  • Prototype

    DBMS_SCHEDULER.DROP_CHAIN_RULE 
(
    chain_name              IN VARCHAR2,
    rule_name               IN VARCHAR2,
    force                   IN BOOLEAN DEFAULT FALSE
);

  • Parameter

    ParameterDescription
    chain_nameChain name of the rule to drop.
    rule_nameName of the rule to drop.
    forceUnsupported. This parameter is ignored.

  • Example

    BEGIN
    DBMS_SCHEDULER.DROP_CHAIN_RULE('my_chain', 'my_rule1'); 
END; 
/

31.2.9. DROP_CHAIN_STEP

Drops a step from a chain in the database.

Details about the DROP_CHAIN_STEP procedure are as follows:

  • Prototype

    DBMS_SCHEDULER.DROP_CHAIN_STEP 
(
    chain_name              IN VARCHAR2,
    step_name               IN VARCHAR2,
    force                   IN BOOLEAN DEFAULT FALSE
);

  • Parameter

    ParameterDescription
    chain_nameChain name of the step to drop..
    step_nameName of the step to drop.
    forceUnsupported. This parameter is ignored.

  • Example

    BEGIN
    DBMS_SCHEDULER.DROP_CHAIN_STEP('my_chain', 'my_step1'); 
END; 
/

31.2.10. DROP_JOB

Drops a job from the database.

Details about the DROP_JOB procedure are as follows:

  • Prototype

    DBMS_SCHEDULER.DROP_JOB 
(
    job_name                IN VARCHAR2,
    force                   IN BOOLEAN DEFAULT FALSE,
    defer                   IN BOOLEAN DEFAULT FALSE,
    commit_semantics        IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR'
);

  • Parameter

    ParameterDescription
    job_nameName of the job to drop.
    forceUnsupported. This parameter is ignored.
    deferUnsupported. This parameter is ignored.
    commit_semanticsUnsupported. This parameter is ignored.

  • Example

    BEGIN
    DBMS_SCHEDULER.DROP_JOB('my_job'); 
END; 
/

31.2.11. DROP_PROGRAM

Drops a program from the database.

Details about the DROP_PROGRAM procedure are as follows:

  • Prototype

    DBMS_SCHEDULER.DROP_PROGRAM 
(
    program_name            IN VARCHAR2,
    force                   IN BOOLEAN DEFAULT FALSE
);

  • Parameter

    ParameterDescription
    program_nameName of the program to drop.
    forceUnsupported. This parameter is ignored.

  • Example

    BEGIN
    DBMS_SCHEDULER.DROP_PROGRAM('my_program'); 
END; 
/

31.2.12. ENABLE

Enables a program, job, or chain. This procedure sets an object's enabled attribute to 'TRUE'.

Details about the ENABLE procedure are as follows:

  • Prototype

    DBMS_SCHEDULER.ENABLE
(
    name              IN VARCHAR2,
    commit_semantics  IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR'
);

  • Parameter

    ParameterDescription
    nameObject name of the attribute to change.
    commit_semanticsUnsupported. This parameter is ignored.

  • Example

    BEGIN
    DBMS_SCHEDULER.ENABLE('my_job'); 
END; 
/

31.2.13. RUN_JOB

Executes a job manually. A job is generally executed automatically by the scheduler when it is in the enabled state.

Details about the RUN_JOB procedure are as follows:

  • Prototype

    DBMS_SCHEDULER.RUN_JOB
(
    job_name                IN VARCHAR2,
    use_current_session     IN BOOLEAN DEFAULT TRUE
);

  • Parameter

    ParameterDescription
    job_nameName of the job to execute.
    use_current_sessionUnsupported. This parameter is ignored. A job is always executed in the current session.

  • Example

    BEGIN
    DBMS_SCHEDULER.RUN_JOB('my_job'); 
END; 
/

31.2.14. SET_ATTRIBUTE

Changes an attribute of a job, program, or chain object.

Details about the SET_ATTRIBUTE procedure are as follows:

  • Prototype

    DBMS_SCHEDULER.SET_ATTRIBUTE
(
    name           IN VARCHAR2,
    attribute      IN VARCHAR2,
    value          IN {BINARY_INTEGER|VARCHAR2}
);

  • Parameter

    ParameterDescription
    nameObject name of the attribute to change.
    attribute

    Attribute to change.

    Supported attributes are:

    • job_action

    • repeat_interval

    • instance_id (instance_id is the TAC instance that will execute the JOB, 0 means any_instance)

    • program_action

    valueNew attribute value.

  • Example

    BEGIN
    /* Set instance_id to 0 (any TAC node instance can execute the job). */
    DBMS_SCHEDULER.SET_ATTRIBUTE('my_job', 'INSTANCE_ID', 0); 
END; 
/

31.2.15. STOP_JOB

Stops a job running in the database.

Details about the STOP_JOB procedure are as follows:

  • Prototype

    DBMS_SCHEDULER.STOP_JOB 
(
    job_name                IN VARCHAR2,
    force                   IN BOOLEAN DEFAULT FALSE,
);

  • Parameter

    ParameterDescription
    job_nameName of the job to stop.
    forceUnsupported. This parameter is ignored.

  • Example

    BEGIN
    DBMS_SCHEDULER.STOP_JOB('my_job');
END; 
/

Prev   Next
Chapter 30. DBMS_ROWID Home Chapter 32. DBMS_SESSION