Oracle – Scheduling a procedure to run later using dbms_job

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).

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s