Oracle DBMS_JOB
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose This package is deprecated and has been supplanted by DBMS_SCHEDULER. If you have any code using this package it is recommended that you move it to DBMS_SCHEDULER as part of migrating to 12c or above.
AUTHID CURRENT_USER
Constants
Name Data Type Value
any_instance BINARY_INTEGER 0
Dependencies
ALL_JOBS DBMS_GSM_POOLADMIN DBMS_SQL
BSLN_INTERNAL DBMS_IJOB DBMS_SYSTEM
CDB_JOBS DBMS_ISCHED DBMS_SYS_ERROR
DBA_JOBS DBMS_I_INDEX_UTL DBMS_UTILITY
DBMS_AQADM_SYS DBMS_PRVTAQIP JOB$
DBMS_ASSERT DBMS_SCHEDULER USER_JOBS
DBMS_AVTUNE DBMS_SNAPSHOT_KKXRCA  
Documented Yes: Packages and Types Reference
Exceptions
Error Code Reason
ORA-00001 Unique constraint (SYS.I_JOB_JOB) violated
ORA-23420 Interval must evaluate to a time in the future
First Available 7.3.4
Job Intervals Execute daily          'SYSDATE + 1'
Execute once per week  'SYSDATE + 7'
Execute hourly         'SYSDATE + 1/24'
Execute every 10 min.  'SYSDATE + 10/1440'
Execute every 30 sec.  'SYSDATE + 30/86400'
Do not re-execute      NULL
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmsjob.sql
Subprograms
BACKGROUND_PROCESS ISUBMIT SUBMIT
BROKEN IS_JOBQ USER_EXPORT
CHANGE NEXT_DATE WHAT
INSTANCE REMOVE Demos
INTERVAL RUN Queries
 
BACKGROUND_PROCESS
Indicates whether execution is a background process or foreground process

For internal use only		 dbms_job.background_process RETURN BOOLEAN;
set serveroutput on

DECLARE
 b BOOLEAN;
BEGIN
  IF dbms_job.background_process THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
END IF;
/
 
BROKEN
How a job becomes 'broken' Oracle has failed to successfully execute the job after 16 attempts.
or
You have marked the job as broken, using the procedure DBMS_JOB.BROKEN

Once a job has been marked as broken, Oracle will not attempt to execute the job until it is either marked not broken, or forced to be execute by calling the DBMS_JOB.RUN.
Force a job to broken status dbms_job.broken (
job       IN BINARY_INTEGER,
broken    IN BOOLEAN,
next_date IN DATE DEFAULT SYSDATE);
-- break one job
exec dbms_job.broken(42, TRUE);

-- break all jobs
DECLARE
  CURSOR jcur IS
  SELECT job
  FROM dba_jobs
  WHERE broken = 'N';
BEGIN
  FOR jrec IN jcur LOOP
    dbms_job.broken(jrec.job, TRUE);
  END LOOP;
END;
/
The following example marks job 14144 as not broken and sets its next execution date to - - the following Monday exec dbms_job.broken(14144, FALSE, NEXT_DAY(SYSDATE, 'MONDAY'));
Force a broken job to run and fix it exec dbms_job.broken(JOB=>&job_no, NEXT_DATE=>SYSDATE + &when_plus_sysdate, broken=>FALSE);
 
CHANGE
Change A Job's Attributes dbms_job.change(
job       IN BINARY_INTEGER,
what      IN VARCHAR2,
next_date IN DATE,
interval  IN VARCHAR2,
instance  IN BINARY_INTEGER DEFAULT NULL,
force     IN BOOLEAN        DEFAULT FALSE);
exec dbms_job.change(14144, NULL, NULL, 'SYSDATE + 3');
 
INSTANCE
Assign a specific instance to execute a job dbms_job.instance(
job      IN BINARY_INTEGER,
instance IN BINARY_INTEGER,
force    IN BOOLEAN DEFAULT FALSE);
SELECT instance_number
FROM gv$instance;

exec dbms_job.instance(42, 1);
 
INTERVAL
Reset the job interval dbms_job.interval(
job      IN BINARY_INTEGER,
interval IN VARCHAR2);
-- use TRUNC(SYSDATE) to keep the job interval from drifting.

exec dbms_job.interval(179, 'TRUNC(SYSDATE) + 24/24');
 
ISUBMIT
Submit a job with a user specified job number dbms_job.isubmit (
job       IN BINARY_INTEGER,
what      IN VARCHAR2,
next_date IN DATE,
interval  IN VARCHAR2 DEFAULT 'NULL',
no_parse  IN BOOLEAN  DEFAULT FALSE);

Note: no_parse indicates whether to parse job PL/SQL at time of submission (FALSE) or execution (TRUE)
exec dbms_job.isubmit(4242, 'MYPROC', SYSDATE);
 
IS_JOBQ
Undocumented

Thank you Laszlo Vincze for the correction		 dbms_job.is_jobq RETURN BOOLEAN;
set serveroutput on

DECLARE
 b BOOLEAN;
BEGIN
  IF dbms_job.is_jobq THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/
 
NEXT_DATE
Reset next execution date and time for a job dbms_job.next_date(
job       IN BINARY_INTEGER,
next_date IN DATE);
exec dbms_job.next_date(134, SYSDATE + 1/24);
 
REMOVE
To remove a job in the job queue dbms_job.removejob IN BINARY_INTEGER);
SELECT job
FROM user_job;

exec dbms_job.remove(23);
 
RUN
Force a job to run immediately and, if it was broken, reset it to not broken dbms_job.run(
job   IN BINARY_INTEGER,
force IN BOOLEAN DEFAULT FALSE);
exec dbms_job.run(job_no);
 
