DBA Tips Archive for Oracle
Debugging PL/SQL with
the DBMS_OUTPUT Package
by Jeff Hunter, Sr. Database Administrator
One of Oracle's most widely used standard package for developers and DBAs is DBMS_OUTPUT. This package comes in very handy when needing to debug a PL/SQL program. DBMS_OUTPUT allows you to display information to your session's output device as your PL/SQL program executes. DBMS_OUTPUT also comes in handy when generating reports from PL/SQL that need to be run in SQL*Plus. The DBMS_OUTPUT package became available with PL/SQL Version 2.
|DISABLE procedure||Disables output from the package. The DBMS_OUTPUT buffer will not not be flushed to the screen.|
|ENABLE procedure||Enables output from the package.|
|GET_LINE procedure||Gets a single line from the buffer.|
|GET_LINES procedure||Gets specified number of lines from the buffer and passes them into a PL/SQL table.|
|NEW_LINE procedure||Inserts an end-of-line mark in the buffer.|
|PUT procedure||Puts information into the buffer.|
|PUT_LINE procedure||Puts information into the buffer and appends an end-of-line marker after that data.|
The buffer size can be set to a size between 2,000 and 1,000,000 bytes with the ENABLE procedure. The buffer can store only three different types of data - VARCHAR2, NUMBER and DATE in their internal representation. The buffer will be contained in the SGA of the database instance.
The following is an example of an anonymous PL/SQL block that uses DBMS_OUTPUT to display the name of all distinct users in the table USER_NAMES and a count of how many time their name appears:
BEGIN DBMS_OUTPUT.ENABLE(100000); FOR user_rec IN (select name, count(*) Count from user_names group by name) LOOP DBMS_OUTPUT.PUT_LINE ('Name: ' || user_rec.name || ' (' || user_rec.count || ')'); END LOOP; END; /The above code generates the following output when executed in SQL*Plus:
Name: Abe Smith (999) Name: Amy Brown (1) Name: Default User (16579) Name: John Smith (2922)Make sure that serveroutput is set to ON in SQL*Plus before executing the above code segment. If this value is not set to ON, you will not be able to see the output from the DBMS_OUTPUT packge. In a SQL*Plus session type:
set serveroutput on
Copyright (c) 1998-2018 Jeffrey M. Hunter. All rights reserved.
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at firstname.lastname@example.org.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.