err.pkb.txt

oracle dba 常用的管理脚本, 覆盖日常的系统管理.

CREATE OR REPLACE PACKAGE BODY err AS
-- --------------------------------------------------------------------------
-- Name         : err.pkb
-- Author       : DR Timothy S Hall
-- Description  : A simple mechanism for logging error information to a table.
-- Requirements : err.pks, dsp.pks, dsp.pkb and schema definied in err.pks
-- Ammedments   :
--   When         Who       What
--   ===========  ========  =================================================
--   17-JUL-2003  Tim Hall  Initial Creation
-- --------------------------------------------------------------------------

  -- Package Variables
  g_logs_on     BOOLEAN      := TRUE;
  g_date_format  VARCHAR2(50) := 'DD-MON-YYYY HH24:MI:SS';

  -- Exposed Methods

  -- --------------------------------------------------------------------------
  PROCEDURE reset_defaults IS
  -- --------------------------------------------------------------------------
  BEGIN
    g_logs_on      := TRUE;
    g_date_format  := 'DD-MON-YYYY HH24:MI:SS';
  END;
  -- --------------------------------------------------------------------------


  -- --------------------------------------------------------------------------
  PROCEDURE logs_on IS
  -- --------------------------------------------------------------------------
  BEGIN
    g_logs_on := TRUE;
  END;
  -- --------------------------------------------------------------------------


  -- --------------------------------------------------------------------------
  PROCEDURE logs_off IS
  -- --------------------------------------------------------------------------
  BEGIN
    g_logs_on := FALSE;
  END;
  -- --------------------------------------------------------------------------


  -- --------------------------------------------------------------------------
  PROCEDURE set_date_format (p_date_format IN VARCHAR2 DEFAULT 'DD-MON-YYYY HH24:MI:SS') IS
  -- --------------------------------------------------------------------------
  BEGIN
    g_date_format := p_date_format;
  END;
  -- --------------------------------------------------------------------------


  -- --------------------------------------------------------------------------
  PROCEDURE line (p_prefix       IN  VARCHAR2,
                  p_data         IN  VARCHAR2,
                  p_error_level  IN  NUMBER   DEFAULT 5,
                  p_error_user   IN  VARCHAR2 DEFAULT USER) IS
  -- --------------------------------------------------------------------------
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    IF g_logs_on THEN
      INSERT INTO error_logs
      (id,
       prefix,
       data,
       error_level,
       created_date,
       created_by)
      VALUES
      (error_logs_seq.NEXTVAL,
       p_prefix,
       p_data,
       p_error_level,
       SYSDATE,
       p_error_user);

      COMMIT;
    END IF;
  END;
  -- --------------------------------------------------------------------------


  -- --------------------------------------------------------------------------
  PROCEDURE line (p_data         IN  VARCHAR2,
                  p_error_level  IN  NUMBER   DEFAULT 5,
                  p_error_user   IN  VARCHAR2 DEFAULT USER) IS
  -- --------------------------------------------------------------------------
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    line (p_prefix       => NULL,
          p_data         => p_data,
          p_error_level  => p_error_level,
          p_error_user   => p_error_user);
  END;
  -- --------------------------------------------------------------------------


  -- --------------------------------------------------------------------------
  PROCEDURE display (p_error_level  IN  NUMBER   DEFAULT NULL,
                     p_error_user   IN  VARCHAR2 DEFAULT NULL,
                     p_from_date    IN  DATE     DEFAULT NULL,
                     p_to_date      IN  DATE     DEFAULT NUll) IS
  -- --------------------------------------------------------------------------
    CURSOR c_errors IS
      SELECT *
      FROM   error_logs
      WHERE  error_level   = NVL(p_error_level, error_level)
      AND    created_by    = NVL(p_error_user, created_by)
      AND    created_date >= NVL(p_from_date, created_date)
      AND    created_date <= NVL(p_to_date, created_date)
      ORDER BY id;
  BEGIN
    FOR cur_rec IN c_errors LOOP
      dsp.line(cur_rec.prefix, cur_rec.data);
    END LOOP;
  END;
  -- --------------------------------------------------------------------------

END err;
/

SHOW ERRORS