CREATE_JOB|V4.3.1| docs|Distributed Database

CREATE_JOB

Last Updated：2024-09-13 03:32:17 Updated

The CREATE_JOB procedure creates a job.

If you set the enabled attribute to TRUE, the Scheduler automatically runs the job based on its schedule. If the created job is disabled, it runs only after you call the SET_ATTRIBUTE procedure to enable it.

Applicability

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

Syntax

Syntax to create a job without calling 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 to create a job by 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. The name must be unique in the SQL namespace. If you are creating a job that belongs to another schema, you must qualify it with the schema name. If you do not specify the job name, an error is returned. You can also call the `GENERATE_JOB_NAME` procedure to use the Scheduler to generate a name, which is a number generated from a sequence. You can prefix a string to the number. The job name will then be a string appended with a number from a sequence and can be used in the `CREATE_JOB` procedure.
job_type	The type of the job. If you do not specify the job type, an error is returned. In the current version, only `STORED_PROCEDURE` is supported. This parameter supports only procedures. Functions that return values are not supported.
job_action	The operation to be performed by the job. If you do not set the `job_action` parameter for the program that is associated with the job, an error is returned. The operations specified by the `job_action` parameter are performed within an autonomous transaction and follow all rules and limits that apply to the autonomous transaction. For a stored procedure, the `job_action` parameter indicates the name of the stored procedures. If the procedure is located in a schema that does not belong to the job, you must specify the schema name. If case sensitivity is required, enclose the schema name and the stored procedure name in double quotation marks (" "), for example, `job_action_proc=>'"Schema"."myProc "'`. If you set a job or program to the `STORED_PROCEDURE` type, the `job_action` parameter does not support PL procedures that contain the `INOUT` or `OUT` parameter.
number_of_argument	The number of arguments that the job requires. Value range: [0,255]. Default value: 0.
program_name	The name of the program that is associated with the job.
start_date	The timestamp that specifies the date and time when the job runs for the first time. If the `start_date` and `repeat_interval` parameters are empty, the job runs immediately after being enabled.
repeat_interval	The interval at which the job recurs. You can specify the interval by using a calendar expression or a PL expression. The time of the next job running is determined by the calculation results of the specified expression. Note: If you do not specify this parameter, an error is reported. However, you can still use the feature normally.
end_date	The timestamp that consists of the date and time after which the job expires and no longer runs. If the `auto_drop` attribute is set to `TRUE`, the job is dropped after the time specified in `end_date`. If the `auto_drop` attribute is set to `FALSE`, the job is disabled, and the status of the job is set to `COMPLETED`. If you do not set the `end_date` attribute, the job is repeatedly executed. In this case, if you set the `max_runs` or `max_failures` attribute, the job stops running when either of the values is reached. The time specified in the `end_date` attribute must be later than that in the `start_date` attribute. Otherwise, an error is returned. If the values of the `end_date` and `start_date` attributes are the same, the job is not executed, and no error is returned.
job_class	The class to which the job belongs.
enabled	Specifies whether to enable the created job. Valid values: `TRUE` and `FALSE`. The default value is `FALSE`, which indicates that the created job is disabled. The Scheduler can obtain the metadata of a disabled job, which is also a database object, but will ignore the job and will not process it. To process the job, you can set this parameter to `TRUE` or call the `ENABLE` procedure to enable the job.
auto_drop	Specifies whether the job is automatically dropped or disabled after it is completed. Valid values: `TRUE` and `FALSE`. If you set it to `TRUE`, the job is automatically dropped or disabled after it is completed. A job is considered completed if one of the following conditions is met: The end date of the job or the end date of its schedule has passed. The job has run `max_runs` times. You must call the `SET_ATTRIBUTE` procedure to set the `max_runs` attribute. The job is one-time and has run once. The job is automatically disabled if its execution fails `max_failures` times. You must call the `SET_ATTRIBUTE` procedure to set the `max_failures` attribute. If you set the `auto_drop` attribute to `FALSE`, the job is not dropped, and its metadata is retained. You can call the `DROP_JOB` procedure to explicitly drop the job. By default, the `auto_drop` attribute is set to `TRUE` when you create a job.
comments	The comments about the job. The default value is `NULL`.
job_style	The style of the job to be created. Valid values: `'REGULAR'`: creates a regular job. This is the default value. `'LIGHTWEIGHT'`: creates a lightweight job. You can set the parameter to this value only if the job references a program object. We recommend that you use lightweight jobs when you need to frequently run many short-term jobs. In some cases, you can use lightweight jobs to slightly improve the performance. `'IN_MEMORY_RUNTIME'`: creates an in-memory runtime job. Jobs of this type are based on a lightweight job structure. They can retain in-memory cache to further improve performance. Therefore, they can minimize disk access operations before and after job running. `'IN_MEMORY_FULL'`: creates an in-memory full job. In-memory full jobs require a program and cannot have a schedule or repeat interval. They automatically run after being enabled and is discarded after the running. In-memory full jobs store all job information in memory and are not backed up on disks. As a result, the information is lost when the instance restarts. They are designed to run the operations that must be immediately performed with minimum overhead.
credential_name	The default credential to be used with the job. For a local database job, the value must be `NULL`. For a local external job, if the default value `NULL` is used, the default credential is selected.
destination_name	The destination database or external destination of the job. If the `credential_name` argument of the `CREATE_JOB` procedure is `NULL`, each destination must be preceded by a credential, in the format of `sql credential.destination`.
max_run_duration	The maximum run time of the job.

Considerations

By default, jobs created by users are disabled. They must be explicitly enabled before they can run as scheduled.

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

Not all attributes of a job can be set by calling the CREATE_JOB procedure. Some parameters must be specified after the job is created. For example, you can set the job_priority and max_runs parameters only by calling 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;
       /

-- View the running status in a business tenant:
SELECT JOB_NAME,enabled,NEXT_RUN_DATE,state,last_start_date FROM DBA_SCHEDULER_JOBS;

The 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