Oracle DBMS_JOB
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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.
AUTHID DEFINER
Constants
Name Data Type Value
any_instance BINARY_INTEGER 0
Dependencies
ALL_JOBS DBMS_PCLXUTIL JOB$
BSLN_INTERNAL DBMS_PRVTAQIP JOBSEQ
CDB_JOBS DBMS_SCHEDULER JOBSEQLSBY
DBA_JOBS DBMS_SNAPSHOT USER$
DBMS_AQADM_SYS DBMS_SQL USER_JOBS
DBMS_ASSERT DBMS_SYS_ERROR V$DATABASE
DBMS_EXPFIL_DR DBMS_SYS_SQL V$INSTANCE
DBMS_IJOB DBMS_UTILITY WWV_FLOW_JOB
DBMS_INTERNAL_LOGSTDBY DBMS_XDBT WWV_FLOW_PLSQL_JOB
DBMS_IREFRESH DUAL WWV_FLOW_SC_TRANSACTIONS
DBMS_ISCHED EXPFIL_ALTEREXPTAB_MAINT WWV_FLOW_SW_SCRIPT
DBMS_I_INDEX_UTL    
Documented Yes
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, EXFSYS, and APEX_040200
Source {ORACLE_HOME}/rdbms/admin/dbmsjob.sql
Subprograms
 
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);
exec dbms_job.broken(42, TRUE);
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);
-- To run everynight at midnight starting tonight
exec dbms_job.submit(:v_JobNo, 'proc1;', TRUNC(SYSDATE)+1, 'TRUNC(SYSDATE)+1');

-- To run every hour, on the hour, starting at the top of the hour
exec dbms_job.submit(:v_JobNo, 'proc2;', TRUNC(SYSDATE+(1/24), 'HH'),
'TRUNC(SYSDATE+(1/24),''HH'')');

-- To run every hour, starting now
exec dbms_job.submit(:v_JobNo, 'proc3;', INTERVAL => 'SYSDATE+(1/24)');

-- To run every ten minutes at 0,10,20,etc. minutes past the hour, starting at the top of the hour
exec dbms_job.submit(:v_JobNo, 'proc4;', TRUNC(SYSDATE+(1/24), 'HH'),
'TRUNC(SYSDATE+(10/24/60),''MI'')');

-- To run every 2 min., on the minute, starting at the top of the minute
exec dbms_job.submit(:v_JobNo, 'proc5;', TRUNC(SYSDATE+(1/24/60), 'MI'),
'TRUNC(SYSDATE+(2/24/60),''MI'')');

-- To run every two minutes, starting now
exec dbms_job.submit(:v_JobNo, 'proc6;', INTERVAL => 'SYSDATE+(2/24/60)');

-- To run every half hour, starting at the top of the hour
exec dbms_job.submit(:v_JobNo, 'proc7;', TRUNC(SYSDATE+(1/24), 'HH'),
'TRUNC(SYSDATE+(30/24/60),''MI'')');
 
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 IS

BEGIN
  INSERT INTO job_table
  (now)
  VALUES
  (SYSDATE);
  COMMIT;
END do_job;
/

CREATE OR REPLACE PROCEDURE job_call 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
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
DBMS_AUTOTASK_ADMIN
DBMS_SCHEDULER
Functions
Procedures
Packages
Table Triggers

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-2014 Daniel A. Morgan All Rights Reserved