Oracle PL/SQL Debugging Techniques

The debugging toolkit

PL/SQL debugging ranges from quick DBMS_OUTPUT calls to full interactive debugging. Knowing when to use each saves significant time.

DBMS_OUTPUT: quick and simple

The fastest way to inspect values during development:

DBMS_OUTPUT.PUT_LINE('Processing order: ' || v_order_id);
DBMS_OUTPUT.PUT_LINE('Amount: ' || TO_CHAR(v_amount, '999,999.99'));

Enable in SQL Developer: View → DBMS Output → green plus icon. Enable in SQLcl: SET SERVEROUTPUT ON SIZE UNLIMITED.

Limitations: output only appears after the block completes (not in real-time); buffer limit (1MB by default); not available in production code.

Logging to a table: production-safe debugging

A persistent logging procedure is essential for production issues:

CREATE TABLE debug_log (
  log_id    NUMBER GENERATED ALWAYS AS IDENTITY,
  log_dt    TIMESTAMP DEFAULT SYSTIMESTAMP,
  proc_name VARCHAR2(100),
  msg       VARCHAR2(4000),
  context   VARCHAR2(500)
);

CREATE OR REPLACE PROCEDURE log_debug (
  p_proc    VARCHAR2,
  p_msg     VARCHAR2,
  p_context VARCHAR2 DEFAULT NULL
) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO debug_log (proc_name, msg, context)
  VALUES (p_proc, p_msg, p_context);
  COMMIT;
END;

Call it throughout your code: log_debug('create_order', 'Validation passed', 'order_id=' || v_id);

SQL Developer interactive debugger

For complex logic that’s hard to trace via logging, use SQL Developer’s built-in debugger:

1. Right-click the procedure → Edit → set breakpoints by clicking the margin
2. Right-click → Debug → step through with F8 (next line), F7 (step into), F9 (run to next breakpoint)
3. Inspect variable values in the Data panel

Requires EXECUTE and DEBUG privilege on the object.

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

In exception handlers, get the full call stack where the error originated:

EXCEPTION
  WHEN OTHERS THEN
    log_debug('handle_order', SQLERRM || ' | ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;

This tells you the exact line number where the exception was raised, not just where it was caught.

Tracing with DBMS_SESSION

For performance debugging — trace a session and analyse with TKPROF:

DBMS_SESSION.SET_SQL_TRACE(TRUE);
-- run your code
DBMS_SESSION.SET_SQL_TRACE(FALSE);

Then find the trace file in USER_DUMP_DEST and run tkprof tracefile.trc output.txt explain=schema/password.