Rem
Rem $Header: schutl.sql 20-mar-2007.14:21:10 rramkiss Exp $
Rem
Rem schutl.sql
Rem
Rem Copyright (c) 2007, Oracle. All rights reserved.
Rem
Rem NAME
Rem schutl.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem <short description of component this file declares/defines>
Rem
Rem NOTES
Rem <other useful comments, qualifications, etc.>
Rem
Rem MODIFIED (MM/DD/YY)
Rem rramkiss 03/14/07 - add e-mail notification
Rem rgmani 03/13/07 - Optional scheduler utilities package
Rem rgmani 03/13/07 - Created
Rem
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--
--
-- PACKAGE scheduler_util
--
--
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE PACKAGE scheduler_util AUTHID CURRENT_USER AS
--
-- PROCEDURE create_file_watch_schedule:
-- ARGUMENTS:
-- schedule_name - Name of schedule to create
-- dir_obj_name - Name of directory object
-- file_name - Name of file to watch for
-- is_prefix - Is this a prefix or a full file name
--
-- Creates the necessary table and queue if this is the first file watch
-- schedule. Adds metadata to the file watch table and creates the schedule.
-- After this, users can create jobs based.
--
PROCEDURE create_file_watch_schedule(
schedule_name IN VARCHAR2,
dir_obj_name IN VARCHAR2,
file_name IN VARCHAR2,
is_prefix IN BOOLEAN);
--
-- PROCEDURE drop_file_watch_schedule:
-- ARGUMENTS:
-- schedule_name - Name of schedule to drop
--
-- Drops the schedule and removes the metadata from the queue. If this is
-- the last file watch schedule for this user, it drops all the database
-- objects that have been created.
--
PROCEDURE drop_file_watch_schedule(
schedule_name IN VARCHAR2);
--
-- PROCEDURE check_for_file_arrival:
-- ARGUMENTS:
--
-- Procedure called by job to check for file arrival.This is one job that
-- checks for all files that the user is interested in. If it finds any
-- then it enqueues a message in the file watch queue.
--
PROCEDURE check_for_file_arrival;
-- PROCEDURE add_job_email_notification:
-- ARGUMENTS:
-- job_name - Name of the job to send e-mail notifications for. Cannot be NULL
-- recipient_addresses - Comma-separated list of e-mail addresses to send
-- notifications to. E-mail notifications for all listed events will
-- be sent to all e-mail addresses provided. This cannot be NULL.
-- events - Comma-separated list of events to e-mail notifications for.
-- E-mail notifications for all specified events will be sent to all
-- e-mail addresses provided. This cannot be NULL.
-- sender_address - E-mail address to use as the sender for e-mail
-- notifications. If the user leaves the default sender address and a
-- valid email_sender_address parameter is specified, that value will be
-- used instead.
-- subject_prefix - This text will be appended to the beginning of the subject
-- line of e-mails sent.
-- filter_condition - This will be used to additionally filter e-mail
-- notifications that are sent. If this is NULL (the default), all listed
-- events will be e-mailed to all specified recipient addresses. The
-- format is a SQL where-clause with :event bound to
-- a scheduler$_event_info type object.
-- For example to send e-mail only when the error number is 600 or 700
-- you can use the following filter_condition:
-- :event.error_code=600 or :event.error_code=700
--
-- This will add job e-mail notifications so that e-mails will be sent to the
-- specified recipient addresses whenever any of the listed events are
-- generated by the job. This will automatically modify the job to raise
-- these events by modifying the raise_events flag. If a filter_condition is
-- given, only events which match the filter_condition will generate an
-- e-mail.
-- This will fail if the email_server parameter is not set. If the job
-- specified does not exist, it is the user's responsibility to ensure that
-- the job will raise the events after it is created by setting the
-- raise_events attribute after creating the job.
--
PROCEDURE add_job_email_notification
(
job_name IN VARCHAR2,
recipient_addresses IN VARCHAR2,
events IN VARCHAR2 DEFAULT
'JOB_FAILED,JOB_BROKEN,JOB_SCH_LIM_REACHED,JOB_CHAIN_STALLED,JOB_OVER_MAX_DUR',
sender_address IN VARCHAR2 DEFAULT NULL,
subject_prefix IN VARCHAR2 DEFAULT 'Oracle Scheduler Job Notification',
filter_condition IN VARCHAR2 DEFAULT NULL);
-- PROCEDURE remove_job_email_notification:
-- ARGUMENTS:
-- job_name - Name of the job to remove e-mail notifications for. This cannot
-- be NULL.
-- recipient_addresses - Comma-separated list of e-mail addresses to remove
-- notifications for. If this is NULL, all notifications for the given
-- job and listed events will be removed.
-- events - Comma-separated list of events to remove e-mail notifications for.
-- If this is NULL, all notifications for the given job and the listed
-- e-mail addresses will be removed.
--
-- This is used to remove one or more e-mail notifications for a given job.
-- It will not modify the job to stop raising the events, but no events will
-- be raised if there are no recipients. The user may reset the event flags
-- in the raise_events job attribute if he is sure that these events are not
-- required or used.
-- If one or both of recipient_addresses or events are comma-separated lists,
-- all matching combinations for the given job will be removed. If both are
-- NULL then all e-mail notifications for the job are removed. job_name cannot
-- be NULL.
--
PROCEDURE remove_job_email_notification
(
job_name IN VARCHAR2,
recipient_addresses IN VARCHAR2 DEFAULT NULL,
events IN VARCHAR2 DEFAULT NULL
);
--
-- PROCEDURE set_sched_util_parameter:
-- ARGUMENTS:
-- parameter_name - Name of the parameter to set
-- parameter_value - Value of parameter
--
-- Checks to see if caller has appropriate privileges and if so sets the
-- parameter to the appropriate value.
PROCEDURE set_sched_util_parameter(
parameter_name IN VARCHAR2,
parameter_value IN VARCHAR2);
--
-- PROCEDURE reset_sched_util_parameter:
-- ARGUMENTS:
-- parameter_name - Name of the parameter to set
--
-- Checks to see if caller has appropriate privileges and if so resets the
-- parameter to the default value.
PROCEDURE reset_sched_util_parameter(
parameter_name IN VARCHAR2);
END scheduler_util;
/
show errors;
CREATE OR REPLACE PUBLIC SYNONYM scheduler_util FOR scheduler_util
/
GRANT EXECUTE ON scheduler_util TO PUBLIC;
/
--
-- Temporary table used by the Java Class schedUtilFileWatchJava to
-- place list of files in directory.
--
CREATE GLOBAL TEMPORARY TABLE SCHEDUTIL$_TEMP_DIR_LIST
( job_owner VARCHAR2(30),
schedule_name VARCHAR2(30),
filename VARCHAR2(255),
last_modified TIMESTAMP )
ON COMMIT DELETE ROWS
/
GRANT SELECT ON SCHEDUTIL$_TEMP_DIR_LIST TO public;
--
-- Configurable parameters table. Right now only one parameter in the
-- table - which is TRACING.
--
CREATE TABLE schedutil$_parameters
( parameter_name VARCHAR2(30) NOT NULL
CONSTRAINT schedutil$_fwpar_pk PRIMARY KEY,
parameter_value VARCHAR2(256));
INSERT INTO schedutil$_parameters VALUES ('TRACING', 'OFF');
INSERT INTO schedutil$_parameters VALUES ('EMAIL_SERVER', NULL);
INSERT INTO schedutil$_parameters VALUES ('EMAIL_SENDER_ADDRESS', NULL);
INSERT INTO schedutil$_parameters VALUES ('FILE_WATCH_RUN_INTV', NULL);
INSERT INTO schedutil$_parameters VALUES ('FORCE_NONZERO_FILSIZ', 'OFF');
COMMIT;
CREATE OR REPLACE VIEW dba_sched_util_parameters AS
SELECT parameter_name, parameter_value FROM schedutil$_parameters
/
COMMENT ON TABLE dba_sched_util_parameters IS
'Scheduler utility package parameters'
/
COMMENT ON COLUMN dba_sched_util_parameters.parameter_name IS
'Scheduler utility package parameter name'
/
COMMENT ON COLUMN dba_sched_util_parameters.parameter_value IS
'Scheduler utility package parameter value'
/
CREATE OR REPLACE PUBLIC SYNONYM dba_sched_util_parameters
FOR dba_sched_util_parameters
/
GRANT SELECT ON dba_sched_util_parameters TO select_catalog_role
/
CREATE OR REPLACE VIEW all_sched_util_parameters AS
SELECT parameter_name, parameter_value FROM schedutil$_parameters
/
COMMENT ON TABLE all_sched_util_parameters IS
'Scheduler utility package parameters'
/
COMMENT ON COLUMN all_sched_util_parameters.parameter_name IS
'Scheduler utility package parameter name'
/
COMMENT ON COLUMN all_sched_util_parameters.parameter_value IS
'Scheduler utility package parameter value'
/
CREATE OR REPLACE PUBLIC SYNONYM all_sched_util_parameters
FOR all_sched_util_parameters
/
GRANT SELECT ON all_sched_util_parameters TO public WITH GRANT OPTION
/
--
-- This table is used to maintain a list of the schedules that we
-- have created. This way, we are sure that when we are dropping
-- the schedule, it is one of the schedules that was created by this
-- package
--
CREATE TABLE schedutil$_schedule_list
( schedule_owner VARCHAR2(30),
schedule_name VARCHAR2(30),
CONSTRAINT schedutil$_slist_pk
PRIMARY KEY (schedule_owner, schedule_name));
-- list of all notifications being maintained by this package
CREATE TABLE schedutil$_notifications
( job_name VARCHAR2(30) NOT NULL,
job_subname VARCHAR2(30),
owner VARCHAR2(30) NOT NULL,
recipient_address VARCHAR2(400) NOT NULL,
event_flag NUMBER NOT NULL,
sender_address VARCHAR2(400),
subject_prefix VARCHAR2(100),
filter_condition VARCHAR2(4000));
/* this speeds up queries on user_s_n */
CREATE INDEX i_schedutil_notifications1
ON schedutil$_notifications (job_name, owner);
/* this speeds up the main query in the AQ PL/SQL callback */
CREATE INDEX i_schedutil_notifications2
ON schedutil$_notifications (job_name, owner, event_flag);
/* this speeds up selects on user_s_n and removing notifications */
CREATE INDEX i_schedutil_notifications3
ON schedutil$_notifications (owner);
CREATE OR REPLACE VIEW dba_sched_util_notifications
(OWNER,JOB_NAME,RECIPIENT_ADDRESS,EVENT,SENDER_ADDRESS,SUBJECT_PREFIX,
FILTER_CONDITION,EVENT_FLAG)
AS SELECT sr.owner, sr.job_name , sr.recipient_address,
DECODE(BITAND(sr.event_flag,1024-1),1,'JOB_STARTED',2,'JOB_SUCCEEDED',
4,'JOB_FAILED',8,'JOB_BROKEN',16,'JOB_COMPLETED',32,'JOB_STOPPED',
64,'JOB_SCH_LIM_REACHED',128,'JOB_DISABLED',256,'JOB_CHAIN_STALLED',
512,'JOB_OVER_MAX_DUR', NULL),
sr.sender_address, sr.subject_prefix, sr.filter_condition, sr.event_flag
FROM schedutil$_notifications sr
/
COMMENT ON TABLE dba_sched_util_notifications IS
'All e-mail notification recipients in the database'
/
COMMENT ON COLUMN dba_sched_util_notifications.owner IS
'Owner of the job to send notifications for'
/
COMMENT ON COLUMN dba_sched_util_notifications.job_name IS
'Name of the job to send notifications for'
/
COMMENT ON COLUMN dba_sched_util_notifications.recipient_address IS
'E-mail address to send this e-mail notification to'
/
COMMENT ON COLUMN dba_sched_util_notifications.event IS
'Job event to send notifications for'
/
COMMENT ON COLUMN dba_sched_util_notifications.sender_address IS
'E-mail address to send this e-mail notification from'
/
COMMENT ON COLUMN dba_sched_util_notifications.subject_prefix IS
'Subject prefix to use for notification e-mails'
/
COMMENT ON COLUMN dba_sched_util_notifications.filter_condition IS
'Filter specifying which events to send notifications for'
/
COMMENT ON COLUMN dba_sched_util_notifications.event_flag IS
'Flag for job event to send notifications for'
/
CREATE OR REPLACE PUBLIC SYNONYM dba_sched_util_notifications
FOR dba_sched_util_notifications
/
GRANT SELECT ON dba_sched_util_notifications TO select_catalog_role
/
CREATE OR REPLACE VIEW user_sched_util_notifications
(JOB_NAME,RECIPIENT_ADDRESS,EVENT,SENDER_ADDRESS,SUBJECT_PREFIX,
FILTER_CONDITION,EVENT_FLAG)
AS SELECT sr.job_name , sr.recipient_address,
DECODE(BITAND(sr.event_flag,1024-1),1,'JOB_STARTED',2,'JOB_SUCCEEDED',
4,'JOB_FAILED',8,'JOB_BROKEN',16,'JOB_COMPLETED',32,'JOB_STOPPED',
64,'JOB_SCH_LIM_REACHED',128,'JOB_DISABLED',256,'JOB_CHAIN_STALLED',
512,'JOB_OVER_MAX_DUR', NULL),
sr.sender_address, sr.subject_prefix, sr.filter_condition, sr.event_flag
FROM schedutil$_notifications sr
WHERE sr.owner = sys_context('USERENV', 'CURRENT_USER')
/
COMMENT ON TABLE user_sched_util_notifications IS
'All e-mail notification recipients for jobs owner by this user'
/
COMMENT ON COLUMN user_sched_util_notifications.job_name IS
'Name of the job to send notifications for'
/
COMMENT ON COLUMN user_sched_util_notifications.recipient_address IS
'E-mail address to send this e-mail notification to'
/
COMMENT ON COLUMN user_sched_util_notifications.event IS
'Job event to send notifications for'
/
COMMENT ON COLUMN user_sched_util_notifications.sender_address IS
'E-mail address to send this e-mail notification from'
/
COMMENT ON COLUMN user_sched_util_notifications.subject_prefix IS
'Subject prefix to use for notification e-mails'
/
COMMENT ON COLUMN user_sched_util_notifications.filter_condition IS
'Filter specifying which events to send notifications for'
/
COMMENT ON COLUMN user_sched_util_notifications.event_flag IS
'Flag for job event to send notifications for'
/
CREATE OR REPLACE PUBLIC SYNONYM user_sched_util_notifications
FOR user_sched_util_notifications
/
GRANT SELECT ON user_sched_util_notifications TO public WITH GRANT OPTION;
/
CREATE OR REPLACE VIEW all_sched_util_notifications
(OWNER,JOB_NAME,RECIPIENT_ADDRESS,EVENT,SENDER_ADDRESS,SUBJECT_PREFIX,
FILTER_CONDITION,EVENT_FLAG)
AS SELECT sr.owner, sr.job_name , sr.recipient_address,
DECODE(BITAND(sr.event_flag,1024-1),1,'JOB_STARTED',2,'JOB_SUCCEEDED',
4,'JOB_FAILED',8,'JOB_BROKEN',16,'JOB_COMPLETED',32,'JOB_STOPPED',
64,'JOB_SCH_LIM_REACHED',128,'JOB_DISABLED',256,'JOB_CHAIN_STALLED',
512,'JOB_OVER_MAX_DUR', NULL),
sr.sender_address, sr.subject_prefix, sr.filter_condition, sr.event_flag
FROM schedutil$_notifications sr
WHERE
(sr.owner = sys_context('USERENV', 'CURRENT_USER')
or exists
(select null
from sys.objauth$ oa, sys.obj$ o, sys.user$ u
where oa.grantee# in ( select kzsrorol
from x$kzsro
)
and oa.obj#=o.obj# and o.owner#=u.user#
and o.name=sr.job_name and u.name=sr.owner
)
or /* user has system privileges */
(exists (select null from v$enabledprivs
where priv_number = -265 /* CREATE ANY JOB */
)
and sr.owner!='SYS'
)
)
/
COMMENT ON TABLE all_sched_util_notifications IS
'All e-mail notification recipients accessible to the current user'
/
COMMENT ON COLUMN all_sched_util_notifications.owner IS
'Owner of the job to send notifications for'
/
COMMENT ON COLUMN all_sched_util_notifications.job_name IS
'Name of the job to send notifications for'
/
COMMENT ON COLUMN all_sched_util_notifications.recipient_address IS
'E-mail address to send this e-mail notification to'
/
COMMENT ON COLUMN all_sched_util_notifications.event IS
'Job event to send notifications for'
/
COMMENT ON COLUMN all_sched_util_notifications.sender_address IS
'E-mail address to send this e-mail notification from'
/
COMMENT ON COLUMN all_sched_util_notifications.subject_prefix IS
'Subject prefix to use for notification e-mails'
/
COMMENT ON COLUMN all_sched_util_notifications.filter_condition IS
'Filter specifying which events to send notifications for'
/
COMMENT ON COLUMN all_sched_util_notifications.event_flag IS
'Flag for job event to send notifications for'
/
CREATE OR REPLACE PUBLIC SYNONYM all_sched_util_notifications
FOR all_sched_util_notifications
/
GRANT SELECT ON all_sched_util_notifications TO public WITH GRANT OPTION;
/
--
-- Job class used for file watch jobs
--
BEGIN
dbms_scheduler.create_job_class(
job_class_name => 'sched_util_file_watch_class');
END;
/
GRANT EXECUTE ON sched_util_file_watch_class to public;
--
-- Type used for queue in which file watch events are raised
--
CREATE OR REPLACE TYPE sched_util_filewatch_type AS OBJECT
(
requesting_user VARCHAR2(30),
dir_obj_name VARCHAR2(65),
monitored_file_name VARCHAR2(256),
is_prefix VARCHAR2(1),
actual_file_name VARCHAR2(512),
file_size NUMBER,
arrival_time TIMESTAMP WITH TIME ZONE,
CONSTRUCTOR FUNCTION sched_util_filewatch_type (
requesting_user VARCHAR2,
dir_obj_name VARCHAR2,
monitored_file_name VARCHAR2,
is_prefix VARCHAR2,
actual_file_name VARCHAR2,
file_size NUMBER,
arrival_time TIMESTAMP WITH TIME ZONE)
RETURN SELF AS RESULT
);
/
GRANT EXECUTE ON sched_util_filewatch_type TO PUBLIC;
-- finally load the internal package and package bodies
@@schutlpb.plb