DBMS_JOB¶

Related Interfaces¶

Table 1 lists all interfaces supported by the DBMS_JOB package.

**Table 1** DBMS_JOB¶
Interface	Description
DBMS_JOB.SUBMIT	Submits a job to the job queue. The job number is automatically generated by the system.
DBMS_JOB.ISUBMIT	Submits a job to the job queue. The job number is specified by the user.
DBMS_JOB.REMOVE	Removes a job from the job queue by job number.
DBMS_JOB.BROKEN	Disables or enables job execution.
DBMS_JOB.CHANGE	Modifies user-definable attributes of a job, including the job description, next execution time, and execution interval.
DBMS_JOB.WHAT	Modifies the job description of a job.
DBMS_JOB.NEXT_DATE	Modifies the next execution time of a job.
DBMS_JOB.INTERVAL	Modifies the execution interval of a job.
DBMS_JOB.CHANGE_OWNER	Modifies the owner of a job.

DBMS_JOB.SUBMIT

The stored procedure SUBMIT submits a job provided by the system.

A prototype of the DBMS_JOB.SUBMIT function is as follows:

DMBS_JOB.SUBMIT(
what         IN   TEXT,
next_date    IN   TIMESTAMP DEFAULT sysdate,
job_interval IN   TEXT  DEFAULT 'null',
job          OUT  INTEGER);

Note

When a job is created (using DBMS_JOB), the system binds the current database and the username to the job by default. This function can be invoked by using call or select. If you invoke this function by using select, there is no need to specify output parameters. To invoke this function within a stored procedure, use perform.

**Table 2** DBMS_JOB.SUBMIT interface parameters¶
Parameter	Type	Input/Output Parameter	Can Be Empty	Description
what	text	IN	No	SQL statement to be executed. One or multiple DMLs, anonymous blocks, and SQL statements that invoke stored procedures, or all three combined are supported.
next_date	timestamp	IN	No	Specifies the next time the job will be executed. The default value is the current system time (sysdate). If the specified time has past, the job is executed at the time it is submitted.
interval	text	IN	Yes	Calculates the next time to execute the job. It can be an interval expression, or sysdate followed by a numeric value, for example, sysdate+1.0/24. If this parameter is left blank or set to null, the job will be executed only once, and the job status will change to 'd' afterward.
job	integer	OUT	No	Specifies the job number. The value ranges from 1 to 32767. When dbms.submit is invoked using select, this parameter can be skipped.

For example:

select DBMS_JOB.SUBMIT('call pro_xxx();', to_date('20180101','yyyymmdd'),'sysdate+1');

select DBMS_JOB.SUBMIT('call pro_xxx();', to_date('20180101','yyyymmdd'),'sysdate+1.0/24');

CALL DBMS_JOB.SUBMIT('INSERT INTO T_JOB  VALUES(1);  call pro_1(); call pro_2();', add_months(to_date('201701','yyyymm'),1), 'date_trunc(''day'',SYSDATE) + 1 +(8*60+30.0)/(24*60)' ,:jobid);

DBMS_JOB.ISUBMIT
ISUBMIT has the same syntax function as SUBMIT, but the first parameter of ISUBMIT is an input parameter, that is, a specified job number. In contrast, that last parameter of SUBMIT is an output parameter, indicating the job number automatically generated by the system.
For example:
```
CALL dbms_job.isubmit(101, 'insert_msg_statistic1;', sysdate, 'sysdate+3.0/24');
```
DBMS_JOB.REMOVE
The stored procedure REMOVE deletes a specified job.
A prototype of the DBMS_JOB.REMOVE function is as follows:
```
REMOVE(job  IN  INTEGER);
```
Table 3 DBMS_JOB.REMOVE interface parameters¶
Parameter
Type
Input/Output Parameter
Can Be Empty
Description
job
integer
IN
No
Specifies the job number.
For example:
```
CALL dbms_job.remove(101);
```

DBMS_JOB.BROKEN

