8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Dba » Here

Script: dsp.pkb

( Download Script )

CREATE OR REPLACE PACKAGE BODY dsp AS
-- --------------------------------------------------------------------------
-- Name         : https://oracle-base.com/dba/miscellaneous/dsp.pkb
-- Author       : Tim Hall
-- Description  : An extension of the DBMS_OUTPUT package.
-- Requirements : https://oracle-base.com/dba/miscellaneous/dsp.pks
-- Ammedments   :
--   When         Who       What
--   ===========  ========  =================================================
--   08-JAN-2002  Tim Hall  Initial Creation.
--   04-APR-2005  Tim Hall  Store last call. Add get_last_prefix and
--                          get_last_data to allow retrieval.
--                          Switch from date to timestamp for greater accuracy.
--   01-MAR-2013  Tim Hall  Add RAISE to output procedure.
--                          Force upper case on directory object names.
--   02-MAR-2013  Tim Hall  Fix wrap_line. ORA-21780: Maximum number of object durations exceeded.
--                          Added file_contents pipelined table function.
--                          Added delete_file.
--   02-DEC-2013  Tim Hall  Add p_trace_level parameter to most code to
--                          limit amount of trace produced.
--                          Prefixed UTL_FILE references with "SYS.".
--   21-NOV-2018  Tim Hall  Add CLOB overloads to LINE.
-- --------------------------------------------------------------------------

-- Package Variables
g_show_output     BOOLEAN         := FALSE;
g_show_date       BOOLEAN         := FALSE;
g_line_wrap       BOOLEAN         := TRUE;
g_max_width       PLS_INTEGER     := 255;
g_date_format     VARCHAR2(32767) := 'DD-MON-YYYY HH24:MI:SS.FF';
g_file_dir        VARCHAR2(32767) := NULL;
g_file_name       VARCHAR2(32767) := NULL;
g_last_prefix     VARCHAR2(32767) := NULL;
g_last_data       VARCHAR2(32767) := NULL;
g_trace_level     PLS_INTEGER     := DSP.trace_level_all;

-- Hidden Methods
PROCEDURE display (p_prefix       IN  VARCHAR2,
                   p_data         IN  VARCHAR2,
                   p_trace_level  IN  PLS_INTEGER := DSP.trace_level_info,
                   p_wrap         IN  BOOLEAN := FALSE);

PROCEDURE display_clob (p_prefix       IN  VARCHAR2,
                        p_data         IN  CLOB,
                        p_trace_level  IN  PLS_INTEGER := DSP.trace_level_info);

PROCEDURE wrap_line (p_data         IN  VARCHAR2,
                     p_trace_level  IN  PLS_INTEGER := DSP.trace_level_info);

PROCEDURE wrap_line_clob (p_data         IN  CLOB,
                          p_trace_level  IN  PLS_INTEGER := DSP.trace_level_info);

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.FF';
  g_trace_level  := DSP.trace_level_all;
END;
-- --------------------------------------------------------------------------


