Rem drv: <create type="pkgbodys" pos="blackout/blackout_pkgbodys.sql+"/>
Rem
Rem $Header: log_pkgbodys.sql 05-sep-2005.11:59:56 rmaggarw Exp $
Rem
Rem log_pkgbodys.sql
Rem
Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      log_pkgbodys.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    rmaggarw    09/05/05 - fix logging 
Rem    gsbhatia    07/01/05 - New repmgr header impl 
Rem    gsbhatia    06/26/05 - New repmgr header impl 
Rem    gsbhatia    02/13/05 - updating repmgr header 
Rem    gsbhatia    02/09/05 - updating repmgr header 
Rem    gsbhatia    02/07/05 - updating repmgr header 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    jsadras     10/15/04 - bug_3818527
Rem    aholser     08/21/03 - change default to error 
Rem    aholser     04/17/03 - fix log levels
Rem    aholser     04/09/03 - 4.1 changes
Rem    rpinnama    10/16/02 - Ignore duplicates
Rem    rpinnama    10/15/02 - Set default for display name    
Rem    rpinnama    10/15/02 - show errors
Rem    rpinnama    10/14/02 - Provide a registration/deregistration interface for logging
Rem    aholser     07/10/02 - add emd_url to error log
Rem    rpinnama    05/14/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/14/02 - Created
Rem