The stored procedure BROKEN sets the broken flag of a job.

A prototype of the DBMS_JOB.BROKEN function is as follows:

DMBS_JOB.BROKEN(
job          IN   INTEGER,
broken       IN   BOOLEAN,
next_date    IN   TIMESTAMP  DEFAULT  sysdate);

**Table 4** DBMS_JOB.BROKEN interface parameters¶
Parameter	Type	Input/Output Parameter	Can Be Empty	Description
job	integer	IN	No	Specifies the job number.
broken	boolean	IN	No	Specifies the status flag, true for broken and false for not broken. Setting this parameter to true or false updates the current job. If the parameter is left blank, the job status remains unchanged.
next_date	timestamp	IN	Yes	Specifies the next execution time. The default is the current system time. If broken is set to true, next_date is updated to '4000-1-1'. If broken is false and next_date is not empty, next_date is updated for the job. If next_date is empty, it will not be updated. This parameter can be omitted, and its default value will be used in this case.

For example:

CALL dbms_job.broken(101, true);
CALL dbms_job.broken(101, false, sysdate);

DBMS_JOB.CHANGE

The stored procedure CHANGE modifies user-definable attributes of a job, including the job content, next-execution time, and execution interval.

A prototype of the DBMS_JOB.CHANGE function is as follows:

DMBS_JOB.CHANGE(
job          IN   INTEGER,
what         IN   TEXT,
next_date    IN   TIMESTAMP,
interval     IN   TEXT);

**Table 5** DBMS_JOB.CHANGE interface parameters¶
Parameter	Type	Input/Output Parameter	Can Be Empty	Description
job	integer	IN	No	Specifies the job number.
what	text	IN	Yes	Specifies the name of the stored procedure or SQL statement block that is executed. If this parameter is left blank, the system does not update the what parameter for the specified job. Otherwise, the system updates the what parameter for the specified job.
next_date	timestamp	IN	Yes	Specifies the next execution time. If this parameter is left blank, the system does not update the next_date parameter for the specified job. Otherwise, the system updates the next_date parameter for the specified job.
interval	text	IN	Yes	Specifies the time expression for calculating the next time the job will be executed. If this parameter is left blank, the system does not update the interval parameter for the specified job. Otherwise, the system updates the interval parameter for the specified job after necessary validity check. If this parameter is set to null, the job will be executed only once, and the job status will change to 'd' afterward.

For example:

CALL dbms_job.change(101, 'call userproc();', sysdate, 'sysdate + 1.0/1440');
CALL dbms_job.change(101, 'insert into tbl_a values(sysdate);', sysdate, 'sysdate + 1.0/1440');

DBMS_JOB.WHAT

The stored procedure WHAT modifies the procedures to be executed by a specified job.

A prototype of the DBMS_JOB.WHAT function is as follows:

DMBS_JOB.WHAT(
job             IN     INTEGER,
what            IN     TEXT);

**Table 6** DBMS_JOB.WHAT interface parameters¶
Parameter	Type	Input/Output Parameter	Can Be Empty	Description
job	integer	IN	No	Specifies the job number.
what	text	IN	No	Specifies the name of the stored procedure or SQL statement block that is executed.

Note

If the value specified by the what parameter is one or multiple executable SQL statements, program blocks, or stored procedures, this procedure can be executed successfully; otherwise, it will fail to be executed.
If the what parameter is a simple statement such as insert and update, a schema name must be added in front of the table name.

For example:

CALL dbms_job.what(101, 'call userproc();');
CALL dbms_job.what(101, 'insert into tbl_a values(sysdate);');

DBMS_JOB.NEXT_DATE

The stored procedure NEXT_DATE modifies the next-execution time attribute of a job.

A prototype of the DBMS_JOB.NEXT_DATE function is as follows:

DMBS_JOB.NEXT_DATE(
job          IN    INTEGER,
next_date    IN    TIMESTAMP);

