Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

The DSP Package


Oracle Tips by Burleson Consulting

 

The following Tip is from the outstanding book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006:

Probably the most common way of producing trace output from PL/SQL is to use the dbms_ouput package.  The main drawbacks with this package include:

  • The maximum size of a single line of output is 255 characters.

  • The maximum amount of trace is limited to 1,000,000 bytes.

  • By default, the output is displayed after the program execution so it can be limiting for long-running programs.

  • By default, the output is only displayed when run from client tools, not during background operations such as scheduled jobs.

The dsp package is a wrapper around the dbms_output package and solves many of these issues.  Its list of features includes:

  • Line wrap to get around the 255 character limit.

  • Output directly to a file rather than the screen.

  • Output of Boolean values.

  • Optional message prefix for all messages.

  • Optional timestamp for all messages.

  • Access to the last message traced.

The package specification and body are listed below and available for free download from the http://www.oracle-base.com website.

dsp.pks

CREATE OR REPLACE PACKAGE dsp AS
-- --------------------------------------------------------------------------
-- Name         : http://www.oracle-base.com/dba/miscellaneous/dsp.pks
-- Author       : DR Timothy S Hall
-- Description  : An extension of the DBMS_OUTPUT package.
-- Requirements : http://www.oracle-base.com/dba/miscellaneous/dsp.pkb
-- 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.
-- --------------------------------------------------------------------------

  PROCEDURE reset_defaults;

  PROCEDURE show_output_on;
  PROCEDURE show_output_off;

  PROCEDURE show_date_on;
  PROCEDURE show_date_off;

  PROCEDURE line_wrap_on;
  PROCEDURE line_wrap_off;

  PROCEDURE set_max_width (p_width  IN  NUMBER);

  PROCEDURE set_date_format (p_date_format  IN  VARCHAR2);

  PROCEDURE file_output_on (p_file_dir   IN  VARCHAR2 DEFAULT NULL,
                            p_file_name  IN  VARCHAR2 DEFAULT NULL);

  PROCEDURE file_output_off;

  FUNCTION get_last_prefix
    RETURN VARCHAR2;

  FUNCTION get_last_data
    RETURN VARCHAR2;

  PROCEDURE line (p_data    IN  VARCHAR2);
  PROCEDURE line (p_data    IN  NUMBER);
  PROCEDURE line (p_data    IN  BOOLEAN);
  PROCEDURE line (p_data    IN  DATE,
                  p_format  IN  VARCHAR2 DEFAULT 'DD-MON-YYYY HH24:MI:SS.FF');

  PROCEDURE line (p_prefix  IN  VARCHAR2,
                  p_data    IN  VARCHAR2);
  PROCEDURE line (p_prefix  IN  VARCHAR2,
                  p_data    IN  NUMBER);
  PROCEDURE line (p_prefix  IN  VARCHAR2,
                  p_data    IN  BOOLEAN);
  PROCEDURE line (p_prefix  IN  VARCHAR2,
                  p_data    IN  DATE,
                  p_format  IN  VARCHAR2 DEFAULT 'DD-MON-YYYY HH24:MI:SS.FF');

END dsp;
/

SHOW ERRORS

dsp.pkb

CREATE OR REPLACE PACKAGE BODY dsp AS
-- --------------------------------------------------------------------------
-- Name         : http://www.oracle-base.com/dba/miscellaneous/dsp.pkb
-- Author       : DR Timothy S Hall
-- Description  : An extension of the DBMS_OUTPUT package.
-- Requirements : http://www.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.
-- --------------------------------------------------------------------------

-- 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(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;

-- 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.FF';
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;
-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------
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) 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.FF') 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.FF') IS
-- --------------------------------------------------------------------------|
BEGIN
  display (p_prefix, TO_CHAR(p_data, p_format));
END;
-- --------------------------------------------------------------------------

-- --------------------------------------------------------------------------
PROCEDURE display (p_prefix  IN  VARCHAR2,
                   p_data    IN  VARCHAR2) IS
