-- Run this script as SYS with 2 parameters, an outgoing e-mail SMTP server
-- and port (normally 25) for example:
-- SQL> connect sys/sys_password as sysdba
-- SQL> @job_notification smtp.example.com 25
--
-- This script creates 2 procedures to let users setup e-mail notification for
-- jobs they have access to and grants execute on these to public. It also
-- creates public synonyms for these procedures.
-- add_job_email_notification
-- remove_job_email_notification
--
-- It also creates a utility program and procedure
-- sys.email_notification_program
-- sys.email_notification_procedure
--
-- WARNING If any of these objects exist, they will be replaced.
--
-- To remove the objects created by this script, run the accompanying
-- remove_job_notification.sql script. This script also removes any job
-- e-mail notifications that have been added.
-- DBA either define the below variables or pass them into the script
prompt Enter an outgoing e-mail SMTP server host
define email_server_host = &1
prompt Enter an outgoing e-mail SMTP server port
define email_server_port = &2
-- this must be run as SYS (to do privilege checking) so check this
begin
if sys_context('USERENV','SESSION_USER') != 'SYS' then
raise_application_error(-20000, 'This script must be run AS SYS');
end if;
end;
/
-- create a utility procedure to send e-mail for a scheduler job event
create or replace procedure sys.email_notification_procedure
( message IN sys.scheduler$_event_info,
recipient_address IN VARCHAR2,
email_server_host IN VARCHAR2,
sender_address IN VARCHAR2,
subject_prefix IN VARCHAR2,
email_server_port IN PLS_INTEGER) AS
msg_text VARCHAR2(4000);
mail_conn utl_smtp.connection;
all_addresses VARCHAR2(4000) := ','||recipient_address;
single_address VARCHAR2(500);
BEGIN
-- compose the message
msg_text := 'Job: "'||message.object_owner||'"."'||message.object_name||'"'||
UTL_TCP.CRLF ||
'Event: '|| message.event_type || UTL_TCP.CRLF ||
'Date: '|| regexp_replace(message.event_timestamp,'\.[0-9]{3,}') ||
UTL_TCP.CRLF;
IF message.log_id IS NOT NULL AND message.log_id != 0 THEN
msg_text := msg_text || 'Log ID: '||message.log_id||UTL_TCP.CRLF;
END IF;
IF message.error_code IS NOT NULL AND message.error_code != 0 THEN
msg_text := msg_text ||
'Error code: ' || message.error_code|| UTL_TCP.CRLF ||
'Error message: ' || UTL_TCP.CRLF || message.error_msg;
END IF;
-- now send the e-mail
mail_conn := utl_smtp.open_connection(email_server_host,email_server_port);
utl_smtp.helo(mail_conn, email_server_host);
utl_smtp.mail(mail_conn, sender_address);
single_address := regexp_substr(all_addresses,'^,[^,]*');
all_addresses := regexp_replace(all_addresses,'^,[^,]*');
WHILE single_address IS NOT NULL AND
regexp_substr(single_address, '[[:graph:]]') IS NOT NULL
LOOP
utl_smtp.rcpt(mail_conn, replace(single_address,','));
single_address := regexp_substr(all_addresses,'^,[^,]*');
all_addresses := regexp_replace(all_addresses,'^,[^,]*');
END LOOP;
utl_smtp.open_data(mail_conn);
utl_smtp.write_data(mail_conn, 'From: ' || sender_address || UTL_TCP.CRLF);
utl_smtp.write_data(mail_conn, 'To: ' || recipient_address || UTL_TCP.CRLF);
utl_smtp.write_data(mail_conn, 'Subject: ' || subject_prefix || ' - ' || message.object_owner || '.' || message.object_name || ' ' || message.event_type || UTL_TCP.CRLF || UTL_TCP.CRLF);
utl_smtp.write_data(mail_conn, msg_text || UTL_TCP.CRLF);
utl_smtp.close_data(mail_conn);
utl_smtp.quit(mail_conn);
END;
/
show errors
grant execute on sys.email_notification_procedure to public;
-- drop any existing email_notification_program, suppressing all errors
begin
dbms_scheduler.drop_program (
program_name => 'sys.email_notification_program',
force => true);
exception when others then null;
end;
/
-- create a utility program to send e-mail for a scheduler job event
begin
dbms_scheduler.create_program (
program_name => 'sys.email_notification_program',
program_action=> 'sys.email_notification_procedure ',
program_type => 'STORED_PROCEDURE',
number_of_arguments => 6,
enabled => FALSE) ;
dbms_scheduler.define_metadata_argument (
program_name => 'email_notification_program',
argument_position => 1 ,
argument_name => 'message',
metadata_attribute => 'EVENT_MESSAGE') ;
dbms_scheduler.define_program_argument (
program_name => 'email_notification_program',
argument_position => 2,
argument_name => 'recipient_address',
argument_type => 'varchar2') ;
dbms_scheduler.define_program_argument (
program_name => 'email_notification_program',
argument_position => 3,
argument_name => 'email_server_host',
argument_type => 'varchar2') ;
dbms_scheduler.define_program_argument (
program_name => 'email_notification_program',
argument_position => 4,
argument_name => 'sender_address',
argument_type => 'varchar2') ;
dbms_scheduler.define_program_argument (
program_name => 'email_notification_program',
argument_position => 5,
argument_name => 'subject_prefix',
argument_type => 'varchar2') ;
dbms_scheduler.define_program_argument (
program_name => 'email_notification_program',
argument_position => 6,
argument_name => 'email_server_port',
argument_type => 'number') ;
dbms_scheduler.enable ('email_notification_program');
end ;
/
grant execute on sys.email_notification_program to public;
-- Sets up e-mail notification for selected job events
-- for a given job.
-- This creates an e-mailer job in the job owner's schema
-- The default events chosen for e-mail notification are:
-- JOB_FAILED,JOB_BROKEN,JOB_SCH_LIM_REACHED,JOB_CHAIN_STALLED
-- The caller must be the job owner or have alter privileges on the job.
create or replace procedure sys.add_job_email_notification
(
job_name IN VARCHAR2,
recipient_address IN VARCHAR2,
events IN VARCHAR2 DEFAULT
'JOB_SUCCEEDED, JOB_FAILED,JOB_BROKEN,JOB_SCH_LIM_REACHED,JOB_CHAIN_STALLED',
sender_address IN VARCHAR2 DEFAULT 'noreply@apexsoftcell.com',
subject_prefix IN VARCHAR2 DEFAULT 'Oracle Scheduler Job Notification',
email_server_host IN VARCHAR2 DEFAULT '&email_server_host',
email_server_port IN PLS_INTEGER DEFAULT &email_server_port
) AS
events_on NUMBER := 0;
job_object VARCHAR2(35);
job_owner VARCHAR2(35);
canon_job_name VARCHAR2(30);
canon_job_owner VARCHAR2(30);
caller VARCHAR2(30) := sys_context('USERENV','SESSION_USER');
notifier_job_name VARCHAR2(30);
new_raise_events NUMBER := 0;
cur_raise_events VARCHAR2(200);
priv_count NUMBER;
event_condition VARCHAR2(4000);
comments_text VARCHAR2(100) :=
'Auto-generated job to send email alerts for job ';
type event_name_list is table of varchar2(30);
type event_number_list is table of number;
event_names event_name_list := event_name_list('JOB_STARTED',
'JOB_SUCCEEDED', 'JOB_FAILED', 'JOB_BROKEN', 'JOB_COMPLETED',
'JOB_STOPPED', 'JOB_SCH_LIM_REACHED', 'JOB_DISABLED',
'JOB_CHAIN_STALLED', 'JOB_OVER_MAX_DUR');
event_numbers event_number_list :=
event_number_list(1,2,4,8,16,32,64,128,256,512);
event_found pls_integer := 0;
event_10862 pls_integer := 0;
begin
-- get job name and owner
sys.dbms_isched.resolve_name(job_name, job_object, job_owner, caller);
-- canonicalize job name and owner
dbms_utility.canonicalize(job_object, canon_job_name, 30);
dbms_utility.canonicalize(job_owner, canon_job_owner, 30);
comments_text := comments_text ||'"'||canon_job_owner ||'"."'||canon_job_name||'"' ;
-- check if the caller has privileges on the job
-- check if the caller is the job owner or 'SYS'
IF canon_job_owner = caller or caller = 'SYS' THEN
goto privilege_check_passed;
END IF;
-- check whether the caller has been granted ALTER on the job or
-- CREATE ANY JOB or SCHEDULER_ADMIN or DBA directly
select count(*) into priv_count from dba_sys_privs where grantee=caller and
privilege='CREATE ANY JOB';
IF priv_count > 0 THEN goto privilege_check_passed; END IF;
select count(*) into priv_count from dba_role_privs where grantee=caller and
granted_role='SCHEDULER_ADMIN';
IF priv_count > 0 THEN goto privilege_check_passed; END IF;
select count(*) into priv_count from dba_tab_privs where grantee=caller and
owner=canon_job_owner and table_name=canon_job_name;
IF priv_count > 0 THEN goto privilege_check_passed; END IF;
-- recursive privileges check for CREATE ANY JOB system priv
-- includes a recursive roles check so SCHEDULER_ADMIN will also work
-- this is slow but all simple privilege checks have failed
select count(*) into priv_count from (
select grantee, granted from
(
/* roles granted */
select grantee, granted_role granted from dba_role_privs
/* system privileges granted */
union
select grantee, privilege granted from dba_sys_privs
)
start with grantee = caller connect by grantee = prior granted )
where granted = 'CREATE ANY JOB';
IF priv_count > 0 THEN goto privilege_check_passed; END IF;
-- recursive privileges check whether the caller has object privileges on the job
-- this is slow but all simple privilege checks have failed
select count(*) into priv_count from (
select * from
(
/* object privileges granted */
select table_name g1, owner g2, grantee obj, grantee own, privilege typ
from dba_tab_privs
/* role privileges granted */
union
select granted_role g1, granted_role g2, grantee, grantee, null
from dba_role_privs
)
start with g1 = canon_job_name and g2 = canon_Job_owner
connect by g1 = prior obj and g2 = prior own)
where obj=caller;
IF priv_count > 0 THEN goto privilege_check_passed; END IF;
-- no privileges, throw job_does_exist error
dbms_sys_error.raise_system_error(-23308, canon_job_owner, canon_job_name, TRUE);
<<privilege_check_passed>>
-- retrieve current events turned on. cast NO_DATA_FOUND to job not found
begin
select raise_events into cur_raise_events from dba_scheduler_jobs where
job_name=canon_job_name and owner=canon_job_owner ;
exception when no_data_found then
dbms_sys_error.raise_system_error(-23308, canon_job_owner, canon_job_name,
TRUE);
when others then raise;
end;
-- generate event_condition
event_condition := 'tab.user_data.object_owner = '''||canon_job_owner||
''' AND tab.user_data.object_name = '''||canon_job_name|| '''';
if instr(UPPER(events),'JOB_ALL_EVENTS')>0 then
-- by default we have no events clause so all events will trigger an e-mail
event_found := 1;
else
event_condition := event_condition ||' AND tab.user_data.event_type in (';
for i in event_names.first..event_names.last loop
if instr(UPPER(events),event_names(i))>0 then
event_condition := event_condition || ''''||event_names(i)||''',';
event_found := 1;
end if;
end loop;
if instr(UPPER(events),'JOB_RUN_COMPLETED')>0 then
event_condition := event_condition ||
'''JOB_SUCCEEDED'',''JOB_FAILED'',''JOB_STOPPED'',';
event_found := 1;
end if;
-- strip last comma and add close brace
event_condition := regexp_replace(event_condition, ',$');
event_condition := event_condition || ')';
end if;
-- if no events have been specified, throw an error
if event_found = 0 then
dbms_sys_error.raise_system_error(-24098, events, 'EVENTS',TRUE);
end if;
-- collect all events to turn on
if cur_raise_events is null then
cur_raise_events := UPPER(events) ;
else
cur_raise_events := UPPER(events) ||','||UPPER(cur_raise_events) ;
end if;
for i in event_names.first..event_names.last loop
if instr(cur_raise_events,event_names(i))>0 then
new_raise_events := new_raise_events + event_numbers(i);
end if;
end loop;
if instr(cur_raise_events,'JOB_RUN_COMPLETED')>0 then
new_raise_events := new_raise_events -
bitand(new_raise_events,sys.dbms_scheduler.job_run_completed) +
sys.dbms_scheduler.job_run_completed;
end if;
if instr(cur_raise_events,'JOB_ALL_EVENTS')>0 then
new_raise_events := new_raise_events -
bitand(new_raise_events,sys.dbms_scheduler.job_all_events) +
sys.dbms_scheduler.job_all_events;
end if;
-- turn on events the user is interested in
dbms_scheduler.set_attribute
( '"'||canon_job_owner||'"."'||canon_job_name||'"' , 'raise_events' ,
new_raise_events);
-- set event 10862 if not set so that we can add a subscriber
-- this is necessary because if event 10862 is not set then AQ is in backward
-- compatibility mode which checks the login user instead of the current user
-- for privileges.
dbms_system.read_ev(10862, event_10862);
IF event_10862 = 0 THEN
EXECUTE IMMEDIATE
'ALTER SESSION SET EVENTS ''10862 TRACE NAME CONTEXT FOREVER, LEVEL 1''';
END IF;
-- add a new subscriber for this notification
BEGIN
dbms_aqadm.add_subscriber
(queue_name => 'SYS.SCHEDULER$_EVENT_QUEUE',
subscriber => sys.aq$_agent(canon_job_owner, NULL, NULL),
rule => 'tab.user_data.object_owner = '''||canon_job_owner||'''');
EXCEPTION WHEN others then
-- unset event 10862, if we set it above
IF event_10862 = 0 THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ' ||
'''10862 TRACE NAME CONTEXT OFF''' ;
END IF;
if sqlcode = -24034 then NULL;
else raise;
end if;
end;
-- unset event 10862, if we set it above
IF event_10862 = 0 THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ' ||
'''10862 TRACE NAME CONTEXT OFF''' ;
END IF;
-- allow the job owner to access our events queue via this subscriber
dbms_aqadm.enable_db_access(canon_job_owner,'"'||canon_job_owner||'"');
-- if this procedure has been run already for this job, drop previously
-- created email notification jobs. One of the parameters might have changed
-- and existing email notification jobs might have been altered or broken.
FOR old_notify_job IN
( SELECT '"'||owner||'"."'||job_name ||'"' name FROM dba_scheduler_jobs WHERE
owner = canon_job_owner and job_name like substr(canon_job_name,1,10) || '_EMAILER%'
and comments = comments_text and program_owner = 'SYS'
and program_name = 'EMAIL_NOTIFICATION_PROGRAM'
)
LOOP
dbms_scheduler.drop_job(old_notify_job.name);
END LOOP;
-- now create a notifier job which waits on job events
BEGIN
-- try using a simple name without an added number suffix
notifier_job_name := substr(canon_job_name,1,10) || '_EMAILER' ;
notifier_job_name := '"' || canon_job_owner || '"."' || notifier_job_name ||'"' ;
dbms_scheduler.create_job(notifier_job_name,
program_name => 'sys.email_notification_program',
event_condition => event_condition,
queue_spec =>'sys.scheduler$_event_queue,"'
|| canon_job_owner||'"',
comments => comments_text);
EXCEPTION WHEN OTHERS THEN
IF sqlcode != -27477 THEN RAISE; END IF;
-- a job already exists using our simple name, add a numerical suffix
notifier_job_name :=
dbms_scheduler.generate_job_name( '"'||substr(canon_job_name,1,10)||'_EMAILER"');
notifier_job_name := '"' || canon_job_owner || '"."' || notifier_job_name ||'"' ;
dbms_scheduler.create_job(notifier_job_name,
program_name => 'sys.email_notification_program',
event_condition => event_condition,
queue_spec =>'sys.scheduler$_event_queue,"'
|| canon_job_owner||'"',
comments => comments_text);
END;
dbms_scheduler.set_job_argument_value(notifier_job_name,2,
recipient_address);
dbms_scheduler.set_job_argument_value(notifier_job_name,3,
email_server_host);
dbms_scheduler.set_job_argument_value(notifier_job_name,4,
sender_address);
dbms_scheduler.set_job_argument_value(notifier_job_name,5,
subject_prefix);
dbms_scheduler.set_job_argument_value(notifier_job_name,6,
to_char(email_server_port));
dbms_scheduler.enable(notifier_job_name);
end;
/
show errors
create or replace public synonym add_job_email_notification for
sys.add_job_email_notification;
grant execute on sys.add_job_email_notification to public;
-- Removes e-mail notification for a given job.
-- This removes the e-mailer job that was created for the job in the job
-- owner's schema.
-- It does not remove the agent created for the job owner or reset raise_events
-- for the given job.
-- The caller must be the job owner or have alter privileges on the given job.
create or replace procedure sys.remove_job_email_notification
(
job_name IN VARCHAR2) AS
job_object VARCHAR2(35);
job_owner VARCHAR2(35);
canon_job_name VARCHAR2(30);
canon_job_owner VARCHAR2(30);
caller VARCHAR2(30) := sys_context('USERENV','SESSION_USER');
notifier_job_name VARCHAR2(30);
priv_count NUMBER;
comments_text VARCHAR2(100) :=
'Auto-generated job to send email alerts for job ';
begin
-- get job name and owner
sys.dbms_isched.resolve_name(job_name, job_object, job_owner, caller);
-- canonicalize job name and owner
dbms_utility.canonicalize(job_object, canon_job_name, 30);
dbms_utility.canonicalize(job_owner, canon_job_owner, 30);
comments_text := comments_text ||'"'||canon_job_owner ||'"."'||canon_job_name||'"' ;
-- check if the caller has privileges on the job
-- check if the caller is the job owner or 'SYS'
IF canon_job_owner = caller or caller = 'SYS' THEN
goto privilege_check_passed;
END IF;
-- check whether the caller has been granted ALTER on the job or
-- CREATE ANY JOB or SCHEDULER_ADMIN or DBA directly
select count(*) into priv_count from dba_sys_privs where grantee=caller and
privilege='CREATE ANY JOB';
IF priv_count > 0 THEN goto privilege_check_passed; END IF;
select count(*) into priv_count from dba_role_privs where grantee=caller and
granted_role='SCHEDULER_ADMIN';
IF priv_count > 0 THEN goto privilege_check_passed; END IF;
select count(*) into priv_count from dba_tab_privs where grantee=caller and
owner=canon_job_owner and table_name=canon_job_name;
IF priv_count > 0 THEN goto privilege_check_passed; END IF;
-- recursive privileges check for CREATE ANY JOB system priv
-- includes a recursive roles check so SCHEDULER_ADMIN will also work
-- this is slow but all simple privilege checks have failed
select count(*) into priv_count from (
select grantee, granted from
(
/* roles granted */
select grantee, granted_role granted from dba_role_privs
/* system privileges granted */
union
select grantee, privilege granted from dba_sys_privs
)
start with grantee = caller connect by grantee = prior granted )
where granted = 'CREATE ANY JOB';
IF priv_count > 0 THEN goto privilege_check_passed; END IF;
-- recursive privileges check whether the caller has object privileges on the job
-- this is slow but all simple privilege checks have failed
select count(*) into priv_count from (
select * from
(
/* object privileges granted */
select table_name g1, owner g2, grantee obj, grantee own, privilege typ
from dba_tab_privs
/* role privileges granted */
union
select granted_role g1, granted_role g2, grantee, grantee, null
from dba_role_privs
)
start with g1 = canon_job_name and g2 = canon_Job_owner
connect by g1 = prior obj and g2 = prior own)
where obj=caller;
IF priv_count > 0 THEN goto privilege_check_passed; END IF;
-- no privileges, throw job_does_exist error
dbms_sys_error.raise_system_error(-23308, canon_job_owner, canon_job_name,
TRUE);
<<privilege_check_passed>>
-- drop created email notification jobs.
FOR old_notify_job IN
( SELECT '"'||owner||'"."'||job_name ||'"' name FROM dba_scheduler_jobs WHERE
owner = canon_job_owner and job_name like substr(canon_job_name,1,10) || '_EMAILER%'
and comments = comments_text and program_owner = 'SYS'
and program_name = 'EMAIL_NOTIFICATION_PROGRAM'
)
LOOP
dbms_scheduler.drop_job(old_notify_job.name);
END LOOP;
end;
/
show errors
create or replace public synonym remove_job_email_notification for
sys.remove_job_email_notification;
grant execute on sys.remove_job_email_notification to public;
----------------------------------------------------end job_notification.sql---------------------
exec add_job_email_notification(job_name => 'EXPORT_JOB', subject_prefix => 'Apex Job Notification', recipient_address => 'kshitij@apexsoftcell.com',sender_address =>'info@apexsoftcell.com', email_server_host => 'mail.apexsoftcell.com', email_server_port => 25);
exec DBMS_SCHEDULER.run_job ('EXPORT_JOB');
----------------------------------------------------start
remove_job_notification.sql
--------------------
-- Run this script as SYS for example:
-- SQL> connect sys/sys_password as sysdba
-- SQL> @remove_job_notification
-- This script removes all objects created by the accompanying
-- job_notification.sql script. It also removes any job e-mail
-- notifications that have been added.
-- this must be run as SYS (to do privilege checking) so check this
begin
if sys_context('USERENV','SESSION_USER') != 'SYS' then
raise_application_error(-20000, 'This script must be run AS SYS');
end if;
end;
/
drop procedure sys.email_notification_procedure;
begin
dbms_scheduler.drop_program (
program_name => 'sys.email_notification_program',
force => true);
end;
/
drop public synonym add_job_email_notification;
drop procedure sys.add_job_email_notification;
drop public synonym remove_job_email_notification;
drop procedure sys.remove_job_email_notification;
-- now remove any e-mailer jobs that have been created
-- They won't run anyway since the program they point to has been removed.
BEGIN
FOR old_notify_job IN
( SELECT '"'||owner||'"."'||job_name ||'"' name FROM dba_scheduler_jobs WHERE
job_name like '%_EMAILER%'
and comments like 'Auto-generated job to send email alerts for job%'
and program_owner = 'SYS'
and program_name = 'EMAIL_NOTIFICATION_PROGRAM'
)
LOOP
dbms_scheduler.drop_job(old_notify_job.name, TRUE);
END LOOP;
END;
/
----------------------------------------------------end
remove_job_notification.sql
--------------------
No comments:
Post a Comment