DBMS_JOB.SUBMIT is a procedure in Oracle’s database that allows for the scheduling of jobs, which are tasks or series of PL/SQL commands that can be executed at a later time, either once or on a recurring basis. This functionality is part of the DBMS_JOB package, which provides a simple, straightforward way to manage jobs within the Oracle database before the introduction of the more robust and feature-rich DBMS_SCHEDULER in Oracle 10g.
Syntax
The basic syntax for DBMS_JOB.SUBMIT is as follows:
DBMS_JOB.SUBMIT( job OUT BINARY_INTEGER, what IN VARCHAR2, next_date IN DATE, interval IN VARCHAR2, no_parse IN BOOLEAN DEFAULT FALSE);
job: This is an output parameter that returns the job number assigned by Oracle after the job is successfully submitted.
what: This parameter specifies the PL/SQL block or anonymous block of code to execute.
next_date: This is the date and time when the job should be first executed.
interval: This parameter specifies the frequency of job execution, using an Oracle date expression. The job will be rescheduled based on this expression after each run.
no_parse: If set to TRUE, the job is not parsed at submission time. This is useful for submitting jobs that will compile successfully only at a future date.
Usage Example
Here’s a simple example of how to use DBMS_JOB.SUBMIT to schedule a job:
DECLARE
l_job NUMBER;
BEGIN
DBMS_JOB.SUBMIT(
job => l_job,
what => 'BEGIN my_procedure; END;',
next_date => SYSDATE + 1/24, -- Schedule for 1 hour from now
interval => 'SYSDATE + 1' -- Reschedule every day
);
COMMIT; -- Important to commit the transaction to schedule the job
END;
This example schedules a job to execute my_procedure starting one hour from the current time and then to run it every day thereafter.
Key Considerations
Commit Requirement: Changes made by DBMS_JOB.SUBMIT are not saved until the transaction is explicitly committed.
Privileges: The user submitting the job must have the necessary privileges to execute the specified PL/SQL block and must be able to use the DBMS_JOB package.
Transition to DBMS_SCHEDULER: Oracle recommends using DBMS_SCHEDULER for new applications because it offers more flexibility, functionality, and control compared to DBMS_JOB. However, DBMS_JOB remains supported for backward compatibility.
In summary, DBMS_JOB.SUBMIT provides a convenient way to schedule and automate tasks within the Oracle database environment, making it a useful tool for database administrators and developers who need to automate database maintenance tasks, batch jobs, or other repetitive tasks.