CREATE OR REPLACE PACKAGE BODY MGMT_LOG AS

  PROCEDURE register_logging_module(v_job_name_in     IN VARCHAR2,
                                    v_display_name_in IN VARCHAR2 DEFAULT NULL,
                                    v_dbms_jobname_in IN VARCHAR2 DEFAULT NULL)
  IS PRAGMA AUTONOMOUS_TRANSACTION;
  l_display_name MGMT_PERFORMANCE_NAMES.display_name%TYPE;
  l_is_dbmsjob   MGMT_PERFORMANCE_NAMES.is_dbmsjob%TYPE := 'N';

  BEGIN
    IF v_display_name_in IS NULL THEN
      l_display_name := v_job_name_in;
    ELSE
      l_display_name := v_display_name_in;
    END IF;

    IF (v_dbms_jobname_in IS NULL) THEN
      l_is_dbmsjob := 'N';
    ELSE
      l_is_dbmsjob := 'Y';
    END IF;

    BEGIN
      INSERT INTO mgmt_performance_names
        (job_name, display_name, dbms_jobname, is_dbmsjob)
      VALUES
        (v_job_name_in, l_display_name, v_dbms_jobname_in, l_is_dbmsjob);

      COMMIT;
    EXCEPTION 
      WHEN DUP_VAL_ON_INDEX THEN
        -- Ignore duplicate registrations..
	NULL;
    END;
      
  END register_logging_module;


  PROCEDURE log_error(v_module_name_in IN VARCHAR2,
                      v_error_code_in  IN NUMBER,
                      v_error_msg_in   IN VARCHAR2,
                      v_facility_in    IN VARCHAR2 DEFAULT 'MGMT',
                      v_client_data_in IN VARCHAR2 DEFAULT NULL,
                      v_oms_url_in     IN VARCHAR2 DEFAULT NULL,
                      v_emd_url_in     IN VARCHAR2 DEFAULT NULL,
                      v_log_level_in   IN VARCHAR2 DEFAULT 'ERROR'
  )
  IS PRAGMA AUTONOMOUS_TRANSACTION;

  err number;
  errct number;
  errlvl number;
  errlvlin number := 0;
  errsysdate DATE ;
  errowid ROWID ;
  BEGIN

    BEGIN
      SELECT DECODE(parameter_value, MGMT_GLOBAL.G_ERROR, 3, 
          MGMT_GLOBAL.G_WARN, 2, MGMT_GLOBAL.G_INFO, 1, 0)
        INTO errlvl FROM mgmt_parameters 
        WHERE parameter_name = 'system_error_log_level';
    EXCEPTION
      -- throw integrity constraint violation - i.e., job not registered
      WHEN OTHERS THEN
        errlvl := 0;
    END;

    if(v_log_level_in = MGMT_GLOBAL.G_ERROR) THEN errlvlin := 3; END IF;
    if(v_log_level_in = MGMT_GLOBAL.G_WARN) THEN errlvlin := 2; END IF;
    if(v_log_level_in = MGMT_GLOBAL.G_INFO) THEN errlvlin := 1; END IF;
 
    IF errlvlin >= errlvl 
    THEN
      BEGIN
        errsysdate := SYSDATE ;
        -- If the same error has been logged by the same module in the last 1
        -- hour then just update the occur date.
        -- There is a very remote possibility of 2 inserts at same time and
        -- both of them going through and inserting. Using locks would slow
        -- down concurrency, so we allow for this very remote possibility to 
        -- happen, since it is not going to break anything. 
        SELECT max(ROWID)
          INTO errowid
          FROM mgmt_system_error_log errlog
         WHERE errlog.module_name = v_module_name_in AND
               errlog.log_level   = v_log_level_in AND
               errlog.occur_date >= errsysdate - 1/24  AND
               errlog.error_code  = v_error_code_in AND
               errlog.error_msg   = SUBSTR(v_error_msg_in,1,2048) ;
        IF errowid IS NULL
        THEN
          INSERT INTO mgmt_system_error_log
          (module_name, occur_date, error_code, log_level, error_msg,
           facility, client_data, host_url, emd_url)
          VALUES
          (v_module_name_in, errsysdate, v_error_code_in, SUBSTR(v_log_level_in,1,16),
           SUBSTR(v_error_msg_in,1,2048), SUBSTR(v_facility_in,1,6),
           SUBSTR(v_client_data_in,1,128), SUBSTR(v_oms_url_in,1,256),
           SUBSTR(v_emd_url_in,1,256));
        ELSE
          -- In case of conflicting updates we want the latest date.
          -- Not using occur_date in where clause since we want to update
          -- occur count (TBD) 
          UPDATE mgmt_system_error_log
             SET occur_date = greatest(occur_date,errsysdate)
           WHERE ROWID = errowid ;
        END IF ;

        COMMIT; -- system error should always be recorded.
      EXCEPTION
        -- throw integrity constraint violation - i.e., job not registered
        WHEN OTHERS THEN
           err := SQLCODE;
           -- Try to log the error, if we haven't logged it in the past hour.
           BEGIN
              SELECT COUNT(error_code) INTO errct 
                 FROM mgmt_system_error_log 
                 WHERE error_code = err
                 AND module_name = 'LOGGING'
                 AND occur_date > SYSDATE - (1/24);
              IF errct < 1 THEN
                -- assume any column could be problem except module_name.  We may
                -- want to expand this later
                INSERT INTO mgmt_system_error_log
                   (module_name, occur_date, error_code, log_level, error_msg)
                   VALUES ('LOGGING', SYSDATE, err, 'ERROR',
                     'log_error: ERROR logging err from '||v_module_name_in);
                COMMIT ;
              END IF;
           -- If we can't log the error in the error log, ignore and continue 
           EXCEPTION
              WHEN OTHERS THEN NULL; 
           END;
 
           IF err = -2291 THEN
              RAISE;
           END IF;
       END;
    END IF;
  END log_error;


  PROCEDURE log_performance(v_job_name_in IN VARCHAR2,
                            v_duration_in IN NUMBER DEFAULT 0,
                            v_time_in IN DATE DEFAULT SYSDATE,
                            v_is_total_in IN VARCHAR2 DEFAULT 'Y',
                            v_name_in IN VARCHAR2 DEFAULT NULL,
                            v_value_in IN VARCHAR2 DEFAULT NULL,
                            v_module_in IN VARCHAR2 DEFAULT NULL,
                            v_action_in IN VARCHAR2 DEFAULT NULL,
                            v_client_data_in IN VARCHAR2 DEFAULT NULL,
                            v_oms_url_in     IN VARCHAR2 DEFAULT NULL
                            )
  IS PRAGMA AUTONOMOUS_TRANSACTION;

  err number;
  l_duration NUMBER(9);
  l_timestamp DATE;
  BEGIN
    BEGIN
      l_duration := v_duration_in;
      l_timestamp := SYSDATE;
      IF v_time_in IS NOT NULL
      THEN
         l_timestamp := v_time_in;
      END IF;
      INSERT INTO mgmt_system_performance_log
        (job_name, time, duration, module, 
         action, is_total, name, value, 
         client_data, host_url)
      VALUES
        (v_job_name_in, SYSDATE, l_duration, SUBSTR(v_module_in, 1, 512),
         SUBSTR(v_action_in,1,32), v_is_total_in, SUBSTR(v_name_in,1,128),
         SUBSTR(v_value_in,1,128), SUBSTR(v_client_data_in,1,128), SUBSTR(v_oms_url_in,1,256));

      COMMIT;
    EXCEPTION
      -- throw integrity constraint violation - i.e., job not registered
      WHEN OTHERS THEN
      --   err := SQLCODE;
      --   IF err = -2291 THEN
            RAISE;
      --   END IF;
    END;
  END log_performance;

END MGMT_LOG;
/
show errors;