CREATE_JOB|V4.2.5| docs|Distributed Database

CREATE_JOB

Last Updated：2026-02-11 07:43:39 Updated

The CREATE_JOB procedure is used to create a job.

If the enabled attribute is set to TRUE, the Scheduler will automatically run the job according to its schedule. If the job is created in a disabled state, it will only run after being enabled using the SET_ATTRIBUTE procedure.

Applicability

This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.

Syntax

Syntax for creating a job without using an existing program or schedule

DBMS_SCHEDULER.CREATE_JOB (
job_name            IN  VARCHAR2,
job_type            IN  VARCHAR2,
job_action          IN  VARCHAR2,
number_of_argument  IN  BINARY_INTEGER DEFAULT 0,
start_date          IN  TIMESTAMP_UNCONSTRAINED DEFAULT NULL,
repeat_interval     IN  VARCHAR2 DEFAULT 'null',
end_date            IN  TIMESTAMP_UNCONSTRAINED 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',
max_run_duration    IN  BINARY_INTEGER DEFAULT 0);

Syntax for creating a job using a named program object and an internal schedule

DBMS_SCHEDULER.CREATE_JOB  (
job_name            IN  VARCHAR2,
program_name        IN  VARCHAR2,
start_date          IN  TIMESTAMP_UNCONSTRAINED DEFAULT NULL,
repeat_interval     IN  VARCHAR2 DEFAULT 'null',
end_date            IN  TIMESTAMP_UNCONSTRAINED 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',
job_style           IN  VARCHAR2 DEFAULT 'REGULER',
credential_name     IN  VARCHAR2 DEFAULT 'null',
destination_name    IN  VARCHAR2 DEFAULT 'null',
max_run_duration    IN  BINARY_INTEGER DEFAULT 0);

Parameters

Parameter	Description
job_name	The name of the job. This name must be unique in the SQL namespace. If the job belongs to another schema, you must qualify the name with the schema name. If you do not specify a job name, an error is returned. You can also use `GENERATE_JOB_NAME` to let the Scheduler generate a name. The generated name is a sequence number, which can be prefixed with a string. The resulting job name is then used in the `CREATE_JOB` procedure.
job_type	The type of the job to create. If not specified, an error is returned. The only supported value in the current version is `STORED_PROCEDURE`. This parameter supports only procedures and does not support functions with return values.
job_action	The action of the job. If you do not specify `job_action` for an inline procedure, an error is returned when the job is created. `job_action` is executed within autonomous transactions and adheres to all autonomous transaction guidelines and limitations. For stored procedures, `job_action` is the name of the stored procedure. If the procedure is in another schema, you must specify the schema. If case-sensitive names are required, enclose the schema and procedure names in double quotation marks. For example, `job_action_proc=>'"Schema"."myProc"'`. When the job or procedure type is `STORED_PROCEDURE`, `job_action` does not support PL procedures with `INOUT` or `OUT` parameters.
number_of_argument	The number of parameters required by the job. The valid range is [0,255], with a default value of 0.
program_name	The name of the program associated with the job.
start_date	The date and time when the job schedule first runs. If both `start_date` and `repeat_interval` are empty, the job runs immediately after it is enabled.
repeat_interval	The frequency at which the job repeats. You can specify the repeat interval using a calendar expression or a PL expression. The next run time of the job is determined by evaluating the specified expression. Note If this parameter is set to empty, an error is returned, but the job can still be used.
end_date	The expiration date and time of the job, after which it no longer runs. If `auto_drop` is `TRUE` and the job has reached its `end_date`, the job is automatically dropped. If `auto_drop` is `FALSE`, the job is disabled, and its `STATE` is set to `COMPLETED`. If you do not specify a value for `end_date`, the job will continue to run until it reaches the `max_runs` or `max_failures` threshold. `end_date` must be after `start_date`; otherwise, an error is returned. If `end_date` is the same as `start_date`, the job will not run, and no error is returned.
job_class	The class associated with the job.
enabled	Specifies whether to enable the created job. This attribute can be set to `TRUE` or `FALSE`, with a default value of `FALSE`, meaning the job is disabled upon creation. Disabled jobs are ignored by the Scheduler and are not processed. To process a disabled job, you can set this parameter to `TRUE` or use the `ENABLE` procedure.
auto_drop	Specifies whether to automatically drop the job after it completes or is disabled. This attribute can be set to `TRUE` or `FALSE`. If set to `TRUE`, the job is automatically dropped after completion or disablement. A job is considered completed if: The job's end date (or the end date of its schedule) has passed. The job has run `max_runs` times. You must use `SET_ATTRIBUTE` to set `max_runs`. The job is a one-time, non-repeating job. If the job fails `max_failures` times, it is disabled. `max_failures` is also set using `SET_ATTRIBUTE`. If `auto_drop` is set to `FALSE`, the job is not dropped and retains its metadata until explicitly dropped using the `DROP_JOB` procedure. By default, `auto_drop` is set to `TRUE` when a job is created.
comments	Comments for the job. By default, this attribute is `NULL`.
job_style	The style of the job being created. Possible values: `'REGULAR'`: Creates a regular job, which is the default. `'LIGHTWEIGHT'`: Creates a lightweight job. This value is only allowed if the job references a program object. Lightweight jobs are recommended for frequently running many short-term jobs. In some cases, lightweight jobs can offer a slight performance improvement. `'IN_MEMORY_RUNTIME'`: Creates an in-memory runtime job. These jobs are based on the lightweight job structure and further improve performance by maintaining an in-memory cache, thereby minimizing disk access for pre- and post-run operations. `'IN_MEMORY_FULL'`: Creates a fully in-memory job. Fully in-memory jobs require a program and cannot have a schedule or repeat interval. When enabled, they automatically run and are discarded after execution. They store all job information in memory without backing it up to disk, meaning the information is lost upon instance restart. Their purpose is to execute critical operations with minimal overhead.
credential_name	The default credentials used with the job. For local database jobs, this value must be `NULL`. For local external jobs, if this attribute is `NULL` (the default), the preferred (default) credentials are selected.
destination_name	The database or external destination for the job. If the `credential_name` parameter of `CREATE_JOB` is `NULL`, each destination must be prefixed with a credential, in the format `sql credential.destination`.
max_run_duration	The maximum duration for which the job can run.

