About

On this page a short summary is given of the scheduler functionality.

Architecture

The scheduler software makes use of the functionality in the Oracle Database and is developed in the language PL/SQL and Java.
It consists of a server part installed inside the Oracle Database and a full Graphical User Interface (GUI) based on Java technology.
Anywhere you are able to install a Oracle Database the scheduler software can be used.
The GUI can be used on any PC client where a Java is available (no Oracle client needs to be installed for the GUI).
For installing the server part a license is needed. The GUI can be downloaded for free and runs on any server installation.

Back to top 

Scheduling

A scheduled PL/SQL program is called a scheduler task. Tasks can be created with SQL*Plus and with the GUI but also from within another PL/SQL program. The following types of scheduling can be done (this is not an extensive list).

Check the scheduler task documentation for all schedule options.

Back to top 

Flow

One of the most powerfull features is the possibility to combine scheduled PL/SQL programs (tasks) into a flow.
Each task in a flow, except the first one, depends on another task in the same flow. If a task has finished the depending tasks are started, are being hold (waiting on user action), are passed (the PL/SQL program is not executed) or get an error status.
Different tasks can depend on the same task.

Back to top 

Output

Screenshot PL/SQL scheduler window W05 Task output

An important feature is that the output of each task is stored in the Oracle Database and can be viewed later on at any time you like.
The output is already available during the execution of the PL/SQL program.
The scheduler also contains some extra built-in functions that can be used for redirecting the output to different destinations.

If you have an existing PL/SQL program (package or procedure) that uses the DBMS_OUTPUT package for generating output no software has to be adjusted when the program is executed. The scheduler automatically stores the generated output in the database.
Back to top 

Java Graphical User Interface (GUI)

Screenshot PL/SQL scheduler window W10 Queue monitor

The Graphical User Interface makes it easy for scheduling PL/SQL programs and monitoring them.
As the name already states, it is a graphical interface. Anything that can be done with the GUI can also be done with SQL*Plus. It is even possible to write your own interface and integrate it with your own software.

Back to top 

Server side

The plsql scheduler server part is installed inside the Oracle Database. One important consequence is that the software is completely platform independent. If the database is moved from one platform to another no extra actions need to be taken.
If the Oracle Database is upgraded to a newer release (even by using export-import) no extra actions are needed.
The server software makes use of the oracle dbms_job mechanism.

Back to top 

Scheduler PL/SQL interface

The scheduler comes with an easy to use PL/SQL interface. All scheduler functionality is avaialable through this interface and can be integrated in any other PL/SQL program.
In the next example we have a PL/SQL database procedure batchrun that needs to be executed each day at 22:30:00 till 31 december 2010.
First a scheduler script is created which contains the name of the procedure to be executed. This only needs to be done once, with this script an unlimited number of tasks can be scheduled.
Second a schedule is made which contains the date and time when the scheduler script must be executed. Third a scheduler task is created for this script and schedule.

begin
  -- first we create a scheduler script for the plsql program
  so_script.init_create;
  so_script.create_or_replace(
      p_script_name => 'batchscript',
      p_script_procedure => 'batchrun');

  -- create a schedule containing information when the script
  -- needs to be executed.
  so_task.schedule_create(
     p_schedule_time => '22:30',
     p_schedule_till => to_date('2010-12-31','YYYY-MM-DD'));

  -- and create a task with this schedule
  so_task.submit(
     p_script_name => 'batchscript');
  so_task.committasks;
end;
/
  

If we omit the call to so_task.schedule_create in the above example the script is executed directly and only once. Also because we did not specify a date for the first execution of the script the first run is scheduled for today.
This is only a simple example, more can be found in the PL/SQL task documentation and PL/SQL script documentation.

Back to top