Debugging Functions, Procedures, and Triggers in Oracle

The Oracle PL/SQL Debugger helps developers to write better code and automate debugging of Oracle packages, stored procedures, functions, triggers, and scripts at runtime.

Oracle PL/SQL Debugger is a reliable tool that allows you to efficiently debug PL/SQL code step by step by using various Oracle PL/SQL debugging techniques such as setting breakpoints, viewing watches, examining the call stack, and evaluating the variables. The tool helps identify and eliminate runtime code bugs, and prevents developers from complicated and error-prone application development. With Oracle PL/SQL Debugger, you can easily control the flow of the debugging process.

Step in or out of called PL/SQL routines
Step through the code a line at a time
Start or stop execution of procedures, functions, triggers, and SQL scripts
Evaluate and change the values of local variables
Detect and recompile invalid objects
Process the code faster

Debug PL/SQL code in Oracle

dbForge Studio for Oracle can debug PL/SQL statements, packages, stored procedures, functions, and triggers. With Oracle debugger, you can quickly set visual breakpoints in your code, step through your code line by line, view variables while they change their values, and so on.

Oracle PL/SQL debugger is easy to use. The tool consists of two windows: Debug layout and Default layout. The Debug layout serves for launching the debugging session, and the Default layout - for performing debugging operations.

Keep the original logic of procedure execution

Manual debugging or a tool that is not reliable enough mostly affects an original execution process and even the procedures logic. Instead of fixing the errors, you may double them and encounter broken procedures.

PL/SQL debugging tool, available in dbForge Studio for Oracle, helps you effectively avoid unexpected behavior and produce error-free code.

Keep the original logic of procedure execution

Take full control over code execution

Oracle PL/SQL Debugger offers full code execution control that includes:

Full support of step-by-step code execution (Step Into, Step Out, and Step Over)
Inserting breakpoints to pause the code execution at a certain point of the source code
Investigating and finding the issues by examining the call stack allowing you to switch between frames
Integrated variables evaluation mechanism

Taking full control over code execution with Oracle PL/SQL Debugger

Examine Call Stack during the debugging session

To track the execution flow of the code and to detect the logic errors, it is better to use the Call Stack window available in the Debug layout mode. The Call Stack is a stack of stored procedures, functions or routines used by the running program. In general, it explains how the program has reached the current state. In the Call Stack window, you can:

View a stacked list of stored procedures or functions being called each other
Switch to the stack frames
Navigate between calls
View the order in which stored procedures, functions, routines, etc. are called

Analysing the Call Stack information during a debugging session in Oracle PL/SQL Debugger

Analyze the values of variables on the fly

Oracle PL/SQL debugger makes it easier for you to identify the issues that cause unexpected behavior of the application and analyze the changes of variable values in the Watches window on the fly while debugging the code.

The Watches window allows you to view server and session variables in your script, edit, monitor, and evaluate the variables while stepping through the code line by line. The Watches window is available in the Debug Layout mode.

Evaluate variables in the Watches window

Step into, Step out, and Step over debugging commands

During the debugging of stored database objects, Oracle PL/SQL Debugger can track the variables and control the execution of the code by performing the stepping debugging commands:

Step into: Executes the current script statement one after the other and allows checking what happens inside the current statement.
Step out: Returns to the line where the current script statement was called.
Step over: Allows moving to the next line of the code.

Using the Step into, Step out, and Step over debugging commands

Set breakpoints in the code

To evaluate the variables and to get a deep analysis of the changes in the values, you can place breakpoints into the lines. They suspend the execution of the code at a certain point and move the program into the break mode. To continue debugging, you need to use the stepping debugging commands such as Step into, Step over, Step out, or Run to Continue.

The Debug > Breakpoints window displays a list of all breakpoints along with their properties placed in the script. It also allows you to delete, enable, or disable breakpoints.

How to set breakpoints in an Oracle stored procedure

Debug a package in Oracle

For better performance and faster code processing, Oracle PL/SQL debugger allows debugging a package with ease and with no efforts.

To effectively debug a specific procedure or function into a package, make sure that your package is compiled and up-to-date. In Database Explorer, you need to select the package you want to debug and then compile it for debugging by selecting one of the options, for example:

Compile for debugging
Compile references for debugging
Compile dependants for debugging

How to debug an Oracle package using the PL/SQL debugging tool

Debug a stored procedure in Oracle

Oracle PL/SQL Debugger helps you easily debug a stored procedure and quickly deal with identifying the reasons for errors taking place in the stored procedure. The tool allows you to debug an Oracle stored procedure by applying the following PL/SQL debugging techniques:

Setting breakpoints in any place of the code
Viewing data items
Evaluating and modifying the values of variables

Prior to start debugging, you need to compile the stored procedure. Otherwise, you'll be asked to execute the compilation when running the debugger.

How to debug a stored procedure in Oracle databases using the PL/SQL debugging tool

Debug a trigger in Oracle

To locate and fix errors in the trigger code or to tune the execution flow of the trigger, you can debug a trigger using the Oracle PL/SQL Debugger. Before you start debugging the trigger, make sure that you have compiled it for debugging. For more information about how to debug a trigger in Oracle, see the Debugging a Trigger documentation.

In Oracle PL/SQL Debugger, you can use the following PL/SQL debugging techniques:

Set a breakpoint to stop the code execution at a specific point
Analyze the call stack available in the Debug layout mode

How to debug an Oacle trigger with the help of the PL/SQL debugging tool

Conclusion

Oracle PL/SQL debugger, built-into dbForge Studio for Oracle, has a convenient debugger interface that simplifies the debugging tasks such as setting breakpoints and watches, examining call stack, viewing, and modifying the values of variables. The tool helps you effectively control the code execution, analyze and monitor the changes to the database objects, as well as process the code easier and faster. Along with the Oracle PL/SQL Debugger and PL/SQL Formatter tools, dbForge Studio for Oracle provides other useful and powerful development and management features.

Availability in the editions of dbForge Studio for Oracle

Feature

Enterprise

Professional

Standard

Express

PL/SQL debugger