-- --------------------------------------------------------------------------
  l_data  VARCHAR2(32767) := p_data;
BEGIN
  g_last_prefix := p_prefix;
  g_last_data   := p_data;
  IF g_show_output THEN
    IF l_data IS NULL THEN
      l_data := '<NULL>';
    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;

    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 wrap_line (p_data  IN  VARCHAR2) IS
-- --------------------------------------------------------------------------
  l_data  VARCHAR2(32767) := p_data;
BEGIN
  LOOP
    display (NULL, SUBSTR(l_data, 1, g_max_width));
    l_data := SUBSTR(l_data, g_max_width + 1);
    EXIT WHEN l_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
      l_file  UTL_FILE.file_type;
    BEGIN
      l_file := UTL_FILE.fopen (g_file_dir, g_file_name, 'A');
      UTL_FILE.put_line(l_file, p_data);
      UTL_FILE.fclose (l_file);
    EXCEPTION
      WHEN OTHERS THEN
        UTL_FILE.fclose (l_file);
    END;
  END IF;
END;
-- --------------------------------------------------------------------------

END dsp;
/

SHOW ERRORS

The test_dsp.sql script creates a package specification and body that includes calls to the dsp package to trace program execution. 

The package contains three procedures, proc_1, proc_2, and proc_3, with proc_1 conditionally calling the other procedures.  Once the package is compiled, the action of the instrumentation will be visible.

test_dsp.sql

CREATE OR REPLACE PACKAGE test_dsp AS

PROCEDURE proc_1 (p_id    IN  NUMBER,
                  p_type  IN  VARCHAR2);

PROCEDURE proc_2 (p_id    IN  NUMBER);

PROCEDURE proc_3 (p_id    IN  NUMBER);

END test_dsp;
/
SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY test_dsp AS

-- ------------------------------------------------------------------
PROCEDURE proc_1 (p_id    IN  NUMBER,
                  p_type  IN  VARCHAR2) AS
