12.6. Using Scheduler Programs

A program defines the action that will occur when a job runs. It can be a PL/SQL block, a stored procedure, or an operating system executable. In the previous section, you learned to define a program within the confines of the CREATE_JOB procedure. However, programs can also be created as independent objects that can be reused by many different jobs. And because programs can also accept arguments, they offer flexibility and encourage reuse.

In the following sections, you will learn the different attributes that define a Scheduler program object. You will learn how to create new programs and how to drop them. Finally, you will also learn to define arguments for programs.

12.6.1. Program Attributes

Scheduler programs have a specific set of attributes that you can set to define the characteristics of the program. These attributes can be set at creation time through the following CREATE_PROGRAM procedure parameters:

PROGRAM_NAME The PROGRAM_NAME parameter specifies the name assigned to the new program. Because programs are stored like any other database object, standard Oracle object naming requirements are enforced for programs. This means that the program name must not only be a valid Oracle object name, it must also be unique within the schema.

PROGRAM_TYPE The PROGRAM_TYPE parameter specifies the type of program that will be created. This is a required parameter and cannot be excluded. It can be any one of the following:

PLSQL_BLOCK The program is an anonymous PL/SQL block. Anonymous PL/SQL block jobs do not accept job or program arguments, so the NUMBER_OF_ARGUMENTS attribute must be set to 0.

STORED_PROCEDURE The program is a PL/SQL stored procedure. By using PL/SQL's External Procedure feature, the PL/SQL procedure could be a wrapper to call a Java stored procedure or an external C routine.

EXECUTABLE The program is external to the database. An external program is any program that can be executed from the operating system's command line.

PROGRAM_ACTION The PROGRAM_ACTION attribute specifies the code to be executed. For a PL/SQL block, the Scheduler automatically wraps the PROGRAM_ACTION code in its own PL/SQL block prior to execution. Therefore, this attribute can be a complete PL/SQL block or one or more lines of valid PL/SQL code.

NUMBER_OF_ARGUMENTS The NUMBER_OF_ARGUMENTS parameter specifies the number of arguments that the job accepts. The range is 0 (the default) to 255.

ENABLED The ENABLED parameter specifies whether the job is created in an enabled state. A value of TRUE means the program will be enabled. By default, all programs are created disabled, so the default value for this parameter is FALSE.

COMMENTS The COMMENTS parameter allows the entry of a comment to document the program.

12.6.2. Creating Programs

New programs can be created by using the DBMS_SCHEDULER.CREATE_PROGRAM procedure. This procedure creates a new program object that can in turn be called by job objects. The procedure's parameters match the list of attributes described in the previous section.

Programs, like jobs, are stored as independent schema objects. Therefore, they must have unique names within the schema, and they must conform to Oracle's standards for valid object naming.

To create a program that executes a stored procedure, see the following example:

SQL> begin
  2  dbms_scheduler.create_program(
  3  program_name => 'STATS_PROGRAM',
  4  program_type => 'STORED_PROCEDURE',
  5  program_action => 'DBMS_STATS.GATHER_SCHEMA_STATS',
  6  number_of_arguments => 1,
  7  comments => 'Gather stats for a schema'),

8  end;
  9  /

PL/SQL procedure successfully completed.

This example creates a reusable program that will gather statistics for a schema. As you can see, the program requires one argument, which is the name of the schema. The argument can be defined by using the DEFINE_PROGRAM_ARGUMENT procedure, as shown here:

SQL> begin
  2  dbms_scheduler.define_program_argument(
  3  program_name => 'STATS_PROGRAM',
  4  argument_position => 1,
  5  argument_type => 'VARCHAR2'),
  6  end;
SQL> /

PL/SQL procedure successfully completed.

This program can now be used by a job object, and the schema name can be passed in as an argument. Therefore, the same program can be used by many jobs, each gathering statistics for a different schema.

Arguments can be dropped from programs as well. The DBMS_SCHEDULER.DROP_PROGRAM_ARGUMENT procedure allows arguments to be dropped by either name or by the position of the argument. The following examples show how an argument may be dropped by specifying either its position:

SQL> begin
  2  dbms_scheduler.drop_program_argument(
  3  program_name => 'STATS_PROGRAM',
  4  argument_position => 1,
  5  end;
SQL> /

PL/SQL procedure successfully completed.

or its name:

SQL> begin
  2  dbms_scheduler.drop_program_argument(
  3  program_name => 'STATS_PROGRAM',
  4  argument_name => 'SCHEMA_NAME',
  5  end;
SQL> /

PL/SQL procedure successfully completed.

12.6.3. Dropping Programs

Program objects can be dropped through the use of the DBMS_SCHEDULER.DROP_PROGRAM procedure. This procedure removes the procedure entirely from the database. If existing job definitions include the program that you are attempting to drop, the drop will fail. However, if you set the FORCE parameter to TRUE, the program will be dropped and the referencing jobs will become disabled.

The following example drops the STATS_PROGRAM program and disables any referencing jobs:

SQL> begin
  2  dbms_scheduler.drop_program (
  3  program_name => 'STATS_PROGRAM',
  4  force => TRUE
  5  end;
SQL> /

PL/SQL procedure successfully completed.