Oracle | DBMS

Oracle DBMS_OUTPUT Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

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' ORDER BY 1; Returns 184 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;

      

      PL/SQL procedure successfully completed.

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);

      

      PL/SQL procedure successfully completed.

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;

      /

      Display a string literal

      

      PL/SQL procedure successfully completed.

Output a variable set serveroutput on size 1000000 format wrapped DECLARE x dbms_id := RPAD('Dan Morgan', 30, 'x'); BEGIN dbms_output.put_line(x); END; / Dan Morganxxxxxxxxxxxxxxxxxxxx PL/SQL procedure successfully completed.