PolarDB:DBMS_JOB

Usage notes

• Only privileged accounts can use the DBMS_JOB extension. For more information about how to create a privileged account, see Create a database account.

• You can create the dbms_job extension in only the postgres database. If you want to configure scheduled jobs for other databases, configure cross-database scheduled jobs in the postgres database. For more information, see Run a scheduled job across databases.

Prepare test data

CREATE TABLE jobrun (
    id              serial               NOT NULL PRIMARY KEY,
    runtime         VARCHAR2(40)
);

CREATE PROCEDURE job_proc
IS
BEGIN
    INSERT INTO jobrun(runtime) VALUES ('job_proc run at ' || TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));
END;

Delete the DBMS_JOB extension

DROP EXTENSION dbms_job CASCADE;

Create a scheduled job

SUBMIT(job OUT BINARY_INTEGER, what VARCHAR2
[, next_date DATE [, interval VARCHAR2 ]])

TRUNC(sysdate,'mi') + 1/(24*60)

TRUNC(sysdate) + 1 + 1/(24)

TRUNC(next_day(sysdate,'monday')) + 1/24

TRUNC(LAST_DAY(SYSDATE)) + 1 + 1/24

TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24

ADD_MONTHS(trunc(sysdate,'yyyy'), 12) + 1/24

Trunc(Sysdate+1) + (8*60+10)/24*60

Trunc(sysdate,'hh') + (60+15)/(24*60)

DECLARE
   jobid           INTEGER;
BEGIN
   DBMS_JOB.SUBMIT(jobid,'job_proc;', SYSDATE, 'SYSDATE + 1/(24 * 60)');
END;

DBMS_JOB.SUBMIT(jobid,'job_proc;', SYSDATE, 'TRUNC(sysdate,'mi') + 1/(24*60)');

DBMS_JOB.SUBMIT(jobid,'job_proc;', SYSDATE, $$TRUNC(sysdate,'mi') + 1/(24*60)$$);

Query scheduled jobs

SELECT * FROM sys.user_jobs;

Modify the content, start time, and execution interval of a scheduled job

CHANGE(job BINARY_INTEGER what VARCHAR2, next_date DATE,interval VARCHAR2)

BEGIN
    DBMS_JOB.CHANGE(1,NULL,TO_DATE('29-DEC-20','DD-MON-YY'),$$Trunc(sysdate,'hh') + (60+15)/(24*60)$$);
END;

Modify the execution interval of a scheduled job

INTERVAL(job BINARY_INTEGER, interval VARCHAR2)

BEGIN
    DBMS_JOB.INTERVAL(1,'TRUNC(sysdate) + 1 + 1/(24)');
END;

Modify the start time of a scheduled job

NEXT_DATE(job BINARY_INTEGER, next_date DATE)

BEGIN
    DBMS_JOB.NEXT_DATE(1, TO_DATE('30-DEC-20','DD-MON-YY'));
END;

Modify the content of a scheduled job

WHAT(job BINARY_INTEGER, what VARCHAR2)

BEGIN
    DBMS_JOB.WHAT(1,'job_proc2');
END;

Stop and start a scheduled job

BROKEN(job BINARY_INTEGER, broken BOOLEAN [, next_date DATE ])

BEGIN
    DBMS_JOB.BROKEN(1,true);
END;

BEGIN
    DBMS_JOB.BROKEN(1,false);
END;

Force a scheduled job to run

RUN(job BINARY_INTEGER)

BEGIN
    DBMS_JOB.RUN(1);
END;

ERROR: syntax error at end of input
CONTEXT: polar-spl function dbms_job._run_job(integer,boolean) line 151 at RAISE line 547 of package body

Delete a scheduled task

REMOVE(job BINARY_INTEGER)

BEGIN
    DBMS_JOB.REMOVE(1);
END;

Query the execution records of a scheduled job

SELECT * FROM dbmsjob.pga_joblog;

Run a scheduled job across databases

The DBMS_JOB extension applies only to the postgres database. If you want to configure scheduled jobs for other databases, configure cross-database scheduled jobs in the postgres database.

An example is used to describe how to configure a cross-database scheduled job. In this example, the DBMS_JOB extension is configured in the postgres database. A scheduled job is configured to run in a database named test. In this example, you want to insert one data record per minute into a table in the test database. For more information about how to create a database, see Create a database.

1. Create a table named jobrun and a stored procedure named job_proc in the test database.

   • Execute the following statement to create a table named jobrun:

     CREATE TABLE public.jobrun (
         id       serial NOT NULL PRIMARY KEY,
         runtime  VARCHAR2(40)
     );

   • Execute the following statement to create a stored procedure named job_proc:

     CREATE PROCEDURE public.job_proc
     IS
     BEGIN
         INSERT INTO jobrun(runtime) VALUES ('job_proc run at ' || TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));
     END;

2. Create a scheduled job in the postgres database.

   You must add the database in which you want to run the scheduled job to the DBMS_JOB.SUBMIT() function. In this example, the database in which the scheduled job runs is named test. For more information about other parameters, see Create a scheduled job.

   The following sample statement provides an example:

   DECLARE
      jobid           INTEGER;
   BEGIN
      DBMS_JOB.SUBMIT(jobid,'job_proc;', SYSDATE, 'SYSDATE + 1/(24 * 60)','test');
   END;

3. Query the state and execution records of the scheduled job in the postgres database.

   • Query the scheduled job:

     SELECT * FROM sys.user_jobs;

     The following output is returned:

      job | jobloguser | job_user | database |           job_created            |           job_changed            |            last_date             | last_sec |         next_date         | next_sec |   total_time    | broken |                 interval                 | failures |                                                      what                                                       | instance
     -----+------------+----------+----------+----------------------------------+----------------------------------+----------------------------------+----------+---------------------------+----------+-----------------+--------+------------------------------------------+----------+-----------------------------------------------------------------------------------------------------------------+----------
        1 | DBUSER     | dbuser   | postgres | 29-OCT-20 02:38:49.478494 +00:00 | 29-OCT-20 02:38:49.478494 +00:00 | 29-OCT-20 02:51:12.025001 +00:00 | 02:51:12 | 29-OCT-20 02:53:12 +00:00 | 02:53:12 | 00:00:00.243224 | N      | BEGIN return SYSDATE + 1/(24 * 30); END; |        0 |  BEGIN EXECUTE IMMEDIATE 'SELECT dbmsjob.dbms_job_internal_job_link(''BEGIN job_proc; END;'', ''test'');' ; END |        0

   • Query the execution records:

     SELECT * FROM dbmsjob.pga_joblog;

     The following output is returned:

      jlgid | jlgjobid | jlgstatus |             jlgstart             |   jlgduration   
     -------+----------+-----------+----------------------------------+-----------------
          1 |        1 | s         | 29-OCT-20 02:38:49.762995 +00:00 | 00:00:00.017495
          2 |        1 | s         | 29-OCT-20 02:39:50.061113 +00:00 | 00:00:00.016463
          3 |        1 | s         | 29-OCT-20 02:40:50.062331 +00:00 | 00:00:00.016244

4. Query the data in the jobrun table in the test database.

   Execute the following statement:

   SELECT * FROM jobrun;

   The following output is returned:

    id |               runtime               
   ----+-------------------------------------
     1 | job_proc run at 2020-10-29 02:38:50
     2 | job_proc run at 2020-10-29 02:39:50
     3 | job_proc run at 2020-10-29 02:40:50

BEGIN
    DBMS_JOB.CHANGE(1,NULL,SYSDATE,'SYSDATE + 1/(24 * 30)','test');
END;