-- --------------------------------------------------------------------------
PROCEDURE show_output_on(p_trace_level  IN  PLS_INTEGER := DSP.trace_level_all) IS
-- --------------------------------------------------------------------------
BEGIN
  g_show_output := TRUE;
  g_trace_level := p_trace_level;
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  PLS_INTEGER) 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  := UPPER(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;
-- --------------------------------------------------------------------------


-- --------------------------------------------------------------------------
FUNCTION get_last_prefix
  RETURN VARCHAR2 IS
-- --------------------------------------------------------------------------
BEGIN
  RETURN g_last_prefix;
END;
-- --------------------------------------------------------------------------


-- --------------------------------------------------------------------------
FUNCTION get_last_data
  RETURN VARCHAR2 IS
-- --------------------------------------------------------------------------
BEGIN
  RETURN g_last_data;
END;
-- --------------------------------------------------------------------------


-- --------------------------------------------------------------------------
PROCEDURE line (p_data         IN  VARCHAR2,
                p_trace_level  IN  PLS_INTEGER := DSP.trace_level_info) IS
-- --------------------------------------------------------------------------
BEGIN
  display (NULL, p_data, p_trace_level);
END;
-- --------------------------------------------------------------------------


-- --------------------------------------------------------------------------
PROCEDURE line (p_data         IN  CLOB,
                p_trace_level  IN  PLS_INTEGER := DSP.trace_level_info) IS
-- --------------------------------------------------------------------------
BEGIN
  display_clob (NULL, p_data, p_trace_level);
END;
-- --------------------------------------------------------------------------


-- --------------------------------------------------------------------------
PROCEDURE line (p_data         IN  NUMBER,
                p_trace_level  IN  PLS_INTEGER := DSP.trace_level_info) IS
-- --------------------------------------------------------------------------
BEGIN
  display (NULL, p_data, p_trace_level);
END;
-- --------------------------------------------------------------------------


-- --------------------------------------------------------------------------
PROCEDURE line (p_data         IN  BOOLEAN,
                p_trace_level  IN  PLS_INTEGER := DSP.trace_level_info) IS
-- --------------------------------------------------------------------------
BEGIN
  line (NULL, p_data, p_trace_level);
END;
-- --------------------------------------------------------------------------


-- --------------------------------------------------------------------------
PROCEDURE line (p_data         IN  DATE,
                p_format       IN  VARCHAR2 DEFAULT 'DD-MON-YYYY HH24:MI:SS.FF',
                p_trace_level  IN  PLS_INTEGER := DSP.trace_level_info) IS
-- --------------------------------------------------------------------------
BEGIN
  line (NULL, p_data, p_format, p_trace_level);
END;
-- --------------------------------------------------------------------------


-- --------------------------------------------------------------------------
PROCEDURE line (p_prefix       IN  VARCHAR2,
                p_data         IN  VARCHAR2,
                p_trace_level  IN  PLS_INTEGER := DSP.trace_level_info) IS
-- --------------------------------------------------------------------------
BEGIN
  display (p_prefix, p_data, p_trace_level);
END;
-- --------------------------------------------------------------------------


-- --------------------------------------------------------------------------
PROCEDURE line (p_prefix       IN  VARCHAR2,
                p_data         IN  CLOB,
                p_trace_level  IN  PLS_INTEGER := DSP.trace_level_info) IS
-- --------------------------------------------------------------------------
BEGIN
  display_clob (p_prefix, p_data, p_trace_level);
END;
-- --------------------------------------------------------------------------


-- --------------------------------------------------------------------------
PROCEDURE line (p_prefix       IN  VARCHAR2,
                p_data         IN  NUMBER,
                p_trace_level  IN  PLS_INTEGER := DSP.trace_level_info) IS
-- --------------------------------------------------------------------------
BEGIN
  display (p_prefix, TO_CHAR(p_data), p_trace_level);
END;
-- --------------------------------------------------------------------------


-- --------------------------------------------------------------------------
PROCEDURE line (p_prefix       IN  VARCHAR2,
                p_data         IN  BOOLEAN,
                p_trace_level  IN  PLS_INTEGER := DSP.trace_level_info) IS
-- --------------------------------------------------------------------------
BEGIN
  IF p_data THEN
    display (p_prefix, 'TRUE', p_trace_level);
  ELSE
    display (p_prefix, 'FALSE', p_trace_level);
  END IF;
END;
-- --------------------------------------------------------------------------


-- --------------------------------------------------------------------------
PROCEDURE line (p_prefix       IN  VARCHAR2,
                p_data         IN  DATE,
                p_format       IN  VARCHAR2 DEFAULT 'DD-MON-YYYY HH24:MI:SS.FF',
                p_trace_level  IN  PLS_INTEGER := DSP.trace_level_info) IS
-- --------------------------------------------------------------------------
BEGIN
  display (p_prefix, TO_CHAR(p_data, p_format), p_trace_level);
END;
-- --------------------------------------------------------------------------


-- --------------------------------------------------------------------------
PROCEDURE display (p_prefix       IN  VARCHAR2,
                   p_data         IN  VARCHAR2,
                   p_trace_level  IN  PLS_INTEGER := DSP.trace_level_info,
                   p_wrap         IN  BOOLEAN := FALSE) IS
-- --------------------------------------------------------------------------
  l_data  VARCHAR2(32767) := p_data;
BEGIN
  g_last_prefix := p_prefix;
  g_last_data   := p_data;

  IF g_show_output AND p_trace_level <= g_trace_level THEN
    IF l_data IS NULL THEN
      l_data := '';
    END IF;

    IF p_prefix IS NOT NULL AND p_wrap = FALSE THEN
      l_data := p_prefix || ' : ' || l_data;
    END IF;

    IF g_show_date AND p_wrap = FALSE THEN
      l_data := TO_CHAR(SYSTIMESTAMP, g_date_format) || ' : ' || l_data;
    END IF;

    IF LENGTH(l_data) > g_max_width THEN
      IF g_line_wrap THEN
        wrap_line (l_data);
      ELSE
        l_data := SUBSTR(l_data, 1, g_max_width);
        output (l_data);
      END IF;
    ELSE
      output (l_data);
    END IF;
  END IF;
END;
-- --------------------------------------------------------------------------



-- --------------------------------------------------------------------------
PROCEDURE display_clob (p_prefix       IN  VARCHAR2,
                        p_data         IN  CLOB,
                        p_trace_level  IN  PLS_INTEGER := DSP.trace_level_info) IS
-- --------------------------------------------------------------------------
  l_data  CLOB := p_data;
BEGIN
  g_last_prefix := p_prefix;

  IF g_show_output AND p_trace_level <= g_trace_level THEN
    IF l_data IS NULL THEN
      l_data := '';
    END IF;

    IF p_prefix IS NOT NULL THEN
      l_data := p_prefix || ' : ' || l_data;
    END IF;

    IF g_show_date THEN
      l_data := TO_CHAR(SYSTIMESTAMP, g_date_format) || ' : ' || l_data;
    END IF;

    wrap_line_clob (l_data);
  END IF;
END;
-- --------------------------------------------------------------------------



-- --------------------------------------------------------------------------
PROCEDURE wrap_line (p_data         IN  VARCHAR2,
                     p_trace_level  IN  PLS_INTEGER := DSP.trace_level_info) IS
-- --------------------------------------------------------------------------
  l_offset NUMBER := 1;
BEGIN
  LOOP
    EXIT WHEN l_offset > LENGTH(p_data);
    display (NULL, SUBSTR(p_data, l_offset, g_max_width), p_trace_level, TRUE);
    l_offset := l_offset + g_max_width;
  END LOOP;
END;
-- --------------------------------------------------------------------------



-- --------------------------------------------------------------------------
PROCEDURE wrap_line_clob (p_data         IN  CLOB,
                          p_trace_level  IN  PLS_INTEGER := DSP.trace_level_info) IS
-- --------------------------------------------------------------------------
  l_offset NUMBER := 1;
BEGIN
  LOOP
    EXIT WHEN l_offset > LENGTH(p_data);
    display (NULL, SUBSTR(p_data, l_offset, g_max_width), p_trace_level, TRUE);
    l_offset := l_offset + g_max_width;
  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
      l_file  SYS.UTL_FILE.file_type;
    BEGIN
      l_file := SYS.UTL_FILE.fopen (g_file_dir, g_file_name, 'A', 32767);
      SYS.UTL_FILE.put_line(l_file, p_data);
      SYS.UTL_FILE.fclose (l_file);
    EXCEPTION
      WHEN OTHERS THEN
        SYS.UTL_FILE.fclose (l_file);
        RAISE;
    END;
  END IF;
END;
-- --------------------------------------------------------------------------


-- --------------------------------------------------------------------------
FUNCTION file_contents (p_dir  IN VARCHAR2,
                        p_file IN VARCHAR2)
  RETURN DBMSOUTPUT_LINESARRAY PIPELINED IS
-- --------------------------------------------------------------------------
  l_file SYS.UTL_FILE.file_type;
  l_text VARCHAR2(32767);
BEGIN
  l_file := SYS.UTL_FILE.fopen(UPPER(p_dir), p_file, 'r', 32767);
  BEGIN
    LOOP
      SYS.UTL_FILE.get_line(l_file, l_text);
      PIPE ROW (l_text);
    END LOOP;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL;
  END;
  UTL_FILE.fclose(l_file);
  RETURN;
EXCEPTION
  WHEN OTHERS THEN
    PIPE ROW ('ERROR: ' || SQLERRM);
    IF SYS.UTL_FILE.is_open(l_file) THEN
      SYS.UTL_FILE.fclose(l_file);
    END IF;
    RETURN;
END;
-- --------------------------------------------------------------------------


-- --------------------------------------------------------------------------
PROCEDURE delete_file (p_dir  IN VARCHAR2,
                       p_file IN VARCHAR2) IS
-- --------------------------------------------------------------------------
BEGIN
  SYS.UTL_FILE.fremove(UPPER(p_dir), p_file);
END;
-- --------------------------------------------------------------------------

END dsp;
/

SHOW ERRORS