Chapter 15. DBMS_JOB_WITH_NAME

Table of Contents

15.1. Overview
15.2. Procedures
15.2.1. BROKEN
15.2.2. CHANGE
15.2.3. INTERVAL
15.2.4. NAME
15.2.5. NEXT_DATE
15.2.6. REMOVE
15.2.7. RUN
15.2.8. SUBMIT
15.2.9. WHAT

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

15.1. Overview

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

DBMS_JOB_WITH_NAME registers statements that can be used in PSM as a job, 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 execute it at the specified time. This package provides the same management functions as the DBMS_JOB package, but it uses the job name instead of the job ID.

Key features of the DBMS_JOB package are as follows:

  • Does not require DBA privilege. A 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.

15.2. Procedures

This section describes the procedures and functions provided by the DBMS_JOB_WITH_NAME package, in alphabetical order.

15.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_WITH_NAME.BROKEN
    (
        job         IN   VARCHAR2,
        broken      IN   BOOLEAN,
        next_date   IN   DATE DEFAULT SYSDATE
    ); 
  • Parameter

    ParameterDescription
    jobJob 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_WITH_NAME.BROKEN('job_name_here', false, sysdate + 1/24); 
    END; 
    /

15.2.2. CHANGE

Changes a field of a stored job.

Details about the CHANGE procedure are as follows:

  • Prototype

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

    ParameterDescription
    jobJob number.
    whatSequence of the PL/SQL procedure or the PSM statement.
    next_dateNext execution time of the job.
    interval

    Expression used to calculate the next job execution time.

    For further information, refer to “15.2.3. INTERVAL”.

    instanceInstance to execute the job on. Default value: 0 (ANY_INSTANCE).
    forceThis parameter is currently not supported.
  • Example

    BEGIN
        DBMS_JOB_WITH_NAME.CHANGE('job_name_here', null, null, 'sysdate + 1'); 
    END; 
    /

15.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_WITH_NAME.INTERVAL
    (
        job         IN   VARCHAR2,
        interval    IN   VARCHAR2
    );
  • Parameter

    ParameterDescription
    jobName of the job to execute.
    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_WITH_NAME.INTERVAL('job_name_here', 'sysdate + 1'); 
    END; 
    /

15.2.4. NAME

Changes the job name to the new_name parameter value.

Details about the NAME procedure are as follows:

  • Prototype

    DBMS_JOB_WITH_NAME.NAME
    (
        job         IN   VARCHAR2,
        new_name    IN   VARCHAR2
    );
  • Parameter

    ParameterDescription
    jobJob name.
    new_nameNew name of the job to execute.
  • Example

    BEGIN
        DBMS_JOB_WITH_NAME.NAME('job_name_here','new_name_here'); 
    END; 
    /

15.2.5. 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_WITH_NAME.NEXT_DATE
    (
        job         IN   VARCHAR2,
        next_date   IN   DATE
    );
  • Parameter

    ParameterDescription
    jobJob number.
    next_dateNext job execution time.
  • Example

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

15.2.6. REMOVE

Removes a job.

Details about the REMOVE procedure are as follows:

  • Prototype

    DBMS_JOB_WITH_NAME.REMOVE
    (
        job         IN   VARCHAR2
    );
  • Parameter

    ParameterDescription
    jobJob name.
  • Example

    BEGIN
        DBMS_JOB_WITH_NAME.REMOVE('job_name_here');
    END; 
    /

15.2.7. 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_WITH_NAME.RUN
    (
        job      IN   VARCHAR2,
        force    IN   BOOLEAN DEFAULT FALSE
    );
  • Parameter

    ParameterDescription
    jobJob number.
    forceThis parameter is currently not supported.
  • Example

    BEGIN
        DBMS_JOB_WITH_NAME.RUN('job_name_here'); 
    END; 
    /

15.2.8. SUBMIT

Adds a new job to the database.

Details about the SUBMIT procedure are as follows:

  • Prototype

    DBMS_JOB_WITH_NAME.SUBMIT 
    (
        job          OUT  BINARY_INTEGER,
        name         IN   VARCHAR2,
        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

    ParameterDescription
    jobJob number.
    whatSequence of the PL/SQL procedure or the PSM statement.
    next_dateNext job execution time.
    interval

    Expression used to calculate the next job execution time.

    For more details, refer to “15.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.

    instanceInstance to execute the job on. Default value: 0 (ANY_INSTANCE).
    forceThis parameter is currently not supported.
  • Example

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

15.2.9. WHAT

Specifies the operation to be executed by the job.

Details about the WHAT procedure are as follows:

  • Prototype

    DBMS_JOB_WITH_NAME.WHAT
    (
        job      IN   VARCHAR2,
        WHAT     IN   VARCHAR2
    );
  • Parameter

    ParameterDescription
    jobJob name.
    what

    Sequence of the PL/SQL procedure or the PSM statement. 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_WITH_NAME.WHAT('job_name_here', 'psm_proc(''abc'', 10);'); 
    END; 
    /