SUBMIT
Submit Parameters JOB An output parameter, this is the identifier assigned to the job you created. You must use this jobnumber whenever you want to alter or remove the job.

WHAT This is the PL/SQL code you want to have executed. The WHAT parameter must end with a semi-colon.

NEXT_DATE The next date when the job will be run. The default value is SYSDATE.

INTERVAL The date function that calculates the next time to execute the job. The default value is NULL. INTERVAL must evaluate to a future point in time or NULL. This parameter is a VARCHAR2 and must be enclosed in single quotes.

NO_PARSE This is a flag. If NO_PARSE is set to FALSE (the default), Oracle parses the procedure associated with the job. If NO_PARSE is set to TRUE, Oracle parses the procedure associated with the job the first time that the job is executed. If, for example, you want to submit a job before you have created the tables associated with the job, set NO_PARSE to TRUE.
Submit a job with a job number selected from sys.jobseq

Many thanks for  Kieron Hardy for contributing these examples.		 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,
INSTANCE  IN  BINARY_INTEGER DEFAULT 0,
FORCE     IN  BOOLEAN        DEFAULT FALSE);
DECLARE
 JobNo BINARY_INTEGER;
BEGIN
  -- run everynight at midnight starting tonight
  dbms_job.submit(JobNo, 'proc1;', TRUNC(SYSDATE)+1, 'TRUNC(SYSDATE)+1');
  dbms_output.put_line(TO_CHAR(JobNo));
  -- run every hour, on the hour, starting at the top of the hour
  dbms_job.submit(JobNo, 'proc2;', TRUNC(SYSDATE+(1/24), 'HH'),
    'TRUNC(SYSDATE+(1/24),''HH'')');
  dbms_output.put_line(TO_CHAR(JobNo));
  -- run every hour, starting now
  dbms_job.submit(JobNo, 'proc3;', INTERVAL => 'SYSDATE+(1/24)');
  dbms_output.put_line(TO_CHAR(JobNo));
  -- run every 10 min. at 0,10,20,... min. past the hour, starting at 00 min.
  dbms_job.submit(JobNo, 'proc4;', TRUNC(SYSDATE+(1/24), 'HH'),
    'TRUNC(SYSDATE+(10/24/60),''MI'')');
  dbms_output.put_line(TO_CHAR(JobNo));
  -- run every 2 min., on the minute, starting at the top of the minute
  dbms_job.submit(JobNo, 'proc5;', TRUNC(SYSDATE+(1/24/60), 'MI'),
    'TRUNC(SYSDATE+(2/24/60),''MI'')');
  dbms_output.put_line(TO_CHAR(JobNo));
  -- run every two minutes, starting now
  dbms_job.submit(JobNo, 'proc6;', INTERVAL => 'SYSDATE+(2/24/60)');
  dbms_output.put_line(TO_CHAR(JobNo));
  -- run every half hour, starting at the top of the hour
  dbms_job.submit(JobNo,'proc7;',TRUNC(SYSDATE+(1/24),'HH'),
    'TRUNC(SYSDATE+(30/24/60),''MI'')');
  dbms_output.put_line(TO_CHAR(JobNo));
END;
/
 
USER_EXPORT
Produces the text of a call to re-create the given job

Overload 1		 dbms_job.user_export(
job    IN     BINARY_INTEGER,
mycall IN OUT VARCHAR2);
SELECT job
FROM user_jobs;

set serveroutput on

DECLARE
 callstr VARCHAR2(500);
BEGIN
  dbms_job.user_export(186, callstr);
  dbms_output.put_line(callstr);
END;
/
Alters instance affinity (8i and after) and preserves compatibility

Overload 2		 dbms_job.user_export (
job    IN     BINARY_INTEGER,
mycall IN OUT VARCHAR2,
myinst IN OUT VARCHAR2);
set serveroutput on

DECLARE
 callstr VARCHAR2(500);
 inststr VARCHAR2(50);
BEGIN
  dbms_job.user_export(186, callstr);
  dbms_output.put_line(callstr);
  dbms_output.put_line(inststr);
END;
/
 
WHAT
Change a job's definition dbms_job.what(
job  IN BINARY_INTEGER,
what IN VARCHAR2);
exec dbms_job.what(42, 'YOURPROC');
 
Demos
Job Scheduling Demonstration CREATE TABLE job_table (
now DATE);

CREATE OR REPLACE VIEW job_view AS
SELECT TO_CHAR(now, 'DD-MON-YYYY HH:MI:SS') NOW
FROM job_table;

CREATE OR REPLACE PROCEDURE do_job AUTHID DEFINER IS
BEGIN
  INSERT INTO job_table
  (now)
  VALUES
  (SYSDATE);
  COMMIT;
END do_job;
/

CREATE OR REPLACE PROCEDURE job_call AUTHID DEFINER AS
 JobNo user_jobs.job%TYPE;
BEGIN
  dbms_job.submit(JobNo, 'begin do_job; end;', SYSDATE,
  'SYSDATE + 36/86400');
  COMMIT;
END;
/

exec job_call

SELECT * FROM job_view;

SELECT job, next_date, next_sec, broken
FROM dba_jobs;
 
Related Queries
Jobs Running SELECT r.sid, r.job, r.this_date, r.this_sec, SUBSTR(what,1,40) what
FROM dba_jobs_running r,dba_jobs j
WHERE r.job = j.job;
User Jobs col job format 99999

SELECT  job, next_date, next_sec, failures, broken, SUBSTR(what,1,40) DESCRIPTION
FROM user_jobs;

Related Topics
Built-in Functions
Built-in Packages
DBMS_AUTOTASK_ADMIN
DBMS_IJOB
DBMS_SCHEDULER
Table Triggers
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx