Oracle DBMS_OUTPUT
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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 21c
What's New In 23c

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx