DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Help with procedure to escalate emails

  1. #1
    Join Date
    Sep 2009
    Posts
    2

    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;

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by deb494 View Post
    Would the best way through a counter or something other method?
    Yes.

    Quote Originally Posted by deb494 View Post
    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.

  3. #3
    Join Date
    Sep 2009
    Posts
    2
    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
  •  


Click Here to Expand Forum to Full Width