Chapter 14. DBMS

Chapter 14. DBMS_JOB

Table of Contents

14.2.1. BROKEN
14.2.2. CHANGE
14.2.3. INTERVAL
14.2.4. NEXT_DATE
14.2.5. REMOVE
14.2.6. RUN
14.2.7. SUBMIT
14.2.8. WHAT

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

14.1. Overview

Tibero periodically checks the jobs updated in the database and executes the specified job at a scheduled time.

DBMS_JOB registers a job with a statement that can be used in PSM, and provides an operation to execute the job. Procedures in the DBMS_JOB package can be used to add a job to the database and run it at a specified time.

Key features of the DBMS_JOB package are as follows:

Does not require specific system privileges. The updated job can be executed or changed only by the owner of the job.
Automatically commits any job updates or changes as well as any operations executed by the job.
Checks jobs via the DBA_JOBS, ALL_JOBS, and USER_JOBS views.
Re-executes a failed job. After 16 failures, the job state is changed to broken.
Does not provide a function to stop a running job.

14.2. Procedures

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

14.2.1. BROKEN

Sets a saved job as normal or broken. If a broken job changes to normal state, its next execution time can be set.

Details about the BROKEN procedure are as follows:

Prototype

DBMS_JOB.BROKEN
(
    job         IN   BINARY_INTEGER,
    broken      IN   BOOLEAN,
    next_date   IN   DATE DEFAULT SYSDATE
);

Parameter

Description

job

Job number.

broken

TRUE: Broken
FALSE: Normal

next_date

If broken is set to TRUE, this parameter is ignored.

Otherwise, sets the next execution time.

Example

BEGIN
          /* Reset broken JOB to normal, and set it to run one hour later. */
          DBMS_JOB.BROKEN(100, false, sysdate + 1/24); END;
          /

14.2.2. CHANGE

Changes a field of a saved job.

Details about the CHANGE procedure are as follows:

Prototype

DBMS_JOB.CHANGE
(
    job           IN   BINARY_INTEGER,
    what          IN   VARCHAR2,
    next_date     IN   DATE,
    interval      IN   VARCHAR2,
    instance      IN   BINARY_INTEGER DEFAULT 0,
    force         IN   BOOLEAN DEFAULT FALSE
);

Parameter

Parameter	Description
job	Job number.
what	Sequence of the PL/SQL procedure or the PSM statement.
next_date	Next execution time of the job.
interval	Expression used to calculate the next job execution time. For further information, refer to “14.2.3. INTERVAL”.
instance	Instance to execute the job on. The default value is 0 (ANY_INSTANCE).
force	This parameter is currently not supported.

Example

BEGIN
          DBMS_JOB.CHANGE(100, null, null, 'sysdate + 1'); END;
          /

14.2.3. INTERVAL

Specifies a parameter that determines the execution interval of a job.

Details about the INTERVAL procedure are as follows:

Prototype

DBMS_JOB.INTERVAL
          ( job IN BINARY_INTEGER, interval IN VARCHAR2
          );

Parameter

Description

job

Job number.

interval

The expression used to update next_date, which is the next execution time of the job.

This parameter should be specified with NULL or a string of an expression that can be evaluated as a date type. The minimum available unit is minutes.

The string should be put in single quotation marks (' ').

This parameter is evaluated before the job is executed. If the job is successfully executed, and the result of the interval parameter is NULL, the job is deleted.

Examples:

Example 1)
```
'sysdate + 1'
```
Execute once per day.
Example 2)
```
'next_day(sysdate,''MONDAY'')'
```
Execute on every Monday.
Example 3)
```
'null'
```
Execute only once.

Example

BEGIN
          /* Runs the job once a day. */ DBMS_JOB.INTERVAL(100, 'sysdate +
          1'); END; /

14.2.4. NEXT_DATE

Specifies when a job is automatically executed. After the job is executed, the next execution time is updated according to the interval value.

Details about the NEXT_DATE procedure are as follows:

Prototype

DBMS_JOB.NEXT_DATE
          ( job IN BINARY_INTEGER, next_date IN DATE
          );

Parameter

Parameter Description
job Job number.
next_date Next job execution time.

Example

BEGIN
          /* Run the job 10 minutes later. */ DBMS_JOB.NEXT_DATE(100, sysdate
          + 10/24/60); END; /

14.2.5. REMOVE

Removes a job.

Details about the REMOVE procedure are as follows:

Prototype

DBMS_JOB.REMOVE
          ( job IN BINARY_INTEGER );

Parameter

Parameter Description
job Job number.

Example

BEGIN
          DBMS_JOB.REMOVE(100); END; /

14.2.6. RUN

Immediately executes a job in the current session. This procedure can also execute a broken job, and the job changes to normal state after it has been successfully executed.

Details about the RUN procedure are as follows:

Prototype

DBMS_JOB.RUN
(
    job      IN   BINARY_INTEGER,
    force    IN   BOOLEAN DEFAULT FALSE
);

Parameter

Parameter Description
job Job number.
force This parameter is currently not supported.

Example

BEGIN
          DBMS_JOB.RUN(100); END; /

14.2.7. SUBMIT

Adds a new job to the database.

Details about the SUBMIT procedure are as follows:

Prototype

DBMS_JOB.SUBMIT 
(
    job          OUT  BINARY_INTEGER,
    what         IN   VARCHAR2,
    next_date    IN   DATE DEFAULT sysdate,
    interval     IN   VARCHAR2 DEFAULT 'null',
    no_parse     IN   BOOLEAN DEFAULT FALSE,
    instance     IN   BINARY_INTEGER DEFAULT 0,
    force        IN   BOOLEAN DEFAULT FALSE
);

Parameter

Parameter	Description
job	Job number.
what	Sequence of the PL/SQL procedure or the PSM statement.
next_date	Next job execution time.
interval	Expression used to calculate the next job execution time. For more details, refer to “14.2.3. INTERVAL”.
no_parse	TRUE: Parses the job when it is executed rather than when it is submitted. The parsing success is notified when it is executed. FALSE: Parses procedures associated with the job before execution.
instance	Instance to execute the job on. The default value is 0 (ANY_INSTANCE).
force	This parameter is currently not supported.

Example

DECLARE
          job_no number; BEGIN
          DBMS_JOB.SUBMIT(job_no,'dbms_output.put_line(''ok'');', SYSDATE,
          'SYSDATE + 1'); END; /

14.2.8. WHAT

Specifies the operation to be executed by the job.

Details about the WHAT procedure are as follows:

Prototype

DBMS_JOB.WHAT
          ( job IN BINARY_INTEGER, WHAT IN VARCHAR2 );

Parameter

Description

job

Job number.

what

Sequence of the PL/SQL procedures or PSM statements. This must be specified with a string that is terminated with a semicolon (;).

Example:

Example 1
```
psm_proc(''abc'', 10);
```
Example 2
```
dbms_output.put_line(''ok'');
```

Example 3

declare x number; 
begin x := x + 1;
dbms_outout.put_line(x); 
end;

Example

BEGIN
          /* Replace the job number 100 with a call of the psm_proc procedure.
          */ DBMS_JOB.WHAT(100, 'psm_proc(''abc'', 10);'); END;
          /

Prev		Next
Chapter 13. DBMS_JAVA	Home	Chapter 15. DBMS_JOB_WITH_NAME