Oracle DBMS_OUTPUT
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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;

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 172 objects
Documented Yes: Packages and Types Reference
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 DBSFWUSER and PUBLIC
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;
 pragma restrict_references(disable,WNDS,RNDS);
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);
pragma restrict_references(enable,WNDS,RNDS);
exec dbms_output.enable(1000000);
 
GET_LINE
Returns a single line of buffered information dbms_output.get_line(line OUT VARCHAR2, status OUT INTEGER);
pragma restrict_references(get_line,WNDS,RNDS);
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);
pragma restrict_references(get_lines,WNDS,RNDS);
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);
pragma restrict_references(get_lines,WNDS,RNDS);
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;
pragma restrict_references(new_line,WNDS,RNDS);
set serveroutput on

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

  FOR rec IN (SELECT table_name FROM user_all_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);
pragma restrict_references(put,WNDS,RNDS);
Obsolete and no longer supported by Oracle
 
PUT_LINE
Output a literal dbms_output.put_line(a IN VARCHAR2);
pragma restrict_references(put_line,WNDS,RNDS);
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;
/

Related Topics
Built-in Functions
Built-in Packages
DBMS_MACOUT
Procedures
Table Triggers
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2021 Daniel A. Morgan All Rights Reserved
  DBSecWorx