Programs can be created using the Create Program screen of the OEM DB Control as shown in Figure 11.14.
Figure 11.14 - OEM DB Control: Create Program
Information about programs can be displayed using the dba_scheduler_programs view. The following script uses this view to display basic information about the currently defined programs.
set verify off
select
owner,
program_name,
enabled
from
dba_scheduler_programs
where
owner = decode(upper('&1'), 'ALL', owner, upper('&1'));
The programs.sql script can display all programs or only those programs of a specified user.
OWNER PROGRAM_NAME ENABL
------------------------------ ------------------------------ -----
SYS PURGE_LOG_PROG TRUE
SYS GATHER_STATS_PROG TRUE
JOB_USER TEST_PLSQL_BLOCK_PROG TRUE
JOB_USER TEST_STORED_PROCEDURE_PROG TRUE
JOB_USER TEST_EXECUTABLE_PROG TRUE
OWNER PROGRAM_NAME ENABL
------------------------------ ------------------------------ -----
JOB_USER TEST_PLSQL_BLOCK_PROG TRUE
JOB_USER TEST_STORED_PROCEDURE_PROG TRUE
JOB_USER TEST_EXECUTABLE_PROG TRUE
Information about program arguments can be displayed using the dba_scheduler_program_args view. The following script uses this view to display information about the arguments of currently defined programs.
-- *************************************************
-- Parameters:
-- 1) Specific USERNAME or ALL which doesn't limit output.
-- 2) Program name.
-- *****************************************************************
set verify off
column argument_name format a20
column default_value format a30
select
argument_position,
argument_name,
default_value
from
dba_scheduler_program_args
where
owner = decode(upper('&1'), 'ALL', owner, upper('&1'))
and
program_name = upper('&2');
The output from the program_args.sql script is displayed below.
SQL> @program_args job_user test_stored_procedure_prog
ARGUMENT_POSITION ARGUMENT_NAME DEFAULT_VALUE
----------------- -------------------- -----------------------------
1 P_TEXT This is a default value.
Programs that are no longer used can be removed using the drop_program procedure whose call specification is listed below.
PROCEDURE drop_program (
program_name IN VARCHAR2,
force IN BOOLEAN DEFAULT FALSE)
The parameters associated with this procedure and their usage are as follows:
-
program_name - A name that uniquely identifies the program
-
force - When set to TRUE, all jobs which reference the program are disabled prior to the program being dropped. If set to FALSE and jobs reference the program, an error is produced. In addition, all program arguments information is dropped.
The following examples show how the drop_program procedure is used.
BEGIN
DBMS_SCHEDULER.drop_program (program_name => 'test_plsql_block_prog');
DBMS_SCHEDULER.drop_program (program_name => 'test_stored_procedure_prog');
DBMS_SCHEDULER.drop_program (program_name => 'test_executable_prog');
END;
/
One can determine that the programs have been removed by checking the output of the programs.sql script.
OWNER PROGRAM_NAME ENABL
------------------------------ ------------------------------ -----
SYS PURGE_LOG_PROG TRUE
SYS GATHER_STATS_PROG TRUE
Program information is also available from the OEM DB Control via the Scheduler Programs screen shown in Figure 11.15.
Figure 11.15 - OEM DB Control: Sheduler Programs
Now that defining reusable programs has been explained, the next section will explain the defining of reusable schedules.
and include the URL for the page.
