-- The following package includes one public procedure called 
-- check_conditions_and_notify 
-- which can be run by a periodic job process. It can be used to notify the database 
-- administrator of conditions on the database that would normally have to 
-- be checked manually. The queries that it runs are held in the constants 
-- check1, check2, check3, etc. These can be easily changed. It is even possible
-- to keep the queries in a table instead of as constants in the procedure. 

-- No more do I have to login to the database to run daily checks on certain logs. The
-- database checks these items for me and emails me the results!

CREATE OR REPLACE PACKAGE xyz_email_notify
AS
/******************************************************************************
   NAME:       xyz_eMAIL_NOTIFY
   PURPOSE:    Notify the DBA that an event has occured.
               This package can be used to Notify the DBA of many different
               events such as certain errors found in the error_log table or
               deletions on SPRADDR that are being audited. This package will
			   be launched on regular intervals by dbms_job.

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2/9/2005    Edward Stoever    1. Created this package.
******************************************************************************/
 
   PROCEDURE check_conditions_and_notify;

END xyz_email_notify;
/

CREATE OR REPLACE PACKAGE BODY xyz_email_Notify
AS
/******************************************************************************
   NAME:       xyz_eMAIL_NOTIFY
   PURPOSE:    Notify the DBA that an event has occured.
               This package can be used to Notify the DBA of many different
               events such as certain errors found in the error_log table or
               deletions on SPRADDR that are being audited.

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2/9/2005    Edward Stoever    1. Created this package.
******************************************************************************/
   FUNCTION check_conditions (query_string VARCHAR2)
      RETURN VARCHAR2
   IS
      TYPE refcursor_type IS REF CURSOR;

      r_c1             refcursor_type;
/*      query_str   VARCHAR2 (1000)
                     := 'select ''o'' from dual union select ''o'' from dual'; */
      temp_varchar     VARCHAR2 (500);
      return_varchar   VARCHAR2 (5000) := '1a1b1c1d1';
   BEGIN
      OPEN r_c1
       FOR query_string;

      LOOP
         FETCH r_c1
          INTO temp_varchar;

         EXIT WHEN r_c1%NOTFOUND;

         IF return_varchar = '1a1b1c1d1'
         THEN
            return_varchar := temp_varchar;
         ELSE
            return_varchar := return_varchar || UTL_TCP.crlf || temp_varchar;
         END IF;
      END LOOP;

      CLOSE r_c1;

      IF return_varchar = '1a1b1c1d1'
      THEN
         return_varchar := NULL;
      END IF;

      RETURN return_varchar;
   EXCEPTION
      WHEN OTHERS
      THEN
         return_varchar := NULL;
         RETURN return_varchar;
   END;

   PROCEDURE notify (p_subject IN VARCHAR2, p_message IN VARCHAR2)
   AS
      l_sender      VARCHAR2 (255)      := '';
      l_recipient   VARCHAR2 (255)      := '';
      l_mailhost    VARCHAR2 (255)      := '';
      l_mail_conn   UTL_SMTP.connection;
      l_header      VARCHAR2 (1000);
      crlf          VARCHAR2 (2)        := UTL_TCP.crlf;
   BEGIN
      l_header :=
            'Date: '
         || TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss')
         || crlf
         || 'From: '
         || l_sender
         || ''
         || crlf
         || 'Subject: '
         || p_subject
         || crlf
         || 'To: '
         || l_recipient;
      l_mail_conn := UTL_SMTP.open_connection (l_mailhost, 25);
      UTL_SMTP.helo (l_mail_conn, l_mailhost);
      UTL_SMTP.mail (l_mail_conn, l_sender);
      UTL_SMTP.rcpt (l_mail_conn, l_recipient);
      UTL_SMTP.open_data (l_mail_conn);
      UTL_SMTP.write_data (l_mail_conn, l_header);
      UTL_SMTP.write_data (l_mail_conn, crlf || p_message);
      UTL_SMTP.close_data (l_mail_conn);
      UTL_SMTP.quit (l_mail_conn);
   END notify;

   PROCEDURE check_conditions_and_notify
   IS
      check1   CONSTANT VARCHAR2 (250)
         :=    'SELECT    ''USER '' || username || '' deleted '
            || 'from SPRADDR on '' || TO_CHAR (TIMESTAMP, ''MM'
            || '/DD/YYYY hh:mi:ss AM'') AS txt  FROM DBA_AUDIT'
            || '_TRAIL WHERE obj_name = ''SPRADDR'' AND TIMEST'
            || 'AMP > SYSDATE - 1';
      check2   CONSTANT VARCHAR2 (250)
         :=    'SELECT ''USER ''||username||'' got the error "'
            || 'ORA-01031: insufficient PRIVILEGES" ON ''||TO_'
            || 'CHAR (TIME_STAMP, ''MM/DD/YYYY hh:mi:ss AM'') '
            || 'AS txt FROM error_log WHERE error_stack LIKE'
            || ' ''ORA-01031%'' AND time_stamp > SYSDATE - 1 O'
            || 'RDER BY time_stamp DESC';
      email_text        VARCHAR2 (5000);
      check3   CONSTANT VARCHAR2 (250)
         :=    'SELECT ''There have been '' || TO_CHAR(COUNT (*))||'' '
            || 'login failures ON PROD IN the past 24 hours.'' A'
            || 'S txt  FROM error_log WHERE error_stack LIKE ''O'
            || 'RA-01017%'' AND time_stamp > SYSDATE - 1 HAVING '
            || 'COUNT(*) > 10';
   BEGIN
      email_text :=
            check_conditions (check1)
         || UTL_TCP.crlf
         || check_conditions (check2)
         || UTL_TCP.crlf
         || check_conditions (check3);

      IF LENGTH (email_text) > 10
      THEN
         notify ('Oracle System Errors on PROD', email_text);
      END IF;
   END check_conditions_and_notify;
END xyz_email_Notify;
/


-- To submit this as a job:
DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
  ( job       => X 
   ,what      => 'xyz_email_notify.check_conditions_and_notify;'
   ,next_date => TO_DATE('16/02/2005 15:45:02','dd/mm/yyyy hh24:mi:ss')
   ,INTERVAL  => 'SYSDATE+1'
   ,no_parse  => FALSE
  );
END;
/