Considerations

By default, jobs created by users are disabled and must be explicitly enabled to run according to their schedule.

To associate a job with a specific class or program, you must have the EXECUTE privilege on that class or program.

Not all job attributes can be set using CREATE_JOB. Some parameters must be set after the job is created. For example, parameters like job_priority and max_runs must be set using the SET_ATTRIBUTE procedure.

Examples

DECLARE
    i INTEGER;
BEGIN
    SELECT COUNT(*) INTO i FROM user_tables WHERE table_name = 'DBMS_SCHEDULER_T1';
    IF i = 0 THEN
        EXECUTE IMMEDIATE  'CREATE TABLE DBMS_SCHEDULER_T1(col DATE)';
    END IF;
    SELECT COUNT(*) INTO i FROM user_tables WHERE table_name = 'DBMS_SCHEDULER_T2';
    IF i = 0 THEN
        EXECUTE IMMEDIATE  'CREATE TABLE DBMS_SCHEDULER_T2(col DATE, job VARCHAR(128), job_name VARCHAR(128))';
    END IF;
END;
/

CREATE OR REPLACE PACKAGE JOB_UTILS IS
  PROCEDURE insert_date;
  PROCEDURE insert_date_program ( job IN VARCHAR2, job_name IN VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY JOB_UTILS IS
  PROCEDURE insert_date IS
  BEGIN
    INSERT INTO DBMS_SCHEDULER_T1 VALUES(TO_CHAR(sysdate));
    COMMIT;
  END;
  PROCEDURE insert_date_program ( job IN VARCHAR2, job_name IN VARCHAR2) IS
  BEGIN
    INSERT INTO DBMS_SCHEDULER_T2 VALUES(sysdate, job, job_name);
    COMMIT;
  END;
END;
/

 BEGIN
     DBMS_SCHEDULER.CREATE_JOB(
             job_name => dbms_scheduler.generate_job_name,
             job_type => 'STORED_PROCEDURE',
             job_action => 'JOB_UTILS.insert_date',
            number_of_argument  => 5,
            start_date  => sysdate ,
             repeat_interval => 'FREQ=SECONDLY; INTERVAL=8',
            end_date  =>sysdate + 100 ,
            job_class   =>'DEFAULT_JOB_CLASS',
            enabled => true,
            auto_drop => true,
            comments =>'test' ,
           credential_name => 'null',
            destination_name => 'null');
           COMMIT;
       END;
       /

--In a business tenant, run the following command to view the job information:
SELECT JOB_NAME,enabled,NEXT_RUN_DATE,state,last_start_date FROM DBA_SCHEDULER_JOBS;

The return result is as follows:

+---------------------------+---------+-------------------------------------+-----------+-------------------------------------+
| JOB_NAME                  | ENABLED | NEXT_RUN_DATE                       | STATE     | LAST_START_DATE                     |
+---------------------------+---------+-------------------------------------+-----------+-------------------------------------+
| FRIDAY_WINDOW             | 1       | 13-SEP-24 10.00.00.000000 PM +08:00 | NULL      | NULL                                |
| JOB$_1125899907865012     | 1       | 13-SEP-24 11.09.49.000000 AM +08:00 | SCHEDULED | 13-SEP-24 11.09.41.000965 AM +08:00 |
| MONDAY_WINDOW             | 1       | 16-SEP-24 10.00.00.000000 PM +08:00 | SCHEDULED | 09-SEP-24 10.00.00.020702 PM +08:00 |
| OPT_STATS_HISTORY_MANAGER | 1       | 14-SEP-24 10.50.02.365883 AM +08:00 | SCHEDULED | 13-SEP-24 10.50.02.366764 AM +08:00 |
| SATURDAY_WINDOW           | 1       | 14-SEP-24 06.00.00.000000 AM +08:00 | NULL      | NULL                                |
| SUNDAY_WINDOW             | 1       | 15-SEP-24 06.00.00.000000 AM +08:00 | NULL      | NULL                                |
| THURSDAY_WINDOW           | 1       | 19-SEP-24 10.00.00.000000 PM +08:00 | SCHEDULED | 12-SEP-24 10.00.00.012722 PM +08:00 |
| TUESDAY_WINDOW            | 1       | 17-SEP-24 10.00.00.000000 PM +08:00 | SCHEDULED | 10-SEP-24 10.00.00.012928 PM +08:00 |
| WEDNESDAY_WINDOW          | 1       | 18-SEP-24 10.00.00.000000 PM +08:00 | SCHEDULED | 11-SEP-24 10.00.00.015659 PM +08:00 |
+---------------------------+---------+-------------------------------------+-----------+-------------------------------------+
9 rows in set