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 - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem 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