The SUBMIT stored procedure submits a new job.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
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. You can query this ID from the JOB column of the USER_JOBS or DBA_JOBS view. |
| what | The PL text to run in the job. It must be a valid PL statement or code block. The SQL statement submitted in the what parameter is wrapped in the following PL 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 for running the job. |
| interval | The date function used to calculate the next date on which the job will be run. Default value: NULL. It must be a future point in time or NULL. |
| no_parse | The default value is FALSE. The value FALSE specifies to parse the stored procedure associated with the job. The value TRUE specifies to parse the stored procedure associated with the job only when the job is run for the first time. For example, if you want to submit a job before creating a table associated with the job, set this parameter to TRUE. |
| zone | The zone where the job runs, which can be specified when you submit the job. |
| force | Specifies whether to forcibly run the job. If this parameter is set to TRUE, the job is run forcibly; otherwise, the job is run only when it is idle. |
Considerations
Before a job is submitted, it cannot be handled by the job queue in the background.
The
zoneandforceparameters are added to the job queue so that you can specify whether the specified zone can run the submitted job.
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;/