Using DBMS_OUTPUT to Debug PL/SQL Code

Debugging is one of the most important tasks for any developer working with PL/SQL code. Oracle provides a very simple yet powerful package called DBMS_OUTPUT that allows developers to print messages, variable values, and process flow during program execution. This makes it much easier to understand how the code is running and where issues may arise. In this article, you will learn how to use DBMS_OUTPUT effectively to debug PL/SQL code, its features, limitations, and best practices.

What is DBMS_OUTPUT?

DBMS_OUTPUT is a built-in Oracle package that enables you to send messages from PL/SQL code to the output buffer. These messages can then be displayed in your development environment (such as SQL*Plus, SQL Developer, or TOAD) when output is enabled.

It is often used to:

• Display values of variables at runtime.
• Trace the flow of program execution.
• Print debugging information during testing.
• Monitor loops, conditions, and procedures.

Enabling DBMS_OUTPUT

Before you can see the output of DBMS_OUTPUT in your environment, you must enable it.

1. In SQL*Plus: SET SERVEROUTPUT ON This command enables the display of output on the console.
2. In Oracle SQL Developer:
   • Go to View → DBMS Output.
   • Click the green “+” icon and select your connection.
   • Now, any output generated by DBMS_OUTPUT.PUT_LINE will appear in the DBMS Output window.
3. In TOAD or Other Tools:
   • Look for DBMS Output panel or options to enable server output.

Without enabling server output, your debugging messages will not appear.

Basic Usage of DBMS_OUTPUT

The most commonly used procedure is PUT_LINE, which prints a line of text.

Example:

BEGIN
   DBMS_OUTPUT.PUT_LINE('Hello, this is a debug message.');
END;

When executed, this will print:

Hello, this is a debug message.

You can also concatenate variable values to track the program state:

DECLARE
   v_emp_name VARCHAR2(50) := 'John';
   v_salary   NUMBER := 5000;
BEGIN
   DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emp_name || ', Salary: ' || v_salary);
END;

Debugging PL/SQL Programs with DBMS_OUTPUT

Let us look at how DBMS_OUTPUT can help in debugging different parts of a program.

1. Debugging Loops

DECLARE
   v_counter NUMBER := 0;
BEGIN
   FOR i IN 1..5 LOOP
      v_counter := v_counter + 1;
      DBMS_OUTPUT.PUT_LINE('Loop iteration: ' || i || ', Counter: ' || v_counter);
   END LOOP;
END;

This allows you to see how variables are changing inside a loop.

2. Debugging Conditional Logic

DECLARE
   v_marks NUMBER := 75;
BEGIN
   IF v_marks >= 90 THEN
      DBMS_OUTPUT.PUT_LINE('Grade: A');
   ELSIF v_marks >= 75 THEN
      DBMS_OUTPUT.PUT_LINE('Grade: B');
   ELSE
      DBMS_OUTPUT.PUT_LINE('Grade: C');
   END IF;
END;

This helps verify whether conditions are being evaluated correctly.

3. Debugging Procedures and Functions

CREATE OR REPLACE PROCEDURE calculate_bonus(p_salary NUMBER) IS
   v_bonus NUMBER;
BEGIN
   v_bonus := p_salary * 0.10;
   DBMS_OUTPUT.PUT_LINE('Salary: ' || p_salary || ', Bonus: ' || v_bonus);
END;
/
BEGIN
   calculate_bonus(6000);
END;

The output will confirm the calculation logic inside the procedure.

Useful DBMS_OUTPUT Procedures

Besides PUT_LINE, the package provides other procedures and functions:

• PUT: Similar to PUT_LINE but does not add a line break.
• NEW_LINE: Manually adds a line break.
• DISABLE/ENABLE: Controls whether output is buffered.
• GET_LINE and GET_LINES: Used to retrieve buffered output programmatically.

Example:

BEGIN
   DBMS_OUTPUT.PUT('This is ');
   DBMS_OUTPUT.PUT('on the same line');
   DBMS_OUTPUT.NEW_LINE;
END;

Output:

This is on the same line

Limitations of DBMS_OUTPUT

While DBMS_OUTPUT is useful, it has certain limitations:

• Performance Overhead: Printing too many messages can slow down execution.
• Buffer Size: The default buffer size may be limited (though it can be increased).
• Not Suitable for Production: It is mainly for development and debugging, not for production logging.
• Requires Enabling Output: If you forget to enable server output, messages will not appear.

Best Practices for Using DBMS_OUTPUT

To use DBMS_OUTPUT effectively, follow these best practices:

1. Use selectively – Print only key values and checkpoints, not every small detail.
2. Format messages clearly – Add meaningful labels to outputs for better readability.
3. Remove or comment out debugging code – Before moving to production, ensure debugging statements are removed.
4. Use conditional debugging – Wrap DBMS_OUTPUT calls inside flags so they can be easily turned on/off.
5. Combine with exception handling – Print error messages for better troubleshooting.

Example with exception handling:

BEGIN
   DBMS_OUTPUT.PUT_LINE('Starting process...');
   -- Some code here
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;

Alternatives to DBMS_OUTPUT

While DBMS_OUTPUT is simple, sometimes you may need more advanced debugging or logging. Alternatives include:

• Using Oracle built-in debugger in SQL Developer.
• Writing logs to database tables for permanent storage.
• Using UTL_FILE to write logs to external files.
• Application-level logging frameworks for large systems.

Conclusion

DBMS_OUTPUT is a fundamental tool for PL/SQL developers who need to debug and understand their code. By printing messages, variable values, and execution checkpoints, it provides clear visibility into how a program is running. However, it should be used wisely, as excessive output can affect performance. For more complex applications, combining DBMS_OUTPUT with structured logging and exception handling leads to more reliable debugging.