CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_JOB
Version 11.1
 
General Information
Note: This package is deprecated and has been supplanted by DBMS_SCHEDULER.
Source {ORACLE_HOME}/rdbms/admin/dbmsjob.sql
First Available 7.3.4
Dependencies
job$    
dba_jobs all_jobs user_jobs
dba_jobs_running all_jobs_running user_jobs_running
Exceptions
Error Code Reason
ORA-00001 Unique constraint (SYS.I_JOB_JOB) violated
ORA-23420 Interval must evaluate to a time in the future
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 Execute is granted to PUBLIC with GRANT option
 
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;
END;
/
 
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);
exec dbms_job.interval(179, 'TRUNC(SYSDATE) + 24/24');
Note: Use TRUNC(SYSDATE) to keep the job interval from drifting.
 
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.remove (job 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 job number 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
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
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');
 
DBMS_JOB Demo

Jobs 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;

 
DBMS_JOB 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;
Forcing mandatory log switches I just used a dbms_job that calls a proc that switches the logfile every n minutes (e.g. 30) if it hasn't been switched since then.
 
Related Topics
DBMS_SCHEDULER
Functions
Procedures
Table Triggers
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [150 users online]    © 2010 psoug.org