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:
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:
|
| 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