Press "Enter" to skip to content

How to Schedule a Job in Oracle SQL Developer?

Below is the step by step guide to how to schedule a job in Oracle SQL Developer.

Steps to Schedule a Job in Oracle SQL Developer

In the following example, we will create a job to run every night at 11:00 PM to execute an Oracle stored procedure BATCH_PROCESS_AT_NIGHT.

  1. In Oracle SQL Developer after connecting to the database, click on the Schema node to expand in which you want to schedule a job.
  2. Scroll down the tree menu and click on the Scheduler node to expand.
  3. Then in the Scheduler node, click on the Job node to select and then do the right click.
  4. From the shortcut menu, choose New Job option.
  5. The Create Job window will open as shown in below image. Then set the following configuration to run the job every night at 11:00 PM.

Schedule a Job in Oracle SQL Developer

  1. Specify the job name in the Job Name field.
  2. Enable checkbox is by default enabled.
  3. Specify the job description in the Description field.
  4. Then from the Type of Job drop-down, select the Stored Procedure.
  5. Specify the Schema and Procedure name.
  6. In the When to Execute Job section, choose Repeating from the drop-down.
  7. To specify Repeat Interval details, click on the Pencil icon to open the Repeat Interval window as shown in the below image:

Create the job to run daily in Oracle SQL Developer

  1. From the Repeat Interval every section, choose Daily option to run the job every day.
  2. On Week Day(s) section, select every checkbox.
  3. Specify the time 23:00:00 hours.
  4. Then click on the OK button to return to the Create Job window.
  5. In the Create Job Window, specify the Start Date and timezone, from when you want to start the Job and click on the Apply button to finally Schedule the Job.

Oracle SQL Developer will generate the PL/SQL code for the above Job as shown in the below example:

BEGIN
   DBMS_SCHEDULER.CREATE_JOB (
      job_name              => '"VINISH"."JOB_EXTRACT_DATA"',
      job_type              => 'STORED_PROCEDURE',
      job_action            => 'VINISH.BATCH_PROCESS_AT_NIGHT',
      number_of_arguments   => 0,
      start_date            => TO_TIMESTAMP_TZ (
                                 '2018-10-11 08:39:11.000000000 EUROPE/LONDON',
                                 'YYYY-MM-DD HH24:MI:SS.FF TZR'),
      repeat_interval       => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN',
      end_date              => NULL,
      enabled               => FALSE,
      auto_drop             => FALSE,
      comments              => 'To extract data from daily sales');

   DBMS_SCHEDULER.SET_ATTRIBUTE (name        => '"VINISH"."JOB_PROCESS_SALARY"',
                                 attribute   => 'logging_level',
                                 VALUE       => DBMS_SCHEDULER.LOGGING_OFF);



   DBMS_SCHEDULER.enable (name => '"VINISH"."JOB_PROCESS_SALARY"');
END;

See also: