Using Programs

Program Tasks and Their Procedures

Table 27-2 illustrates common program tasks and their appropriate procedures and privileges:

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;
/

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;
/

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;
/

Altering Programs

You can use Enterprise Manager or the DBMS_SCHEDULER.SET_ATTRIBUTE and DBMS_SCHEDULER.SET_ATTRIBUTE_NULL package procedures to alter programs. See Oracle Database PL/SQL Packages and Types Reference for more information on the DBMS_SCHEDULER package procedures. The following are instructions for altering a program with Enterprise Manager:

1. Access the Database Home page.

2. At the top of the page, click Server to display the Server page.

3. In the Oracle Scheduler section, click Programs

   The Scheduler Programs page appears. It displays existing programs.

4. Select a program, and then click Edit.

   The Edit Program page appears.

5. Next to the Enabled heading, select Yes or No.

6. In the Description field, change any comments.

7. From the Type drop-down list, select one of the following:

   • PLSQL_BLOCK

     A Source field appears. Enter or alter the PL/SQL code in this field.

   • STORED_PROCEDURE

     A Procedure Name field appears. If the field contains a stored procedure name, click View Procedure to view or edit the stored procedure. If the field is blank, or if you want to change stored procedures, click Select Procedure. A Select Procedure page then appears. Select a stored procedure and then click Select to return to the Edit Program page. (Click Help at the top of the page for help with using the Select Procedure page.)

     With a procedure name selected, a list of arguments appears under the Arguments heading on the Edit Program page. Optionally enter default values for one or more arguments.

   • EXECUTABLE

     An Executable Name field appears. Enter the full path of the executable. Under the Arguments heading, edit or delete arguments, or click Add Another Row to add an argument.

8. Click Apply to save your changes.

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

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.