DBMS_JOB

Related Interfaces

Table 1 lists all interfaces supported by the DBMS_JOB package.

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

  1 2 3 4 5

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

  

  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:

  1 2 3 4 5

  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.SUBMIT_NODE

  The stored procedure SUBMIT submits a job provided by the system. The execution node is specified by the user. This interface is supported only by clusters of version 8.3.0 or later.

  The prototype of the DBMS_JOB.SUBMIT_NODE function is:

  1 2 3 4 5 6

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

  Table 3 DBMS_JOB.SUBMIT_NODE interface parameters

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  what	text	IN	No	Specifies the 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.
  node	text	IN	Yes	Specifies the name of the job execution node.
  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:

  1 2 3 4 5

  select DBMS_JOB.SUBMIT_NODE('call pro_xxx();', to_date('20180101','yyyymmdd'),'sysdate+1','coordinator1'); select DBMS_JOB.SUBMIT_NODE('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)', 'coordinator1', :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:

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

  1	REMOVE(job IN INTEGER);

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

  1 2 3 4

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

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

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

  1 2 3 4 5

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

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

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

  1 2 3

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

  Table 7 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.

  

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

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

  1 2 3

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

  Table 8 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.

  

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

  For example:

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

  1 2 3

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

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

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

  

  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.

Constraints

1. 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.
2. 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.
3. 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.
4. 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.
5. 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.
6. The length of the SQL statement to be executed in a job is limited. The maximum length is 8 KB.