20. november 2005 - 11:46
#3
En god artikel om det du har brug for:
Bookmark Go to End
Doc ID: Note:1005663.6
Subject: Debugging PL/SQL Programs Using DBMS_OUTPUT
Type: PROBLEM
Status: PUBLISHED
Content Type: TEXT/X-HTML
Creation Date: 30-SEP-1994
Last Revision Date: 12-JUL-2005
To aid in the development of PL/SQL programs, stored procedures, functions, and
triggers, Oracle provides a public package named DBMS_OUTPUT for displaying
debugging information. The procedures in this package provide much the same
functionality as the printf() function in C. This package is not designed to be
used for report displaying, or formatting output to the standard output.
This bulletin explains how to debug PL/SQL programs using DBMS_OUTPUT. An
explanation of the package and its component procedures is provided as well as
some examples of their use.
______________________________________________________________________________
Debugging PL/SQL Programs Using DBMS_OUTPUT
Introduction:
To aid in the development of PL/SQL programs, stored procedures, and triggers,
Oracle provides a public package named DBMS_OUTPUT for displaying debugging
information. The procedures in this package provide much the same functionality
as the printf() function in C.
To create the DBMS_OUTPUT package, the script DBMSOTPT.SQL must be run by
the user SYS. This script is run automatically by the CATPROC.SQL script.
Both of these scripts can be found in $ORACLE_HOME/rdbms/admin on unix systems,
or in the ORA_RDBMS directory on VMS. These scripts create the package - to
run it you will also need execute privilege on it.
DBMS_OUTPUT and how it is used:
The package is provided by Oracle to help programmers in debugging PL/SQL
programs, procedures, and triggers. It is not designed to be used for report
displaying, or formatting output to the standard output. The package
is designed to display debugging info in stream-like processing. That is, the
variables, string literals, and other info are written to a buffer. The buffer
is divided into lines, each of which can have up to 255 characters. There is
no other structure on the buffer. DBMS_OUTPUT is most helpful when used with
SQL*Plus and SQL*DBA. In order to use the package with these two tools, you
must set the 'serveroutput' before any info can be displayed to the screen.
Use the following command in SQL*Plus or SQL*DBA:
SQL> set serveroutput on
Note that 'serveroutput' is one word. If serveroutput is not turned on, nothing
will be displayed even if you call the DBMS_OUTPUT.ENABLE packaged procedure.
The way DBMS_OUTPUT works is as follows:
In a PL/SQL program, calls to DBMS_OUTPUT.PUT_LINE will add lines to the buffer.
Nothing is actually displayed until the program executes completely and control
is transfered back to SQL*Plus or SQL*DBA. At this point, if serveroutput is on,
SQL*Plus or SQL*DBA will make a call to DBMS_OUTPUT.GET_LINES and print the
results to the screen.
If your PL/SQL program runs for a long time, you actually have to wait until
the program executes and control goes back to SQL*Plus in order to see any
information displayed by DBMS_OUTPUT.PUT_LINE. Consider the following
example - I had to wait about 13 minutes before the output was displayed:
--PL/SQL script out1.sql displays time in hh-mi-ss format at the beginning
--of the script, loops to eat cpu time, and displays time at the end of the
--script.
-- Calls to the DBMS_OUTPUT package are capitalized for emphasis - as with
-- all database objects they are really case-insenitive.
DECLARE
y1 varchar2(80);
y2 number;
BEGIN
SELECT to_char(sysdate, 'hh-mi-ss')
into y1
from dual;
DBMS_OUTPUT.PUT_LINE(y1);
y2 := 0;
LOOP
IF y2 > 100000 THEN
exit;
END IF;
y2 := y2 + 1;
END LOOP;
SELECT to_char(sysdate, 'hh-mi-ss')
into y1
from dual;
DBMS_OUTPUT.PUT_LINE(y1);
END;
/
SQL> @out1
12-43-26
12-56-19
PL/SQL procedure successfully completed.
Notice how long the program took to execute. Both prints were done at the same
time - they are not printed as the block is executing.
Calls to DBMS_OUTPUT.PUT_LINE are dispalyed even if a rollback was issued at
the end of the PL/SQL script, the following example demonstrates this:
SQL> BEGIN
2 dbms_output.put_line('test rollback');
3 ROLLBACK;
4 END;
5 /
test rollback
PL/SQL procedure successfully completed.
Even if the script has an unhandled exception, all the calls to
DBMS_OUTPUT.PUT_LINE before the unhandled exception are displayed. Example:
SQL> DECLARE
2 x number;
3 BEGIN
4 dbms_output.put_line('test errors');
5 SELECT 'Junk' into x from dual;
6 END;
7 /
test errors
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 5
Notice how the line was displayed regardless of the unhandled exception. It
should be noted that in versions of SQL*Plus prior to 3.1.2.2, SQL*Plus did
not call dbms_output.get_lines to display the information in the buffer if an
unhandled exception occurred.
Procedures in DBMS_OUTPUT package:
procedure enable(bufsize IN default 2000);
This procedure enables calls to put, put_line, new_line, get_line, and
get_lines. If the dbms_output package has not been enabled, calls to these
procedures will be ignored. If you use the dbms_output.disable procedure
in your PL/SQL block, you will have to call dbms_output.enable if you later
want to re-enable calls to procedures put, put_line, new_line, get_line,
and get_lines.
This procedure is used to change the default size of the buffer. The default
and minimum size is 2000. Maximum is 1,000,000. When you set serveroutput
on, dbms_output.enable is implicitly called, with the default size of 2000.
Example:
SQL> set serveroutput on
SQL> BEGIN
2 dbms_output.put('Example, show that enable is not needed ');
3 dbms_output.put_line('when "set serveroutput on" is used.');
4 END;
5 /
Example, show that enable is not needed when "set serveroutput on" is used.
PL/SQL procedure successfully completed.
procedure disable;
This procedure will disable all calls to put, put_line, new_line,
get_line, and get_lines. It will also clear the buffer of all
information. If you call the procedure after calling
dbms_output.put_line, nothing will be displayed. The SQL*Plus and
SQL*DBA command "set serveroutput off" does the same thing.
Example:
SQL> set serveroutput on
SQL> BEGIN
2 dbms_output.put_line('Example, show that enable is not needed');
3 dbms_output.disable;
4 END;
5 /
PL/SQL procedure successfully completed.
Notice that the buffer was cleared before the block ended, so nothing was
displayed.
procedure put(a varchar2);
procedure put(a number);
procedure put(a date);
Input parameters:
-- a
-- Item to buffer
This procedure puts the specified piece of information in the buffer. The
procedure dbms_output.new_line or dbms_output.put_lines must be called after
one or more calls to dbms_output.put to end the line. A line that has not
been terminated with a newline character will not be displayed by a
subsequent call to dbms_output.get_lines.
procedure put_line(a varchar2);
procedure put_line(a number);
procedure put_line(a date);
Input parameters:
-- a
-- Item to buffer
This procedure puts the specified piece of information in the buffer followed
by an end-of-line marker.
procedure new_line;
This procedure places an end-of-line marker in the buffer.
procedure get_line(line OUT varchar2, status OUT integer);
procedure get_lines(lines OUT chararr, numlines IN OUT integer);
Output parameters:
-- line
-- Holds the line to be displayed
-- status
-- This will be 0 upon successful completion of the call. A 1 means there
-- are no more lines.
-- lines
-- Type chararr is a PL/SQL table defined in the DBMS_OUTPUT package.
-- type chararr is table of varchar2(255) index by binary_integer;
-- When using this type in your Pl/SQL script, you should declare a
-- variable as
-- declare
-- var1 dbms_output.chararr;
-- If you redefine the type again, an Oracle error will be issued.
Input parameters:
-- numlines
-- The maximum number of lines to be returned.
These two procedures are used to get lines back from the buffer into varchar2
variables declared in the PL/SQL program. If you call put_line after calling
get_line or get_lines, the buffer will be cleared and the only thing
displayed is the information provided with calls to put_line afterward.
The different between get_line and get_lines is :
get_lines can return more than one line from buffer.
Example:
SQL> set serveroutput on
SQL> declare
2 y1 varchar2(80);
3 y2 number;
4 begin
5 dbms_output.put_line('First line before dbms_out.get_line');
6 dbms_output.put_line('Second line before dbms_out.get_line');
7 dbms_output.get_line(y1,y2);
8 dbms_output.put_line('Line after dbms_out.get_line');
9 end;
10 /
Line after dbms_out.get_line
PL/SQL procedure successfully completed.
Notice how the buffer was cleared by the call to get_line, so the only line
displayed is the argument to put_line after the get_line call.
Notes:
1) As the package is implemented currently, there is no way to flush the
buffer, print the contents, and then add new lines to it. This is because the
buffer is not actually displayed to the screen until the block ends, and
SQL*Plus or SQL*DBA calls get_lines and prints the buffer.
2) If you run into the error:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
just call enable with a larger buffer size - it can go up to 1,000,000.
It is possible also to modify buffer size in SQL*PLUS and SQL*DBA by using the
following command:
SET SERVEROUTPUT ON SIZE n
where n is an integer between 2,000 and 1,000,000.
3) There is little reason to use the procedures in DBMS_OUPUT with the
precompilers. You can make calls to put_line, but the buffer will not be
printed to the screen automatically. You could call get_line to get data from
the buffer back into host variables and display them using regular host
language output statements, but in this case, it would make more sense to
assign to the host variables directly and bypass the buffer entirely.
4) DBMS_OUTPUT is not designed to be a report writer. It is meant to be
used for debugging. It is not really powerful enough for much more than this.
5) For more information, look at Appendix A of the Oracle7 Server Application
Developer's Guide. The DBMSOTPT.SQL file also has descriptions of the package
and the procedures.
______________________________________________________________________________
.
