The SUBMIT stored procedure is used to submit a new job.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support this feature.
Syntax
DBMS_JOB.SUBMIT (
job OUT BINARY_INTEGER,
what IN VARCHAR2,
next_date IN DATE DEFAULT SYSDATE,
interval IN VARCHAR2 DEFAULT 'null',
no_parse IN BOOLEAN DEFAULT FALSE,
zone IN VARCHAR2 DEFAULT NULL,
force IN BOOLEAN DEFAULT FALSE);
Parameters
| Parameter | Description |
|---|---|
| job | The ID of the new job. To find this ID, query the JOB column of the USER_JOBS or DBA_JOBS view. |
| what | The PL/SQL text to be executed by the job. It must be a valid PL/SQL statement or code block. The SQL statement submitted in the what parameter is enclosed in the following PL/SQL block: DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN WHAT :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; |
| next_date | The next date on which the job will run. |
| interval | The function to calculate the next date on which the job will run. The default value is NULL. It must be a future date or NULL. |
| no_parse | The default value is FALSE. If set to FALSE, the stored procedure associated with the job will be parsed. If set to TRUE, the stored procedure associated with the job will be parsed when the job is first run. For example, if you want to submit the job before creating the table associated with the job, set this parameter to TRUE. |
| zone | When submitting the job, you can specify which zone can run the job. |
| force | If set to TRUE, the job will be forcibly run. Otherwise, the job will only be run if it is not already running. |
Considerations
Before submitting, the job will not be processed by the background job queue.
The job queue has been associated with the
zoneandforceparameters, allowing users to specify whether the submitted job can run in the specified zone.
Examples
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET nls_timestamp_format ='YYYY-MM-DD HH24:MI:SS.FF';
DECLARE
job01 BINARY_INTEGER;
BEGIN
DBMS_JOB.SUBMIT(job01 ,'dbms_stats.gather_database_stats_job_proc();', sysdate,'sysdate+1');
COMMIT;
END;/