DBMS_OUTPUT - Debug & Display Output in Oracle PL/SQL

DBMS_OUTPUT

Overview

DBMS_OUTPUT is Oracle’s built-in package for writing text from PL/SQL blocks, procedures, functions, and triggers to a buffer that client tools can display after execution completes. It is the simplest way to add debugging output to PL/SQL code during development and is available in every Oracle edition without any additional privileges. SQL*Plus, SQL Developer, and SQLcl all support it natively.

Package: DBMS_OUTPUT Available Since: Oracle 6 Required Privileges: None — PUBLIC execute grant is supplied by default in all Oracle installations

Understanding its limitations is as important as understanding its use: DBMS_OUTPUT writes to an in-memory buffer, not to a console in real time. All output is held until the calling block completes and the client retrieves it. This makes it unsuitable for monitoring long-running processes live, and entirely inappropriate for production application logging.

---

Enabling Output in SQL*Plus and SQLcl

Output is disabled by default in every session. You must enable it before running any PL/SQL block whose output you want to see:

-- Enable with no explicit buffer size (uses unlimited in Oracle 10g+)
SET SERVEROUTPUT ON

-- Enable with a specific buffer size (bytes) — use for Oracle 9i or to constrain memory
SET SERVEROUTPUT ON SIZE 1000000

-- Enable unlimited buffer (Oracle 10g+, recommended for development)
SET SERVEROUTPUT ON SIZE UNLIMITED

-- Enable with line wrapping at 80 characters
SET SERVEROUTPUT ON SIZE UNLIMITED FORMAT WRAPPED

-- Enable with word-based wrapping
SET SERVEROUTPUT ON SIZE UNLIMITED FORMAT WORD_WRAPPED

-- Disable output collection
SET SERVEROUTPUT OFF

The SET SERVEROUTPUT ON command is a SQL*Plus/SQLcl client instruction — it calls DBMS_OUTPUT.ENABLE on your behalf and instructs the client to call DBMS_OUTPUT.GET_LINES after each anonymous block or procedure call to retrieve and display buffered output.

---

Core Procedures

PUT_LINE

The most commonly used procedure. Writes a string followed by a newline to the output buffer:

DBMS_OUTPUT.PUT_LINE(item IN VARCHAR2);

BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello, world!');
    DBMS_OUTPUT.PUT_LINE('Current date: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
END;
/

The item parameter accepts VARCHAR2 up to 32,767 bytes per call. Attempting to pass a longer string raises ORA-20000.

---

PUT

Writes a string to the buffer without appending a newline. Output accumulates on the current line until NEW_LINE is called:

DBMS_OUTPUT.PUT(item IN VARCHAR2);

BEGIN
    DBMS_OUTPUT.PUT('Column 1: ');
    DBMS_OUTPUT.PUT('Value A  ');
    DBMS_OUTPUT.PUT('Column 2: ');
    DBMS_OUTPUT.PUT('Value B');
    DBMS_OUTPUT.NEW_LINE;   -- Flush the accumulated line to the buffer
END;
/

---

NEW_LINE

Terminates the current line and moves to the next. Equivalent to writing a newline character:

DBMS_OUTPUT.NEW_LINE;

---

ENABLE and DISABLE

Control buffer allocation explicitly. SQL*Plus calls these for you when you use SET SERVEROUTPUT, but in JDBC/OCI programs you must call ENABLE yourself:

-- Enable with a specific buffer size (in bytes); NULL = unlimited (10g+)
DBMS_OUTPUT.ENABLE(buffer_size IN INTEGER DEFAULT 20000);

-- Disable output collection and discard the buffer
DBMS_OUTPUT.DISABLE;

In Oracle 10g and later, passing NULL to ENABLE allocates an unlimited buffer. In Oracle 9i and earlier, the buffer was capped at 1,000,000 bytes (1 MB).

---

GET_LINE and GET_LINES

Retrieve buffered output programmatically. Used by clients (SQL*Plus, JDBC applications, monitoring scripts) to read lines after the PL/SQL block completes:

-- Retrieve one line at a time
DBMS_OUTPUT.GET_LINE(
    line   OUT VARCHAR2,
    status OUT INTEGER         -- 0 = line returned; 1 = no more lines
);

-- Retrieve multiple lines at once (more efficient for large output)
DBMS_OUTPUT.GET_LINES(
    lines      OUT CHARARR,    -- DBMS_OUTPUT.CHARARR is a TABLE OF VARCHAR2(32767)
    numlines   IN OUT INTEGER  -- Pass in the max lines to retrieve; returns actual count
);

Example — reading output from a JDBC application:

In Java you would call DBMS_OUTPUT.ENABLE first (in a CallableStatement), execute your PL/SQL block, then loop calling GET_LINES until numlines returns 0.

-- Reading output in a PL/SQL wrapper (e.g., for logging)
DECLARE
    l_lines   DBMS_OUTPUT.CHARARR;
    l_count   INTEGER := 100;
BEGIN
    -- Execute the block whose output you want to capture
    SOME_PROCEDURE_THAT_USES_DBMS_OUTPUT;

    -- Drain the buffer
    LOOP
        DBMS_OUTPUT.GET_LINES(l_lines, l_count);
        FOR i IN 1 .. l_count LOOP
            INSERT INTO debug_log (log_time, message)
            VALUES (SYSTIMESTAMP, l_lines(i));
        END LOOP;
        EXIT WHEN l_count < 100;
    END LOOP;
    COMMIT;
END;
/

---

Buffer Size and Limits

Oracle Version	Default Buffer	Maximum Buffer
Oracle 9i and earlier	20,000 bytes	1,000,000 bytes (1 MB)
Oracle 10g+	20,000 bytes	Unlimited (NULL = no cap)

The buffer is allocated per session in PGA memory. An unlimited buffer in a session performing heavy debug output can consume significant PGA. Always disable output or set a reasonable limit in batch scripts.

Line length limit: Each individual PUT_LINE call is limited to 32,767 bytes. To output longer strings, split them manually:

DECLARE
    l_long_text   VARCHAR2(32767) := RPAD('X', 32767, 'X');
    l_chunk_size  PLS_INTEGER := 255;
    l_pos         PLS_INTEGER := 1;
BEGIN
    WHILE l_pos <= LENGTH(l_long_text) LOOP
        DBMS_OUTPUT.PUT_LINE(SUBSTR(l_long_text, l_pos, l_chunk_size));
        l_pos := l_pos + l_chunk_size;
    END LOOP;
END;
/

---

Common Debugging Patterns

Tracing Loop Execution

DECLARE
    l_count   PLS_INTEGER := 0;
BEGIN
    FOR rec IN (SELECT table_name FROM user_tables ORDER BY table_name) LOOP
        l_count := l_count + 1;
        DBMS_OUTPUT.PUT_LINE(
            LPAD(l_count, 4) || '. ' || rec.table_name
        );
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('--- Total: ' || l_count || ' tables ---');
END;
/

Displaying Variable Values at Checkpoints

DECLARE
    l_step    VARCHAR2(50);
    l_value   NUMBER;
BEGIN
    l_step  := 'initialization';
    l_value := 0;
    DBMS_OUTPUT.PUT_LINE('[' || l_step || '] value = ' || l_value);

    -- Simulate processing
    l_step  := 'after loop';
    l_value := 42;
    DBMS_OUTPUT.PUT_LINE('[' || l_step || '] value = ' || l_value);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERROR at step [' || l_step || ']: ' || SQLERRM);
        RAISE;
END;
/

Formatted Table Output

DECLARE
    l_line VARCHAR2(200);
BEGIN
    -- Header
    DBMS_OUTPUT.PUT_LINE(RPAD('OWNER', 20) || RPAD('TABLE_NAME', 30) || LPAD('ROWS', 12));
    DBMS_OUTPUT.PUT_LINE(RPAD('-', 20, '-') || RPAD('-', 30, '-') || LPAD('-', 12, '-'));

    FOR rec IN (
        SELECT owner, table_name, NVL(num_rows, 0) AS num_rows
        FROM   dba_tables
        WHERE  owner = 'SALES'
        ORDER  BY num_rows DESC
        FETCH FIRST 10 ROWS ONLY
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(
            RPAD(rec.owner, 20) ||
            RPAD(rec.table_name, 30) ||
            LPAD(TO_CHAR(rec.num_rows, '999,999,999'), 12)
        );
    END LOOP;
END;
/

---

Using DBMS_OUTPUT in SQL Developer and SQLcl

SQL Developer enables server output automatically when you run a script or anonymous block from the Script Output window. You can also enable it explicitly from View > DBMS Output and click the green plus (+) button for your connection.

SQLcl uses the identical SET SERVEROUTPUT ON syntax as SQL*Plus.

---

Using DBMS_OUTPUT in JDBC and OCI Applications

DBMS_OUTPUT does not work automatically in JDBC or OCI client code. You must:

1. Call DBMS_OUTPUT.ENABLE(NULL) before your PL/SQL block
2. Execute your PL/SQL block
3. Loop calling DBMS_OUTPUT.GET_LINES to drain the buffer

-- Step 1: Enable before the block
BEGIN DBMS_OUTPUT.ENABLE(NULL); END;
/

-- Step 2: Execute your PL/SQL block
BEGIN
    MY_PACKAGE.MY_PROCEDURE;
END;
/

-- Step 3: Drain output (in a loop in your application code)
DECLARE
    l_lines   DBMS_OUTPUT.CHARARR;
    l_count   INTEGER := 50;
BEGIN
    DBMS_OUTPUT.GET_LINES(l_lines, l_count);
    FOR i IN 1 .. l_count LOOP
        -- Process l_lines(i) in your application
        NULL;
    END LOOP;
END;
/

---

Common Pitfalls

• Forgetting SET SERVEROUTPUT ON — The single most frequent issue. Output is silently discarded if the buffer is not enabled. Always add SET SERVEROUTPUT ON at the top of every SQL*Plus script that uses DBMS_OUTPUT.
• Output appears after the block completes — DBMS_OUTPUT is not a console logger. All output is buffered and only displayed when execution ends. You cannot use it to monitor the progress of a long-running loop in real time.
• Buffer overflow on Oracle 9i — On older versions, exceeding 1 MB triggers ORA-20000: ORU-10027: buffer overflow. Use DBMS_OUTPUT.DISABLE and re-enable with a larger size, or switch to an alternative logging method.
• Not available in triggers in some contexts — While technically callable from triggers, DBMS_OUTPUT in a trigger is only useful for debugging from SQL*Plus. Application code calling the DML will never see trigger output.
• No output in scheduled jobs — Output from DBMS_SCHEDULER or DBMS_JOB-executed procedures goes into the job’s void. Use table-based logging or UTL_FILE instead.

---

Production Alternatives

DBMS_OUTPUT is a development and debugging tool, not a production logging mechanism. For production use:

Table-Based Logging

-- Simple logging table
CREATE TABLE app_log (
    log_id    NUMBER GENERATED ALWAYS AS IDENTITY,
    log_time  TIMESTAMP DEFAULT SYSTIMESTAMP,
    severity  VARCHAR2(10),
    module    VARCHAR2(50),
    message   VARCHAR2(4000),
    CONSTRAINT app_log_pk PRIMARY KEY (log_id)
);

-- Logging procedure using an autonomous transaction
CREATE OR REPLACE PROCEDURE log_message (
    p_severity IN VARCHAR2,
    p_module   IN VARCHAR2,
    p_message  IN VARCHAR2
) AS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO app_log (severity, module, message)
    VALUES (p_severity, p_module, p_message);
    COMMIT;
END;
/

UTL_FILE — Write to OS Files

DECLARE
    l_file  UTL_FILE.FILE_TYPE;
BEGIN
    l_file := UTL_FILE.FOPEN(
        location  => 'LOG_DIR',          -- Oracle directory object
        filename  => 'process_' || TO_CHAR(SYSDATE,'YYYYMMDDHH24MI') || '.log',
        open_mode => 'W',
        max_linesize => 32767
    );
    UTL_FILE.PUT_LINE(l_file, TO_CHAR(SYSTIMESTAMP) || ' Processing started');
    -- ... process ...
    UTL_FILE.PUT_LINE(l_file, TO_CHAR(SYSTIMESTAMP) || ' Processing completed');
    UTL_FILE.FCLOSE(l_file);
EXCEPTION
    WHEN OTHERS THEN
        UTL_FILE.FCLOSE(l_file);
        RAISE;
END;
/

DBMS_APPLICATION_INFO — Lightweight Progress Monitoring

BEGIN
    DBMS_APPLICATION_INFO.SET_MODULE(
        module_name => 'NIGHTLY_BATCH',
        action_name => 'Step 1 of 5: Load staging'
    );
    -- ... processing ...

    DBMS_APPLICATION_INFO.SET_ACTION('Step 2 of 5: Validate');
    -- ... processing ...
END;
/

-- Monitor from another session
SELECT module, action, last_call_et, status
FROM   v$session
WHERE  username IS NOT NULL
AND    module   = 'NIGHTLY_BATCH';

---

Best Practices

1. Always SET SERVEROUTPUT ON at the top of SQL*Plus scripts — Make it a habit; silent loss of output wastes debugging time.
2. Use SIZE UNLIMITED on Oracle 10g+ — There is no reason to cap buffer size in development environments; the memory cost is negligible.
3. Remove DBMS_OUTPUT calls before promoting to production — Replace them with proper table-based or UTL_FILE logging. DBMS_OUTPUT left in production code is at best dead weight and at worst causes ORA-20000 buffer overflows.
4. Use conditional compilation to strip debug output — Wrap DBMS_OUTPUT calls in $IF $$DEBUG $THEN ... $END conditional compilation blocks so they are compiled out in production builds.
5. Prefer table logging with AUTONOMOUS_TRANSACTION for batch jobs — This gives you real-time visibility into batch progress from a separate session without the synchronisation constraints of the main transaction.
6. For JDBC applications, always drain the buffer — Call GET_LINES after every PL/SQL call that might produce output, even if you discard the results, to prevent buffer growth across multiple calls in the same session.

---

Related Resources

• V$SESSION — Monitor batch process progress via DBMS_APPLICATION_INFO.SET_MODULE and query MODULE and ACTION columns
• Oracle Scheduler — Schedule batch procedures; note that DBMS_OUTPUT is not visible from scheduled jobs
• UTL_FILE — Write structured output to server-side OS files from PL/SQL
• DBMS_APPLICATION_INFO — Lightweight mechanism to expose PL/SQL progress to V$SESSION for real-time monitoring without a logging table