Skip Headers
Oracle® Database Administrator's Guide
11g Release 2 (11.2)

Part Number E17120-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

Creating and Managing Programs to Define Jobs

A program is a collection of metadata about a particular task. You optionally use a program to help define a job. This section introduces you to basic program tasks, and discusses the following topics:

See Also:

"Programs" for an overview of programs.

Program Tasks and Their Procedures

Table 29-3 illustrates common program tasks and their appropriate procedures and privileges:

Table 29-3 Program Tasks and Their Procedures

Task Procedure Privilege Needed

Create a program

CREATE_PROGRAM

CREATE JOB or CREATE ANY JOB

Alter a program

SET_ATTRIBUTE

ALTER or CREATE ANY JOB or be the owner

Drop a program

DROP_PROGRAM

ALTER or CREATE ANY JOB or be the owner

Disable a program

DISABLE

ALTER or CREATE ANY JOB or be the owner

Enable a program

ENABLE

ALTER or CREATE ANY JOB or be the owner


See "Scheduler Privileges" for further information regarding privileges.

Creating Programs

You create programs by using the CREATE_PROGRAM procedure or Enterprise Manager. By default, programs are created in the schema of the creator. To create a program in another user's schema, you need to qualify the program name with the schema name. For other users to use your programs, they must have EXECUTE privileges on the program, therefore, once a program has been created, you have to grant the EXECUTE privilege on it. An example of creating a program is the following, which creates a program called my_program1:

BEGIN
  DBMS_SCHEDULER.CREATE_PROGRAM (
   program_name           => 'my_program1',
   program_action         => '/usr/local/bin/date',
   program_type           => 'EXECUTABLE',
   comments               => 'My comments here');
END;
/

Programs are created disabled by default; you have to enable them before you can enable jobs that point to them. Do not attempt to enable a program that requires arguments until you define all program arguments, as described in "Defining Program Arguments".

Defining Program Arguments

After creating a program, you can define program arguments. Arguments are defined by position in the calling sequence, with an optional argument name and optional default value. If no default value is defined for a program argument, the job that references the program must supply an argument value. (The job can also override a default value.) All argument values must be defined before the job can be enabled.

To set program argument values, use the DEFINE_PROGRAM_ARGUMENT or DEFINE_ANYDATA_ARGUMENT procedures. DEFINE_ANYDATA_ARGUMENT is used for complex types that must be encapsulated in an ANYDATA object. An example of a program that might need arguments is one that starts a reporting program that requires a start date and end date. The following code example sets the end date argument, which is the second argument expected by the reporting program. The example also assigns a name to the argument so that you can refer to the argument by name (instead of position) from other package procedures, including SET_JOB_ANYDATA_VALUE and SET_JOB_ARGUMENT_VALUE.

BEGIN
 DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (
   program_name            => 'operations_reporting',
   argument_position       => 2,
   argument_name           => 'end_date',
   argument_type           => 'VARCHAR2',
   default_value           => '12-DEC-03');
END;
/

Valid values for the argument_type argument are only SQL data types, therefore booleans are not supported. For external executables, only string types such as CHAR or VARCHAR2 are permitted.

You can drop a program argument either by name or by position, as in the following:

BEGIN
 DBMS_SCHEDULER.DROP_PROGRAM_ARGUMENT (
   program_name            => 'operations_reporting',
   argument_position       => 2);

 DBMS_SCHEDULER.DROP_PROGRAM_ARGUMENT (
   program_name            => 'operations_reporting',
   argument_name           => 'end_date');
END;
/

In some special cases, program logic is dependent on the Scheduler environment. The Scheduler has some predefined metadata arguments that can be passed as an argument to the program for this purpose. For example, for some jobs whose schedule is a window name, it is useful to know how much longer the window will be open when the job is started. This is possible by defining the window end time as a metadata argument to the program.

If a program needs access to specific job metadata, you can define a special metadata argument using the DEFINE_METADATA_ARGUMENT procedure, so values will be filled in by the Scheduler when the program is executed.

Altering Programs

You alter a program by modifying its attributes. You can use Enterprise Manager or the DBMS_SCHEDULER.SET_ATTRIBUTE and DBMS_SCHEDULER.SET_ATTRIBUTE_NULL package procedures to alter programs. See the DBMS_SCHEDULER.CREATE_PROGRAM procedure in Oracle Database PL/SQL Packages and Types Reference for details on program attributes.

If any currently running jobs use the program that you altered, they continue to run with the program as defined before the alter operation.

The following example changes the executable that program my_program1 runs:

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE (
   name           => 'my_program1',
   attribute      => 'program_action',
   value          => '/usr/local/bin/salesreports1');
END;
/

Dropping Programs

You drop one or more programs using the DROP_PROGRAM procedure or Enterprise Manager.

Running jobs that point to the program are not affected by the DROP_PROGRAM call, and are allowed to continue. Any arguments that pertain to the program are also dropped when the program is dropped. You can drop several programs in one call by providing a comma-delimited list of program names. For example, the following statement drops three programs:

BEGIN
  DBMS_SCHEDULER.DROP_PROGRAM('program1, program2, program3');
END;
/

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the DROP_PROGRAM procedure.

Disabling Programs

You disable one or more programs using the DISABLE procedure or Enterprise Manager. When a program is disabled, the status is changed to disabled. A disabled program implies that, although the metadata is still there, jobs that point to this program cannot run.

Running jobs that point to the program are not affected by the DISABLE call, and are allowed to continue. Any argument that pertains to the program will not be affected when the program is disabled.

A program can also become disabled for other reasons. For example, if a program argument is dropped or number_of_arguments is changed so that all arguments are no longer defined.

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the DISABLE procedure.

Enabling Programs

You enable one or more programs using the ENABLE procedure or Enterprise Manager. When a program is enabled, the enabled flag is set to TRUE. Programs are created disabled by default, therefore, you have to enable them before you can enable jobs that point to them. Before programs are enabled, validity checks are performed to ensure that the action is valid and that all arguments are defined.

You can enable several programs in one call by providing a comma-delimited list of program names to the ENABLE procedure call. For example, the following statement enables three programs:

BEGIN
  DBMS_SCHEDULER.ENABLE('program1, program2, program3');
END;
/

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the ENABLE procedure.