CREATE_JOB|V4.1.0| docs|Distributed Database

CREATE_JOB

Last Updated：2023-07-28 02:55:43 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

Use the following 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');

Use the following 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');

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 serial number generated in order. You can prefix a string to the number. As a result, the job name is a string with a serial number 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. You can specify only `STORED_PROCEDURE`, which indicates that the job is a procedural language (PL) or Java stored procedure or an external C subprogram. 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 (""). 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. If the program type is `EXECUTABLE`, the job owner must have the `CREATE EXTERNAL JOB` privilege to enable or run 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. For a recurring job whose repeat interval is specified by a calendar expression, the value of the `start_date` parameter is used as a reference date. The first running time of the job specified in the calendar expression must not be earlier than the current time. The Scheduler cannot ensure that the job is executed at the accurate scheduled time because resources may be unavailable due to system overload.
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. If you do not set the `repeat_interval` parameter, the job runs only once on the specified start date. For more information, see Calendar syntax.
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 value 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 this parameter 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. The credential must exist. This parameter is available only for remote database jobs, remote external jobs, local external jobs, and script jobs. 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. This attribute is available only for remote database jobs and remote external jobs. For a job running on a local database or a local external job (an executable file), the value must be `NULL`. The value can be a single destination name or the name of a group of the `EXTERNAL_DEST` or `DB_DEST` type. The specified single destination or group must already exist. If the `credential_name` parameter of `CREATE_JOB` is set to `NULL`, each destination must be preceded with a credential, in the format of `sql credential.destination`.

Considerations

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

To create a job in your own schema, you must have the CREATE JOB privilege. If you have the CREATE ANY JOB privilege, you can create programs in all schemas. If you are creating a job that resides in another schema, you must qualify it with the schema name. If the job type is EXECUTABLE, or the job points to a program of the EXECUTABLE type, the job owner must have the CREATE EXTERNAL JOB privilege to enable or run the job.

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. We recommend that you call the CREATE_JOBS procedure to create multiple jobs at a time, which improves the efficiency.