Oracle® Database Administrator's Guide 11g Release 1 (11.1) Part Number B28310-01 |
|
|
View PDF |
A program is a collection of metadata about a particular task. This section introduces you to basic program tasks, and discusses the following topics:
See Also:
"Programs" for an overview of programs.Table 27-2 illustrates common program tasks and their appropriate procedures and privileges:
Table 27-2 Program Tasks and Their Procedures
Task | Procedure | Privilege Needed |
---|---|---|
Create a program |
|
|
Alter a program |
|
|
Drop a program |
|
|
Disable a program |
|
|
Enable a program |
|
|
See "Scheduler Privileges" for further information regarding privileges.
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; /
After creating a program, you can define a name or default value for each program argument. 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; /
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.
See Also:
Oracle Database PL/SQL Packages and Types Reference for detailed information about theDEFINE_PROGRAM_ARGUMENT
, DEFINE_ANYDATA_ARGUMENT
, and DEFINE_METADATA_ARGUMENT
proceduresYou 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:
Access the Database Home page.
At the top of the page, click Server to display the Server page.
In the Oracle Scheduler section, click Programs
The Scheduler Programs page appears. It displays existing programs.
Select a program, and then click Edit.
The Edit Program page appears.
Next to the Enabled heading, select Yes or No.
In the Description field, change any comments.
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.
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.
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.
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.
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.