Update: Although it is still relevant today, this was originally written years ago for Oracle 9i and below. dbms_scheduler is the recommended method to schedule, in the newer versions of Oracle.
Occasionally, everyone has a need to kick off some PL/SQL procedure later in the day/night because some condition will not be met until that time and you cannot be physically there. Typically, one just logs into the system just to kick off the procedure and logs off right away.
There are plenty of scheduling tools that are both command line and UI based but the mechanism below is a built in Oracle mechanism to do one-time kick offs like the one described above using DBMS_JOB.
In the example below, a new job is created with a call to DBMS_JOB.SUMBIT giving it the text to run and the time to run it. By indicating that the interval is NULL, we say that we only want to run it once and that it should be removed after that.
Please note that the user the job is submitted as has to have permission to run the procedure or anonymous block specified.
DECLARE v_JobNo NUMBER; --This is what will be run...it can be an anonymous block like the one below or just a procedure call. v_RunWhat VARCHAR2(1024) := 'BEGIN ' || ' Common_Logic.Writer.Write(Common_Logic.Writer.c_Writer_Default, ''This is a test''); ' || 'END; '; --This is the time to run it v_RunAt DATE := TO_DATE('08/19/2012 13:24:00', 'MM/DD/YYYY HH24:MI:SS'); BEGIN dbms_job.submit(job => v_JobNo, what => v_RunWhat, next_date => v_RunAt, interval => NULL); dbms_output.put_line(v_JobNo); COMMIT; END;
To look at the registered jobs, use this query
SELECT * FROM dba_jobs;
At the scheduled time, if the job completes successfully, it will be removed automatically from the jobs list. Otherwise, the “BROKEN” column in the above view will get a value of Y and it will continue to stay on the list of registered jobs until it is either fixed with a call to DBMS_JOB.CHANGE or removed.
You could manually remove your job by doing
EXEC DMBS_JOB.REMOVE( [JOB] );
Where [JOB] is the number corresponding to your job in the view all_jobs.
Warning: The use of DBMS_JOB is not recommended in Oracle 10g and above (use DBMS_SCHEDULER instead).