The Library is currently in the process of being upgraded from Oracle Database Version 126.96.36.199 to 188.8.131.52.
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.
Do you remember when mainframes were replaced by client-server? When
client-server was replaced by n-tier architecture? The "Cloud" is a
distraction ... DevOps is not. Prepare for the future.
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.
Unique constraint (SYS.I_JOB_JOB) violated
Interval must evaluate to a time in the future
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
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.
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'),
-- 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'),
-- 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'),
-- 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'),