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.


The basic syntax for DBMS_JOB.SUBMIT is as follows:

   job        OUT BINARY_INTEGER,
   what       IN VARCHAR2,
   next_date  IN DATE,
   interval   IN VARCHAR2,

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:

   l_job NUMBER;
      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

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.