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.
