-
Help with procedure to escalate emails
What are some suggestions to modify this procedure so if an alert gets sent more than 2 times the message changes i.e. gets escalated. Would the best way through a counter or something other method? Sent emails aren't tracked in a table to compare current and previous records.
create or replace PROCEDURE EMAIL_STATUS_CAP
IS
current_process_status VARCHAR2 (8);
new_process_status VARCHAR2 (8);
prior_process_status VARCHAR2 (8);
process_state VARCHAR2 (200);
capture_avail NUMBER;
process_name VARCHAR2 (100);
email VARCHAR2 (50);
rec NUMBER;
r NUMBER;
MESSAGE VARCHAR2 (200);
subject VARCHAR2 (100);
dyn_query_string VARCHAR2 (2000);
inst VARCHAR (9);
CURSOR capture_c IS
SELECT capture_name, status
FROM dba_capture;
BEGIN
SELECT COUNT (*)
INTO capture_avail
FROM dba_capture;
IF capture_avail > 0 THEN
FOR rec IN capture_c
LOOP
current_process_status := rec.status;
process_name := rec.capture_name;
dyn_query_string := 'SELECT prev_status from
repl_process ' ||
' WHERE stream_name=''' || process_name || '''';
EXECUTE IMMEDIATE dyn_query_string INTO prior_process_status;
--Send email
IF current_process_status <> prior_process_status THEN
dyn_query_string := 'select status from dba_capture where
capture_name='''||process_name||'''';
EXECUTE IMMEDIATE dyn_query_string into new_process_status;
IF new_process_status = 'ENABLED' THEN
MESSAGE := 'The status of the capture process ' ||
process_name || ' was ' ||
prior_process_status || '' || '.';
MESSAGE := MESSAGE || ' The process is now enabled.';
ELSE
MESSAGE := 'The status of the capture process ' ||
process_name || ' is ' ||
current_process_status || '' || '.';
END IF;
subject := 'Capture Process Down on ';
strmmon.repl_send_email (MESSAGE,subject);
END IF; --current_process_status <> prior_process_status
dyn_query_string := 'SELECT state from repl_process ' ||
' WHERE stream_name=''' || process_name ||
'''';
EXECUTE IMMEDIATE dyn_query_string INTO process_state;
IF process_state = 'PAUSED FOR FLOW CONTROL' THEN
MESSAGE := 'The state of the capture process ' || process_name
|| ' is ' ||
process_state || '' || '.';
subject := 'Cap PAUSED FOR FLOW CONTROL on ';
strmmon.repl_send_email (MESSAGE,subject);
END IF; --process_state = 'PAUSED FOR FLOW CONTROL'
END LOOP;
END IF; --capture_avail > 0
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SQLERRM (SQLCODE));
END email_status_cap;
-
Originally Posted by deb494
Would the best way through a counter or something other method?
Yes.
Originally Posted by deb494
Sent emails aren't tracked in a table to compare current and previous records.
Better to create a table to track escalation status.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Can you share an example on how you would do this?
TIA
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|