Debugging a Stored Procedure

This example demonstrates how to debug Oracle stored procedure by stepping into the stored procedure. It also illustrates different debugging techniques such as setting breakpoints, viewing data items, and so on.

Creating a Sample Procedure

In this topic, some sample database objects are used for demonstrating how to work with the dbForge Studio Debugger. Use scripts specified in this topic to create necessary objects in your test database.

Database Procedure

Use the script below to create the procedure that takes two input parameters: DateFrom and DateTo. Based on these input parameters the table fills up with data.

Debugging the Stored Procedure

Assume that you need to get Time Sheet for a specific time period. To do this, execute the FillSchedule procedure:

  1. In Database Explorer, choose your test database.
  2. Click Procedures and then right-click the FillSchedule procedure, and click Execute on the shortcut menu.

  3. Enter input parameters.

    Note

    Actual document look may slightly vary from the above image, depending on the product you use.

A quick look through the code gives you nothing and you have no other choice but to start the debugger.

Debugging Procedure

To start debugging the stored procedure:

  1. To compile the stored procedure for debugging, navigate to the required procedure in Database Explorer, select Compile, and then click Compile Dependants for Debugging on the shortcut menu.
  2. Click the Start Debugging button on the Debug toolbar.
  3. If you have not compiled the procedure, the dbForge Studio prompts you to compile it. Click Yes.

  4. Enter the stored procedure input parameters once again and click OK.

    You can see that the yellow arrow identifies the stack frame where the execution pointer is currently located.

  5. To stop the debugging process, click the Stop button.
  6. Click Execute to run the stored procedure.
  7. You can see the message telling us that the stored procedure executed successfully. To verify this, you can retrieve the data from the table.
  8. In Database Explorer, choose the required database.
  9. Click Tables and then right-click the required table.

  10. Click Retrieve Data on the shortcut menu.

  11. dbForge Studio displays the result table.

Note

Place the mouse pointer over a variable to see its value.

As you see, the Debugger for Oracle is a very simple and powerful tool to use. You can use different debugging techniques to achieve successful results.