**Table 7** DBMS_JOB.NEXT_DATE interface parameters¶
Parameter	Type	Input/Output Parameter	Can Be Empty	Description
job	integer	IN	No	Specifies the job number.
next_date	timestamp	IN	No	Specifies the next execution time.

Note

If the specified next_date value is earlier than the current date, the job is executed once immediately.

For example:

CALL dbms_job.next_date(101, sysdate);

DBMS_JOB.INTERVAL

The stored procedure INTERVAL modifies the execution interval attribute of a job.

A prototype of the DBMS_JOB.INTERVAL function is as follows:

DMBS_JOB.INTERVAL(
job              IN   INTEGER,
interval         IN   TEXT);

**Table 8** DBMS_JOB.INTERVAL interface parameters¶
Parameter	Type	Input/Output Parameter	Can Be Empty	Description
job	integer	IN	No	Specifies the job number.
interval	text	IN	Yes	Specifies the time expression for calculating the next time the job will be executed. If this parameter is left blank or set to null, the job will be executed only once, and the job status will change to 'd' afterward. interval must be a valid time or interval type.

For example:

CALL dbms_job.interval(101, 'sysdate + 1.0/1440');

Note

For a job that is currently running (that is, job_status is 'r'), it is not allowed to use remove, change, next_date, what, or interval to delete or modify job parameters.

DBMS_JOB.CHANGE_OWNER
The stored procedure CHANGE_OWNER modifies the owner of a job.
A prototype of the DBMS_JOB.CHANGE_OWNER function is as follows:
```
DMBS_JOB.CHANGE_OWNER(
job             IN     INTEGER,
new_owner       IN     NAME);
```
Table 9 DBMS_JOB.CHANGE_OWNER interface parameters¶
Parameter
Type
Input/Output Parameter
Can Be Empty
Description
job
integer
IN
No
Specifies the job number.
new_owner
name
IN
No
Specifies the new username.
For example:
```
CALL dbms_job.change_owner(101, 'alice');
```

Constraints¶

After a new job is created, this job belongs to the current coordinator only, that is, this job can be scheduled and executed only on the current coordinator. Other coordinators will not schedule or execute this job. All coordinators can query, modify, and delete jobs created on other CNs.
Create, update, and delete jobs only using the procedures provided by the DBMS_JOB package. These procedures synchronize job information between different CNs and associate primary keys between the pg_jobs tables. If you use DML statements to add, delete, or modify records in the pg_jobs table, job information will become inconsistent between CNs and system tables may fail to be associated, compromising internal job management.
Each user-created task is bound to a CN. If the automatic migration function is not enabled, task statuses cannot be updated in real time when the CN is faulty during task execution. When a CN fails, all jobs on this CN cannot be scheduled or executed until the CN is restored manually. Enable the automatic migration function on CNs, so that jobs on the faulty CN will be migrated to other CNs for scheduling.
For each job, the hosting CN updates the real-time job information (including the job status, last execution start time, last execution end time, next execution start time, the number of execution failures if any) to the pg_jobs table, and synchronizes the information to other CNs, ensuring consistent job information between different CNs. In the case of CN failures, job information synchronization is reattempted by the hosting CNs, which increases job execution time. Although job information fails to be synchronized between CNs, job information can still be properly updated in the pg_jobs table on the hosting CNs, and jobs can be executed successfully. After a CN recovers, job information such as job execution time and status in its pg_jobs table may be incorrect and will be updated only after the jobs are executed again on related CNs.
For each job, a thread is established to execute it. If multiple jobs are triggered concurrently as scheduled, the system will need some time to start the required threads, resulting in a latency of 0.1 ms in job execution.
The length of the SQL statement to be executed in a job is limited. The maximum length is 8 KB.

last updated: 2024-11-13 15:46 UTC - commit: bdcb01d62e4f1de672b662d88ef6cb8eb3a27a29

Parameter	Type	Input/Output Parameter	Can Be Empty	Description
job	integer	IN	No	Specifies the job number.
new_owner	name	IN	No	Specifies the new username.