-- ------------------------------------------------------------------
BEGIN
  DSP.line('--------------------------------------------);
  DSP.line('test_dsp.proc_1', 'Started - p_id=' || p_id || ' p_type=' || p_type);


  IF p_type = 'BIG' THEN
    proc_2(p_id => p_id);
  ELSE
    proc_3(p_id => p_id);
  END IF;

  DSP.line('test_dsp.proc_1', 'Finished - p_id=' || p_id || ' p_type=' || p_type);
EXCEPTION
  WHEN OTHERS THEN
    DSP.line('test_dsp.proc_1', 'Finished - p_id=' || p_id || ' p_type=' || p_type ||
                                ' - OTHERS - ' || SQLERRM);
END proc_1;
-- ------------------------------------------------------------------

-- ------------------------------------------------------------------
PROCEDURE proc_2 (p_id    IN  NUMBER) AS
-- ------------------------------------------------------------------
BEGIN
  DSP.line('test_dsp.proc_2', 'Started - p_id=' || p_id);

  -- Raise an error.

  RAISE TOO_MANY_ROWS;

  DSP.line('test_dsp.proc_2', 'Finished - p_id=' || p_id);
END proc_2;
-- ------------------------------------------------------------------

-- ------------------------------------------------------------------
PROCEDURE proc_3 (p_id    IN  NUMBER) AS
-- ------------------------------------------------------------------
BEGIN
  DSP.line('test_dsp.proc_3', 'Started - p_id=' || p_id);

  -- Do something.

  NULL;

  DSP.line('test_dsp.proc_3', 'Finished - p_id=' || p_id);
END proc_3;
-- ------------------------------------------------------------------

END test_dsp;
/
SHOW ERRORS

By default the instrumentation is always switched off, so before executing a procedure, the dsp trace must be turned on.  The following commands show how the trace is enabled to use the dbms_output package.

SQL> SET SERVEROUTPUT ON SIZE 100000
SQL> EXEC DSP.show_output_on;

PL/SQL procedure successfully completed.

SQL> EXEC DSP.show_date_on;

PL/SQL procedure successfully completed.

The first command is required to switch on the output from the dbms_output package, while the second and third commands turn on the dsp trace and associate a date-time with each trace message.

Rather than use this method, the trace will be outputted directly to a file on the database server, which is done using the following commands.

SQL> CREATE OR REPLACE DIRECTORY trace_dir AS '/tmp/';

Directory created.

SQL> EXEC DSP.show_output_on;

PL/SQL procedure successfully completed.

SQL> EXEC DSP.file_output_on('TRACE_DIR', 'dsp.log');

PL/SQL procedure successfully completed.

SQL> EXEC DSP.show_date_on;

PL/SQL procedure successfully completed.

First a directory object is created to point to the location of the trace file.  Then the trace is switched on, and the output directed to a file.  Finally a date-time is associated with the trace messages.

With the trace turned on, the proc_1 procedure is called with different p_type parameters to show the affect on the trace output.

SQL> EXEC test_dsp.proc_1(p_id => 1234, p_type => 'BIG');

PL/SQL procedure successfully completed.

SQL> EXEC test_dsp.proc_1(p_id => 1234, p_type => 'SMALL');

PL/SQL procedure successfully completed.

The trace written to the log file by these two procedure calls is displayed below.

29-AUG-2005 09:06:20.084000000 : --------------------------------------------

29-AUG-2005 09:06:20.084000000 : test_dsp.proc_1 : Started - p_id=1234 p_type=BIG

29-AUG-2005 09:06:20.084000000 : test_dsp.proc_2 : Started - p_id=1234

29-AUG-2005 09:06:20.084000000 : test_dsp.proc_1 : Finished - p_id=1234 p_type=BIG - OTHERS - ORA-01422: exact fetch returns more than requested number of rows

29-AUG-2005 09:06:28.365000000 : --------------------------------------------

29-AUG-2005 09:06:28.365000000 : test_dsp.proc_1 : Started - p_id=1234 p_type=SMALL

29-AUG-2005 09:06:28.365000000 : test_dsp.proc_3 : Started - p_id=1234

29-AUG-2005 09:06:28.365000000 : test_dsp.proc_3 : Finished - p_id=1234

29-AUG-2005 09:06:28.365000000 : test_dsp.proc_1 : Finished - p_id=1234 p_type=SMALL

It is evident from this output that the first call resulted in a call to the proc_2 procedure which raised a too_many_rows exception, while the second call completed successfully.

For the sake of completeness, it is worth mentioning the format_error_backtrace function which has been added to the dbms_utility package in Oracle 10g.  Unlike other error reporting functions, this function returns the error stack including line numbers, making the identification of problems in the code much simpler.  The output from this function can be demonstrated by substituting the follow exception handler into the test_dsp package.

 EXCEPTION
  WHEN OTHERS THEN
    DSP.line('test_dsp.proc_1', 'Finished - p_id=' || p_id || ' p_type=' || p_type ||
                                ' - OTHERS - ' || SQLERRM || CHR(10) ||
                                DBMS_UTILITY.format_error_backtrace);
 

The output from the exception handler is displayed in the call to the proc_1 procedure below.

23-MAY-2005 11:22:59.128370000 : test_dsp.proc_1 : Finished - p_id=1234 p_type=BIG - OTHERS -
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "TEST.TEST_DSP", line 33
ORA-06512: at "TEST.TEST_DSP", line 11

The examples shown above demonstrate the usefulness of code instrumentation.  In most situations, the trace output is the first thing to check when attempting to identify a performance or logic problem in the code.

The following sections explore the use of the dbms_application_info, dbms_session and dbms_system packages for identifying sessions and providing information about their current actions.  These packages do not provide instrumentation as such, but their use can be closely tied to code instrumentation, hence their inclusion.

 

This is an excerpt from the bestselling book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006.

You can buy the book for only $23.95 (30%-off) when you buy directly from the publisher, and you also get instant access to the code depot of PL/SQL tuning scripts:


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational