oracle__skills/db/plsql/plsql-debugging.md

PL/SQL Debugging

Overview

Debugging PL/SQL requires a range of techniques from simple output utilities to full interactive debuggers. Understanding the strengths and limitations of each approach enables faster issue isolation in both development and production environments.

---

DBMS_OUTPUT

DBMS_OUTPUT is the simplest debugging tool — it writes messages to a server-side buffer that is displayed to the client after the PL/SQL block completes.

Basic Usage

-- Enable output in SQL*Plus / SQLcl
SET SERVEROUTPUT ON SIZE UNLIMITED

-- Enable in SQL Developer: View > DBMS Output panel > click the green +

BEGIN
  DBMS_OUTPUT.PUT_LINE('Starting process');
  DBMS_OUTPUT.PUT('Partial line ');  -- no newline
  DBMS_OUTPUT.PUT_LINE('continues here');
  DBMS_OUTPUT.NEW_LINE;  -- blank line
  DBMS_OUTPUT.PUT_LINE('Done');
END;
/

Buffer Management

-- Default buffer is 20,000 bytes in older releases
-- In 10g+, set to UNLIMITED in SQL*Plus
SET SERVEROUTPUT ON SIZE UNLIMITED

-- In PL/SQL: explicitly enable and set buffer size
DBMS_OUTPUT.ENABLE(buffer_size => NULL);  -- NULL = unlimited

-- Flush buffer manually (rarely needed; happens automatically at block end)
DBMS_OUTPUT.GET_LINES(lines_array, numlines);  -- programmatic read

Limitations of DBMS_OUTPUT

Limitation	Detail
Not real-time	Output appears only AFTER the entire block completes
Buffer overflow	With bounded size, ORA-20000 if buffer full
Not visible in production	Applications don't read DBMS_OUTPUT
Alters session state	ENABLE/DISABLE affects buffer availability
Multi-session	Only visible in the originating session
Not for high-frequency loops	Performance overhead per call

-- Pattern: conditional debug output using package flag
CREATE OR REPLACE PACKAGE debug_pkg AS
  g_debug BOOLEAN := FALSE;

  PROCEDURE enable;
  PROCEDURE disable;
  PROCEDURE log(p_msg IN VARCHAR2);
END debug_pkg;
/

CREATE OR REPLACE PACKAGE BODY debug_pkg AS
  PROCEDURE enable  IS BEGIN g_debug := TRUE;  END;
  PROCEDURE disable IS BEGIN g_debug := FALSE; END;

  PROCEDURE log(p_msg IN VARCHAR2) IS
  BEGIN
    IF g_debug THEN
      DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF3') || ' | ' || p_msg);
    END IF;
  END log;
END debug_pkg;
/

-- Usage: enable temporarily, then disable
BEGIN
  debug_pkg.enable;
  process_orders;
  debug_pkg.disable;
END;
/

---

DBMS_APPLICATION_INFO

DBMS_APPLICATION_INFO sets the MODULE, ACTION, and CLIENT_INFO fields visible in V$SESSION. This is valuable for monitoring long-running operations in production without modifying the business logic significantly.

CREATE OR REPLACE PROCEDURE process_month_end_close(
  p_period_id IN NUMBER
) IS
  l_count NUMBER := 0;
BEGIN
  -- Set module/action visible in V$SESSION
  DBMS_APPLICATION_INFO.SET_MODULE(
    module_name => 'MONTH_END_CLOSE',
    action_name => 'INITIALIZING'
  );

  DBMS_APPLICATION_INFO.SET_CLIENT_INFO('period_id=' || p_period_id);

  -- Phase 1
  DBMS_APPLICATION_INFO.SET_ACTION('VALIDATE_OPEN_ITEMS');
  validate_open_items(p_period_id);

  -- Phase 2
  DBMS_APPLICATION_INFO.SET_ACTION('POST_JOURNAL_ENTRIES');
  FOR rec IN (SELECT * FROM pending_journals WHERE period_id = p_period_id) LOOP
    post_journal(rec.journal_id);
    l_count := l_count + 1;

    -- Update progress visible in V$SESSION
    DBMS_APPLICATION_INFO.SET_ACTION(
      'POSTING_JOURNALS (' || l_count || ' done)'
    );
  END LOOP;

  -- Phase 3
  DBMS_APPLICATION_INFO.SET_ACTION('GENERATE_REPORTS');
  generate_period_reports(p_period_id);

  -- Clear on completion
  DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);

END process_month_end_close;
/

-- Monitor progress from another session (DBA)
SELECT sid, module, action, client_info, last_call_et AS seconds_running
FROM   v$session
WHERE  module = 'MONTH_END_CLOSE';

Long Operations Tracking

-- Register a long operation visible in V$SESSION_LONGOPS
DECLARE
  l_rindex   BINARY_INTEGER;
  l_slno     BINARY_INTEGER;
  l_total    NUMBER := 1000;
  l_sofar    NUMBER := 0;
BEGIN
  l_rindex := DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS_NOHINT;

  FOR i IN 1..l_total LOOP
    -- ... process row i ...
    l_sofar := i;

    IF MOD(i, 100) = 0 THEN  -- update every 100 rows
      DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
        rindex      => l_rindex,
        slno        => l_slno,
        op_name     => 'Process Employee Records',
        target      => 0,
        context     => 0,
        sofar       => l_sofar,
        totalwork   => l_total,
        target_desc => 'employees table',
        units       => 'rows'
      );
    END IF;
  END LOOP;
END;
/

-- Monitor from another session
SELECT opname, sofar, totalwork,
       ROUND(sofar/totalwork * 100, 1) AS pct_complete,
       elapsed_seconds
FROM   v$session_longops
WHERE  sofar < totalwork
  AND  opname = 'Process Employee Records';

---

SQL Developer PL/SQL Debugger

SQL Developer provides an interactive debugger with breakpoints, watches, and step execution.

Setup

1. Grant debug privilege to your user:

-- Required before debugging
GRANT DEBUG CONNECT SESSION TO my_dev_user;
GRANT DEBUG ANY PROCEDURE TO my_dev_user;  -- or specific objects

2. Compile with debug info (required for line-level debugging):

-- Compile with debug symbols
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 1;  -- disable optimizer for accurate line tracking

ALTER PROCEDURE my_procedure COMPILE DEBUG;
-- or via SQL Developer: right-click > Compile for Debug

Debugging Steps

1. Open the procedure in SQL Developer editor
2. Click in the left margin to set a breakpoint (red dot appears)
3. Right-click the procedure > Run (or click the debug icon)
4. In the Run PL/SQL dialog, set input parameter values
5. Execution pauses at the breakpoint
6. Use the debugger toolbar:
   - Step Into (F7): enter called procedures
   - Step Over (F8): execute current line, stay in current procedure
   - Step Out (Shift+F7): run to end of current procedure, return to caller
   - Resume (F9): continue until next breakpoint
7. Inspect variables in the Data panel
8. Modify variable values during execution to test branches
9. Use Watches to monitor specific expressions continuously

Common Debugger Techniques

-- Conditional breakpoint: pause only when condition is true
-- (Set in SQL Developer breakpoint properties)
-- Condition example: employee_id = 12345

-- Smart watch: evaluate expression at each pause
-- Example watch expression: l_salary * 1.1

-- Compile all dependent objects with debug info
BEGIN
  FOR obj IN (
    SELECT object_name, object_type
    FROM   user_objects
    WHERE  object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE BODY')
  ) LOOP
    EXECUTE IMMEDIATE
      'ALTER ' || obj.object_type || ' ' || obj.object_name || ' COMPILE DEBUG';
  END LOOP;
END;
/

---

Enabling and Reading Compile Warnings

Oracle can emit compile-time warnings about potential issues in PL/SQL code.

PLSQL_WARNINGS Parameter

-- Enable all warnings for current session
ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL';

-- Enable specific categories
ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:PERFORMANCE, ENABLE:INFORMATIONAL';

-- Enable all, but treat specific warning as error
ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL, ERROR:06002';

-- Disable specific warning (suppress)
ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL, DISABLE:07204';

-- Disable all warnings
ALTER SESSION SET PLSQL_WARNINGS = 'DISABLE:ALL';

Warning Categories

Category	Code Range	Description
SEVERE	PLW-05xxx	Likely errors (e.g., unreachable code)
PERFORMANCE	PLW-07xxx	Code likely to cause performance problems
INFORMATIONAL	PLW-06xxx	Style or design improvement suggestions

-- After enabling, recompile to see warnings
ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL';

CREATE OR REPLACE FUNCTION risky_function(p_val IN NUMBER) RETURN NUMBER IS
  l_result NUMBER;
BEGIN
  IF p_val > 0 THEN
    RETURN p_val * 2;
  ELSE
    RETURN 0;
  END IF;
  l_result := 99;  -- PLW-06002: Unreachable code
  RETURN l_result;
END risky_function;
/
-- Warning: PLW-06002: Unreachable code

-- Read warnings after compilation
SELECT line, position, text, attribute
FROM   user_errors
WHERE  name = 'RISKY_FUNCTION'
  AND  type = 'FUNCTION'
ORDER BY line;

DBMS_WARNING Package (Programmatic Control)

-- Save and restore warning settings
DECLARE
  l_saved_setting VARCHAR2(100);
BEGIN
  l_saved_setting := DBMS_WARNING.GET_WARNING_SETTING_STRING;
  DBMS_WARNING.SET_WARNING_SETTING_STRING('ENABLE:ALL', 'SESSION');

  -- compile some code...

  DBMS_WARNING.SET_WARNING_SETTING_STRING(l_saved_setting, 'SESSION');
END;
/

---

UTL_FILE for Log Files

UTL_FILE writes to server-side OS files — useful for logging in batch jobs where DBMS_OUTPUT is impractical.

-- First: DBA must create a DIRECTORY object pointing to an OS path
-- CREATE OR REPLACE DIRECTORY app_log_dir AS '/opt/oracle/logs';
-- GRANT READ, WRITE ON DIRECTORY app_log_dir TO my_schema;

CREATE OR REPLACE PROCEDURE write_log(
  p_message IN VARCHAR2
) IS
  l_file    UTL_FILE.FILE_TYPE;
  l_logfile VARCHAR2(50) := 'process_' || TO_CHAR(SYSDATE, 'YYYYMMDD') || '.log';
BEGIN
  l_file := UTL_FILE.FOPEN(
    location     => 'APP_LOG_DIR',  -- DIRECTORY object name (uppercase)
    filename     => l_logfile,
    open_mode    => 'a',             -- 'a' = append, 'w' = write, 'r' = read
    max_linesize => 32767
  );

  UTL_FILE.PUT_LINE(
    l_file,
    TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF3') ||
    ' | ' || SYS_CONTEXT('USERENV','SESSION_USER') ||
    ' | ' || p_message
  );

  UTL_FILE.FCLOSE(l_file);

EXCEPTION
  WHEN UTL_FILE.INVALID_DIRECTORY THEN
    RAISE_APPLICATION_ERROR(-20100, 'Invalid log directory: APP_LOG_DIR');
  WHEN UTL_FILE.WRITE_ERROR THEN
    RAISE_APPLICATION_ERROR(-20101, 'Write error to log file');
  WHEN OTHERS THEN
    IF UTL_FILE.IS_OPEN(l_file) THEN
      UTL_FILE.FCLOSE(l_file);
    END IF;
    RAISE;
END write_log;
/

---

Tracing with DBMS_TRACE

DBMS_TRACE enables server-side PL/SQL execution tracing. Trace data is written to the PLSQL_TRACE_EVENTS and PLSQL_TRACE_RUNS tables.

-- Setup (DBA task, one-time)
-- @?/rdbms/admin/tracetab.sql  -- creates trace tables

-- Grant execute (DBA)
-- GRANT EXECUTE ON DBMS_TRACE TO my_user;

-- Start tracing in your session
DBMS_TRACE.SET_PLSQL_TRACE(DBMS_TRACE.TRACE_ALL_CALLS);
-- Options:
--   TRACE_ALL_CALLS    -- trace every call
--   TRACE_ENABLED_CALLS -- trace only procedures compiled with debug
--   TRACE_ALL_EXCEPTIONS -- trace all exceptions
--   TRACE_ENABLED_EXCEPTIONS -- trace exceptions in debug-compiled units
--   TRACE_ALL_SQL      -- trace SQL statements

-- Run the code you want to trace
BEGIN
  process_orders;
END;
/

-- Stop tracing
DBMS_TRACE.CLEAR_PLSQL_TRACE;

-- Read trace data
SELECT r.runid, r.run_date, r.run_comment,
       e.event_seq, e.event_kind, e.proc_name, e.line#
FROM   plsql_trace_runs  r
JOIN   plsql_trace_events e ON e.runid = r.runid
WHERE  r.run_date > SYSDATE - 1/24  -- last hour
ORDER BY r.runid, e.event_seq;

---

Reading SQL Trace Files

Enable SQL trace to capture all SQL and PL/SQL execution details including waits and bind values.

-- Enable trace for current session
ALTER SESSION SET SQL_TRACE = TRUE;
-- or for more detail with waits and binds:
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
-- Level 1: basic, 4: bind variables, 8: wait events, 12: binds + waits

-- Run the problematic code
BEGIN
  process_orders;
END;
/

-- Disable trace
ALTER SESSION SET SQL_TRACE = FALSE;
-- or: ALTER SESSION SET EVENTS '10046 trace name context off';

-- Find the trace file location
SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

-- Or find by session info
SELECT s.sid, s.serial#, s.username,
       p.tracefile
FROM   v$session s
JOIN   v$process p ON p.addr = s.paddr
WHERE  s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

Processing Trace Files

# On the database server OS:
# tkprof formats the raw trace file into readable output
tkprof /path/to/trace.trc output.txt explain=myuser/mypass sys=no sort=prsela,exeela,fchela

# Key sections in tkprof output:
# - call count, cpu, elapsed, disk, query, current, rows
# - Rows: actual rows processed
# - Elapsed: wall clock time
# - CPU: CPU time
# - Disk: physical reads

---

Debugging Best Practices

• Use DBMS_APPLICATION_INFO in all long-running procedures — it costs almost nothing and makes monitoring easy.
• Structure debug output with timestamps: TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF3').
• Use a package-level debug flag to enable/disable output without changing business logic.
• Always compile with DEBUG during development; compile with normal optimization for production.
• Remove or disable DBMS_OUTPUT calls in production code paths — replace with proper logging.
• Use SQL trace (level 12) to diagnose unexpected SQL execution behavior including bind values.
• UTL_FILE log files should be in a dedicated directory with controlled permissions.
• Test with PLSQL_WARNINGS = 'ENABLE:ALL' regularly to catch unreachable code and performance issues.

---

Common Mistakes

Mistake	Problem	Fix
Relying on DBMS_OUTPUT in production	Not visible to applications	Use proper logging tables with autonomous transactions
Not checking IS_OPEN before FCLOSE	Raises exception if file was never opened	Always IF UTL_FILE.IS_OPEN(l_file) THEN CLOSE; END IF
Compiling DEBUG for production	Performance overhead, exposes source	Compile normally for production (ALTER PROCEDURE ... COMPILE)
Not clearing DBMS_APPLICATION_INFO on exit	Module/Action remain set for next user of pooled connection	Always clear in exception handler and normal exit
Setting PLSQL_WARNINGS system-wide without testing	Floods compile errors on existing code	Set at session level first, fix warnings, then promote
Large debug strings in tight loops	Memory and CPU overhead	Conditionally log only every N iterations

---

Oracle Version Notes (19c vs 26ai)

• Baseline guidance in this file is valid for Oracle Database 19c unless a newer minimum version is explicitly called out.

• Features marked as 21c, 23c, or 23ai should be treated as Oracle Database 26ai-capable features; keep 19c-compatible alternatives for mixed-version estates.

• For dual-support environments, test syntax and package behavior in both 19c and 26ai because defaults and deprecations can differ by release update.

• Oracle 11g+: DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS with better granularity.

• Oracle 12c+: Improved SQL Developer debugger with better handling of 12c features.

• Oracle 18c+: Cloud-compatible tracing with Automatic Diagnostic Repository (ADR) improvements.

• All versions: DBMS_OUTPUT.ENABLE(NULL) for unlimited buffer available since 10g.

---

See Also

• PL/SQL Error Handling — Structured exception handling design and logging patterns

Sources

• DBMS_OUTPUT (19c) — PUT_LINE, PUT, NEW_LINE, ENABLE
• DBMS_APPLICATION_INFO (19c) — SET_MODULE, SET_ACTION, SET_SESSION_LONGOPS
• UTL_FILE (19c) — FOPEN, PUT_LINE, FCLOSE, IS_OPEN, exception types
• DBMS_TRACE (19c) — SET_PLSQL_TRACE, CLEAR_PLSQL_TRACE, trace constants
• Oracle Database SQL Trace Guide — event 10046, tkprof