dsp.pkb.txt

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

CREATE OR REPLACE PACKAGE BODY dsp AS
-- --------------------------------------------------------------------------
-- Name         : dsp.pkb
-- Author       : DR Timothy S Hall
-- Description  : An extension of the DBMS_OUTPUT package.
-- Requirements : dsp.pks
-- Ammedments   :
--   When         Who       What
--   ===========  ========  =================================================
--   08-JAN-2002  Tim Hall  Initial Creation
-- --------------------------------------------------------------------------

  -- Package Variables
  g_show_output  BOOLEAN       := FALSE;
  g_show_date    BOOLEAN       := FALSE;
  g_line_wrap    BOOLEAN       := TRUE;
  g_max_width    NUMBER(10)    := 255;
  g_date_format  VARCHAR2(50)  := 'DD-MON-YYYY HH24:MI:SS';
  g_file_dir     VARCHAR2(100) := NULL;
  g_file_name    VARCHAR2(100) := NULL;

  -- Hidden Methods
  PROCEDURE display (p_prefix IN VARCHAR2,
                     p_data   IN VARCHAR2);
  PROCEDURE wrap_line (p_data IN VARCHAR2);
  PROCEDURE output (p_data IN VARCHAR2);
                     
                     
  -- Exposed Methods

  -- --------------------------------------------------------------------------
  PROCEDURE reset_defaults IS         
  -- --------------------------------------------------------------------------
  BEGIN
    g_show_output  := FALSE;
    g_show_date    := FALSE;
    g_line_wrap    := TRUE;
    g_max_width    := 255;
    g_date_format  := 'DD-MON-YYYY HH24:MI:SS';
  END;
  -- --------------------------------------------------------------------------


  -- --------------------------------------------------------------------------
  PROCEDURE show_output_on IS
  -- --------------------------------------------------------------------------
  BEGIN
    g_show_output := TRUE;
  END;
  -- --------------------------------------------------------------------------

  
  -- --------------------------------------------------------------------------
  PROCEDURE show_output_off IS
  -- --------------------------------------------------------------------------
  BEGIN
    g_show_output := FALSE;
  END;
  -- --------------------------------------------------------------------------


  -- --------------------------------------------------------------------------
  PROCEDURE show_date_on IS
  -- --------------------------------------------------------------------------
  BEGIN
    g_show_date := TRUE;
  END;
  -- --------------------------------------------------------------------------

  
  -- --------------------------------------------------------------------------
  PROCEDURE show_date_off IS
  -- --------------------------------------------------------------------------
  BEGIN
    g_show_date := FALSE;
  END;
  -- --------------------------------------------------------------------------


  -- --------------------------------------------------------------------------
  PROCEDURE line_wrap_on IS
  -- --------------------------------------------------------------------------
  BEGIN
    g_line_wrap := TRUE;
  END;
  -- --------------------------------------------------------------------------

  
  -- --------------------------------------------------------------------------
  PROCEDURE line_wrap_off IS
  -- --------------------------------------------------------------------------
  BEGIN
    g_line_wrap := FALSE;
  END;
  -- --------------------------------------------------------------------------


  -- --------------------------------------------------------------------------
  PROCEDURE set_max_width (p_width IN NUMBER) IS
  -- --------------------------------------------------------------------------
  BEGIN
    g_max_width := p_width;
  END;
  -- --------------------------------------------------------------------------


  -- --------------------------------------------------------------------------
  PROCEDURE set_date_format (p_date_format IN VARCHAR2) IS
  -- --------------------------------------------------------------------------
  BEGIN
    g_date_format := p_date_format;
  END;
  -- --------------------------------------------------------------------------


  -- --------------------------------------------------------------------------
  PROCEDURE file_output_on (p_file_dir  IN VARCHAR2 DEFAULT NULL,
                            p_file_name IN VARCHAR2 DEFAULT NULL) IS
  -- --------------------------------------------------------------------------
  BEGIN
    g_file_dir  := p_file_dir;
    g_file_name := p_file_name;
  END;
  -- --------------------------------------------------------------------------


  -- --------------------------------------------------------------------------
  PROCEDURE file_output_off IS
  -- --------------------------------------------------------------------------
  BEGIN
    g_file_dir  := NULL;
    g_file_name := NULL;
  END;
  -- --------------------------------------------------------------------------


  -- --------------------------------------------------------------------------
  PROCEDURE line (p_data IN VARCHAR2) IS
  -- --------------------------------------------------------------------------
  BEGIN
    display (NULL, p_data);
  END;
  -- --------------------------------------------------------------------------
  

  -- --------------------------------------------------------------------------
  PROCEDURE line (p_data IN NUMBER) IS
  -- --------------------------------------------------------------------------
  BEGIN
    display (NULL, p_data);
  END;
  -- --------------------------------------------------------------------------
  

  -- --------------------------------------------------------------------------
  PROCEDURE line (p_data IN BOOLEAN) IS
  -- --------------------------------------------------------------------------
  BEGIN
    line (NULL, p_data);
  END;
  -- --------------------------------------------------------------------------
  

  -- --------------------------------------------------------------------------
  PROCEDURE line (p_data   IN DATE,
                  p_format IN VARCHAR2 DEFAULT 'DD-MON-YYYY HH24:MI:SS') IS
  -- --------------------------------------------------------------------------
  BEGIN
    line (NULL, p_data, p_format);
  END;
  -- --------------------------------------------------------------------------

  
  -- --------------------------------------------------------------------------
  PROCEDURE line (p_prefix IN VARCHAR2,
                  p_data   IN VARCHAR2) IS
  -- --------------------------------------------------------------------------
  BEGIN
    display (p_prefix, p_data);
  END;
  -- --------------------------------------------------------------------------

  
  -- --------------------------------------------------------------------------
  PROCEDURE line (p_prefix IN VARCHAR2,
                  p_data   IN NUMBER) IS
  -- --------------------------------------------------------------------------
  BEGIN
    display (p_prefix, To_Char(p_data));
  END;
  -- --------------------------------------------------------------------------

  
  -- --------------------------------------------------------------------------
  PROCEDURE line (p_prefix IN VARCHAR2,
                  p_data   IN BOOLEAN) IS
  -- --------------------------------------------------------------------------
  BEGIN
    IF p_data THEN
      display (p_prefix, 'TRUE');
    ELSE
      display (p_prefix, 'FALSE');
    END IF;
  END;
  -- --------------------------------------------------------------------------
  
  
  -- --------------------------------------------------------------------------
  PROCEDURE line (p_prefix IN VARCHAR2,
                  p_data   IN DATE,
                  p_format IN VARCHAR2 DEFAULT 'DD-MON-YYYY HH24:MI:SS') IS
  -- --------------------------------------------------------------------------
  BEGIN
    display (p_prefix, To_Char(p_data, p_format));
  END;
  -- --------------------------------------------------------------------------
  
  
  -- --------------------------------------------------------------------------
  PROCEDURE display (p_prefix IN VARCHAR2,
                     p_data   IN VARCHAR2) IS
  -- --------------------------------------------------------------------------
    v_data  VARCHAR2(32767) := p_data;
  BEGIN
    IF g_show_output THEN
      IF v_data IS NULL THEN
        v_data := '<NULL>';
      END IF;
      
      IF p_prefix IS NOT NULL THEN
        v_data := p_prefix || ' : ' || v_data;
      END IF;
      
      IF g_show_date THEN
        v_data := To_Char(Sysdate, g_date_format) || ' : ' || v_data;
      END IF;
      
      IF Length(v_data) > g_max_width THEN
        IF g_line_wrap THEN
          wrap_line (v_data);
        ELSE
          v_data := SubStr(v_data, 1, g_max_width);
          output (v_data);
        END IF;
      ELSE
        output (v_data);
      END IF;
    END IF;
  END;
  -- --------------------------------------------------------------------------
  
  
  -- --------------------------------------------------------------------------
  PROCEDURE wrap_line (p_data IN VARCHAR2) IS
  -- --------------------------------------------------------------------------
    v_data  VARCHAR2(32767) := p_data;
  BEGIN
    LOOP
      display (NULL, SubStr(v_data, 1, g_max_width));
      v_data := SubStr(v_data, g_max_width + 1);
      EXIT WHEN v_data IS NULL;
    END LOOP;
  END;
  -- --------------------------------------------------------------------------
  

  -- --------------------------------------------------------------------------
  PROCEDURE output (p_data IN VARCHAR2) IS
  -- --------------------------------------------------------------------------
  BEGIN
    IF g_file_dir IS NULL OR g_file_name IS NULL THEN
      DBMS_OUTPUT.put_line(p_data);
    ELSE
      DECLARE
        v_file  UTL_FILE.file_type;
      BEGIN
        v_file := UTL_FILE.fopen (g_file_dir, g_file_name, 'A');
        UTL_FILE.put_line(v_file, p_data);
        UTL_FILE.fclose (v_file);
      EXCEPTION
        WHEN OTHERS THEN
          UTL_FILE.fclose (v_file);
      END;
    END IF;
  END;
  -- --------------------------------------------------------------------------

END dsp;
/

SHOW ERRORS