Oracle DBMS_OUTPUT Demos

Oracle DBMS_OUTPUT Version 12.1.0.1

General Information

Library Note

Morgan's Library Page Footer

The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Purpose

Enables sending messages from stored procedures, packages, and triggers: Especially useful for displaying PL/SQL debugging information.

AUTHID

DEFINER

Data types

TYPE chararr IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
-- Note: was 255 bytes in 10gR1 and earlier

TYPE dbmsoutput_linesarray IS
VARRAY(2147483647) OF VARCHAR2(32767);

Dependencies

SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_OUTPUT'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_OUTPUT';

-- returns 141 objects

Documented

Yes

Exceptions

Error Code	Reason
ORU-10027	Buffer overflow, limit of <buf_limit> bytes
ORU-10028	Line length overflow, limit is 32767 bytes per line

First Available

7.3.4

Security Model

Owned by SYS with EXECUTE granted to PUBLIC and the APEX_040200 role

Source

{ORACLE_HOME}/rdbms/admin/dbmsotpt.sql

SQL*Plus

SET SERVEROUTPUT ON in SQL*Plus is equivalent to:

dbms_output.enable(buffer_size => NULL);

Subprograms

DISABLE	GET_LINES	PUT
ENABLE	NEW_LINE	PUT_LINE
GET_LINE

DISABLE

Disable DBMS_OUTPUT and reset the buffer size to the default

dbms_output.disable;

exec dbms_output.disable;

ENABLE

Enable DBMS_OUTPUT and set the buffer size. The buffer size can be between 1 and 1,000,000

dbms_output.enable(buffer_size IN INTEGER DEFAULT 20000);

exec dbms_output.enable(1000000);

GET_LINE

Returns a single line of buffered information

dbms_output.get_line(line OUT VARCHAR2, status OUT INTEGER);

set serveroutput on

DECLARE
buffer VARCHAR2(100);
status INTEGER;
BEGIN
dbms_output.put_line('This is');
dbms_output.put_line('a test.');
dbms_output.get_line(buffer, status);
dbms_output.put_line('Buffer: ' || buffer);
dbms_output.put_line('Status: ' || TO_CHAR(status));
END;
/

GET_LINES

Retrieves an array of lines from the buffer

Overload 1

dbms_output.get_lines(lines OUT CHARARR, numlines IN OUT INTEGER);

set serveroutput on

DECLARE
outtab dbms_output.chararr;
fetchln INTEGER := 15;
BEGIN
outtab(1) := 'This is a test';
outtab(12) := 'of dbms_output.get_lines';

dbms_output.put_line('A: ' || outtab(1));
dbms_output.put_line('A: ' || outtab(12));

dbms_output.get_lines(outtab, fetchln);
dbms_output.put_line(TO_CHAR(fetchln));
/*
FOR i IN 1 .. fetchln LOOP
dbms_output.put_line('B: ' || outtab(i));
END LOOP;
*/
END;
/

DECLARE
outtab dbms_output.chararr;
fetchln INTEGER := 15;
BEGIN
outtab(1) := 'This is a test';
outtab(12) := 'of dbms_output.get_lines';

dbms_output.put_line('A: ' || outtab(1));
dbms_output.put_line('A: ' || outtab(12));

dbms_output.get_lines(outtab, fetchln);
dbms_output.put_line('FETCHLN: ' || TO_CHAR(fetchln));

FOR i IN 1 .. fetchln LOOP
dbms_output.put_line('B: ' || outtab(i));
END LOOP;
END;
/

Overload 2

dbms_output.get_lines(
lines OUT dbmsoutput_linesarray,
numlines IN OUT INTEGER);

set serveroutput on

DECLARE
lo dbmsoutput_linesarray := dbmsoutput_linesarray('Test Message');
BEGIN
dbms_output.put_line(lo(1));
END;
/

DECLARE
lo dbmsoutput_linesarray := dbmsoutput_linesarray();
fetchln INTEGER;
BEGIN
FOR i IN 65 .. 70 LOOP
lo.extend;
    lo(i-64) := CHR(i) || CHR(i+1) || CHR(i+2);
END LOOP;
dbms_output.put_line('Array Size Is: ' || TO_CHAR(lo.LAST));

FOR i IN 1 .. lo.LAST LOOP
    dbms_output.put_line(lo(i));
END LOOP;

-- dbms_output.get_lines(lo, fetchln);
-- dbms_output.put_line('FETCHLN: ' || TO_CHAR(fetchln));
END;
/

DECLARE
lo dbmsoutput_linesarray := dbmsoutput_linesarray();
fetchln INTEGER;
BEGIN
FOR i IN 65 .. 70 LOOP
lo.extend;
    lo(i-64) := CHR(i) || CHR(i+1) || CHR(i+2);
END LOOP;
dbms_output.put_line(Array Size Is: ' || TO_CHAR(lo.LAST));

FOR i IN 1 .. lo.LAST LOOP
    dbms_output.put_line(lo(i));
END LOOP;

dbms_output.get_lines(lo, fetchln);
dbms_output.put_line('FETCHLN: ' || TO_CHAR(fetchln));
END;
/

NEW_LINE

Inserts an end-of-line marker

dbms_output.new_line;

set serveroutput on

BEGIN
dbms_output.enable(9999999);
dbms_output.new_line();

FOR rec IN (SELECT table_name FROM user_tables)
LOOP
dbms_output.put_line (rec.table_name);
END LOOP;
dbms_output.new_line();
END;
/

PUT

Put a piece of information in the buffer

dbms_output.put(a IN VARCHAR2);

Obsolete and no longer supported by Oracle

PUT_LINE

Output a literal

dbms_output.put_line(a IN VARCHAR2);

set serveroutput on

BEGIN
dbms_output.put_line('Display a string literal');
END;
/

Output a variable

set serveroutput on size 1000000 format wrapped

DECLARE
x VARCHAR2(20) := RPAD('Dan Morgan', 199, 'x')
BEGIN
dbms_output.put_line(x);
END;
/