 |
|
Oracle dbms_output tips
Oracle Tips by Burleson Consulting
|
Oracle dbms_output
The PL/SQL dbms_output package has a
put_line procedure to allow you to write
data to flat file or to direct your PL/SQL
output to a screen.
When using dbms_output inside SQL*Plus,
you need to use the "set serveroutput on
10000" command (where 10000 is the display
arraysize argument) to see the PL/SQL output
directly with dbms_output.put_line.
Here is a code example using dbms_output:
dbms_output.put_line('Fixed IO/SEC:'||to_char(fixed_io_per_sec,'9,999,999.99'));
dbms_output.put_line('Temp IO/SEC :'||to_char(temp_io_per_sec,
'9,999,999.99'));
dbms_output.put_line('Total IO/SEC:'||to_char(tot_io_Per_Sec,
'9,999,999.99'));
dbms_output.put_line('FS1 Blocks =
'||v_fs1_blocks);
dbms_output.put_line('FS2 Blocks =
'||v_fs2_blocks);
dbms_output.put_line('FS3 Blocks =
'||v_fs3_blocks);
dbms_output.put_line('FS4 Blocks =
'||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;
More information
on dbms_output
The dbms_output package was introduced in Oracle7. Dbms_output is used to
allow output to the SQL buffer in SQL*Plus from PL/SQL blocks. The
dbms_output package was intended primarily as a debugging tool. The
dbms_output package is now being supplanted by Oracle’s step-through
debuggers and several other third-party tools.
The dbms_output
package is often used to deal with runtime errors. Dbms_output can be used
to isolate the location of an error so that a developer can correct the
problem. The dbms_output package provides an excellent debugging tool when
used properly.
In order to use the
DBMS_Output
package for debugging, you must issue the following command.
set serveroutput on
Oracle 11g provides the following information about the DBMS_OUTPUT package:
Description of the DBMS_OUTPUT package:
PROCEDURE DBMS_OUTPUT.DISABLE
PROCEDURE DBMS_OUTPUT.ENABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
BUFFER_SIZE NUMBER(38) IN DEFAULT
PROCEDURE DBMS_OUTPUT.GET_LINE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LINE VARCHAR2 OUT
STATUS NUMBER(38) OUT
PROCEDURE DBMS_OUTPUT.GET_LINES
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LINES TABLE OF VARCHAR2(32767) OUT
NUMLINES NUMBER(38) IN/OUT
PROCEDURE DBMS_OUTPUT.GET_LINES
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
LINES DBMSOUTPUT_LINESARRAY OUT
NUMLINES NUMBER(38) IN/OUT
PROCEDURE DBMS_OUTPUT.NEW_LINE
PROCEDURE DBMS_OUTPUT.PUT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
A VARCHAR2 IN
PROCEDURE DBMS_OUTPUT.PUT_LINE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
A VARCHAR2 IN
Related notes on DBMS_OUTPUT:
For more information on dbms_output see here.
