How to Debug a Package in Oracle Database

Last modified: October 30, 2023

This example demonstrates how to debug an Oracle package using Oracle PL/SQL debugging tool built into dbForge Studio for Oracle. In this topic, sample database objects are created with the help of dbForge Studio for Oracle.

The example describes the following steps:

• creating a stored procedure
• creating a sample package
• compiling a package
• debugging the package

Creating a stored procedure

We will use the stored procedure created in Debugging a Stored Procedure.

Creating a package

For the demo, we create the PACKAGE_SCHEDULE package.

To create a package, do the following:

1. In Database Explorer, right-click the Packages folder and click New Package on the shortcut menu.

2. In the document that opens, copy and paste the following script:

    -- PACKAGE_SCHEDULE specification
    CREATE OR REPLACE PACKAGE SCHEDULE.Package_Schedule AS
    PROCEDURE FILLSCHEDULE(DATE_FROM IN DATE, DATE_TO IN DATE,SCHEDULE_ID IN INT);
    FUNCTION GetFirstDayOfMonth(DT DATE) RETURN DATE;
    END Package_Schedule;
    /
   

3. To save the changes, click Apply Changes or press Shift + Alt + A.

Compiling a package

To debug Oracle packages, you should first compile them with debug information.

To compile the package for debugging, do the following:

1. In Database Explorer, right-click the package you want to compile, click Compile, and then select Compile Dependants for Debugging to load debugging information for the objects on which the selected object is dependent.

2. In the compiling window that opens, the object you want to compile is selected by default. Click Compile, and then click Close.

Debugging a package

To debug an Oracle package, do the following:

1. In Database Explorer, expand the Packages node and then double-click the package you have created and compiled for debugging.
2. In the document that opens, highlight the code and execute the statement by pressing F8.

3. To set a breakpoint for the package, right-click the line of the code and click Insert Breakpoint on the shortcut menu.

   

   Note:

   Keep in mind that the breakpoint can be set only on the executable code. To execute the package, select the code, right-click the code, and click Execute Current Statement or press F8.

   If you want to remove the breakpoint you have set, right-click the breakpoint in the document and click Delete Breakpoint on the shortcut menu.

4. Make sure that the procedure which the package is dependent on is opened in the document. In our case, it is the FILLSCHEDULE procedure.
5. In Database Explorer, right-click the procedure name, and click Compile > Compile Dependants For Debugging on the shortcut menu.
6. In the Compile Dependants for Debugging dialog box, select the procedure and click Compile, and then click Close.

7. In Database Explorer, right-click the procedure and click Step Into or press F11 to start debugging.

   

8. Step through the procedure until the end of the debugging process.

To stop the debugging process, click Stop Debugging on the Debug toolbar or press Shift + F5.

To proceed with debugging, click Continue or press Ctrl + F5.

For more information about how to debug a stored procedure, see Debugging a Stored Procedure regarding the process of debugging the procedure.

Breakpoints pane

In the Breakpoints pane, you can do the following:

• Remove the selected breakpoint by clicking Delete
• Delete all breakpoints by clicking Delete all breakpoints or pressing Ctrl + Shift + F9
• Disable all breakpoints by clicking Disable all breakpoints
• Switch to the source code by clicking Go to Source Code

Note

You can also debug a package from other PL/SQL programs, which call package procedures or functions using the same way for schema procedures and functions debugging.