How to Debug a Stored Procedure in Oracle Database

This example demonstrates how to debug an Oracle stored procedure using PL/SQL Debugger for Oracle built into dbForge Studio for Oracle. It also illustrates different debugging techniques such as setting breakpoints, viewing data items, and so on. In this topic, sample database objects are created with the help of dbForge Studio for Oracle. In our example, we use a SCHEDULE_DETAIL test table and a SCHEDULE test database.

For the demo, we will do the following:

  • create a stored procedure
  • execute a stored procedure
  • compile the procedure
  • debug a procedure

Creating a stored procedure

To create the FillSchedule procedure, do the following:

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

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

     CREATE OR REPLACE PROCEDURE SCHEDULE.FILLSCHEDULE(DATE_FROM   IN DATE,
                                      DATE_TO     IN DATE,
                                      SCHEDULE_ID IN INT)
   AS
     TEMPLATE_COUNT SMALLINT;
     TEMPLATE_ID    INT;
     DAY_COUNT      INT;


     TYPE REC_TYPE IS RECORD (
         DATE_OUT        DATE,
         DAY_ORDER       SMALLINT,
         TIME_SHEET_DATE DATE

       );
     TYPE ENUM_TYPE IS TABLE OF REC_TYPE;
     DAY_ENUM       ENUM_TYPE;
     DAY_ENUM2      REC_TYPE1;

   BEGIN
     DAY_ENUM2 := REC_TYPE1(DATE_FROM, NULL, SYSDATE);
     DAY_ENUM2.ADMIT(1);
     INSERT INTO SCHEDULE.WORK (
       DATE_OUT, DAY_ORDER, TIME_SHEET_DATE
     )
     VALUES (DAY_ENUM2.DATE_OUT -- DATE_OUT - DATE
       , DAY_ENUM2.DAY_ORDER -- DAY_ORDER - NUMBER(*, 0)
       , DAY_ENUM2.TIME_SHEET_DATE -- TIME_SHEET_DATE - DATE
     );
     SELECT COUNT(*),
            MAX(S.TEMPLATEID)
       INTO TEMPLATE_COUNT,
            TEMPLATE_ID
       FROM SCHEDULE.SCHEDULE S
         JOIN SCHEDULE.SCHEDULE_TEMPLATE_DETAIL STD
           ON S.TEMPLATEID = STD.TEMPLATEID
       WHERE S.SCHEDULEID = SCHEDULE_ID;

     --	IF (TEMPLATE_ID IS NULL) THEN
     --		RAISE_APPLICATION_ERROR(-20001, 'TEMPLATE_ID IS NULL' );
     --	END IF; 

     SELECT DATE_OUT,
            DAY_ORDER,
            GETFIRSTDAYOFMONTH(DATE_OUT)
       BULK COLLECT INTO DAY_ENUM
       FROM (SELECT DATE_FROM - 1 + ROWNUM AS DATE_OUT,
                    NVL(NULLIF(MOD(ROWNUM, TEMPLATE_COUNT), 0), TEMPLATE_COUNT) AS DAY_ORDER
           FROM ALL_OBJECTS
           WHERE ROWNUM <= DATE_TO - DATE_FROM + 1) T;

     DAY_COUNT := DAY_ENUM.COUNT;

     --	IF (DAY_COUNT = 0) THEN
     --		RAISE_APPLICATION_ERROR(-20001, 'ENUM IS EMPTY' );
     --	END IF;

     DELETE FROM SCHEDULE.SCHEDULE_DETAIL
       WHERE SCHEDULEID = SCHEDULE_ID
         AND DATEOUT BETWEEN DATE_FROM AND DATE_TO;

     FOR I IN DAY_ENUM.FIRST .. DAY_ENUM.LAST
     LOOP

       INSERT INTO SCHEDULE.SCHEDULE_DETAIL (
         TIMESHEETDATE, DATEOUT, SCHEDULEID, ABSENCECODE, WORKSHIFTCD
       )
       SELECT DAY_ENUM(I).TIME_SHEET_DATE,
       DAY_ENUM(I).DATE_OUT,
       SCHEDULE_ID,
       STD.ABSENCECODE,
       STD.WORKSHIFTCD
       FROM SCHEDULE.SCHEDULE_TEMPLATE_DETAIL STD
       WHERE STD.TEMPLATEID = TEMPLATE_ID
       AND STD.DAYORDER = DAY_ENUM(I).DAY_ORDER;

       DBMS_OUTPUT.PUT_LINE(DAY_ENUM(I).TIME_SHEET_DATE || ',  ' || DAY_ENUM(I).DATE_OUT || ',  ' || DAY_ENUM(I).DAY_ORDER);

     END LOOP;

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

The procedure has two input parameters: DateFrom and DateTo. Based on these input parameters the table is populated with test data.

Assume that we need to get Time Sheet for a specific time period. For this, we need to execute the procedure.

Executing a stored procedure

To debug a stored procedure, it should be executable.

To execute the FillSchedule procedure, do the following:

  1. In Database Explorer, choose your test database and double-click the Procedures folder to open it.
  2. Right-click the FillSchedule procedure and click Execute on the shortcut menu.

  3. In the Edit Parameters:<procedure_name> dialog box, enter input parameters.

    Edit Parameters dialog

    The pop-up window informing about the successful completion and the execution time appears.

  4. To complete the process, click Ok.

Compiling a stored procedure

Prior to debugging, you should compile the stored procedure for debugging. Otherwise, dbForge Studio for Oracle prompts you to compile it.

To compile the stored procedure for debugging, do the following:

  1. In Database Explorer, right-click the procedure you want to compile, click Compile, and then select one of the following options on the shortcut menu:

    • Compile for Debugging: Loads debugging information for the selected object.
    • Compile References for Debugging: Loads debugging information for the objects that are linked to the selected object.
    • Compile Dependants for Debugging: Loads 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 Close.

Debugging a stored procedure

To start debugging the stored procedure, do the following:

  1. On the Debug toolbar, click Start debugging icon Start Debugging or press Ctrl + F5.

    Debug toolbar

    Note:

    If you have not compiled the procedure, the dbForge Studio prompts you to compile it. In the pop-up window that opens, click Yes. In the Edit Parameters:<procedure_name> dialog box, specify the 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.

    The Watches pane displays information about variables, their value, and type.

    The Call Stack pane displays the line of the code where Oracle PL/SQL Debugger is currently located.

    Stored procedure debugging

  2. To set a breakpoint, use one of the following options:

    • On the Debug toolbar, click Breakpoints icon Breakpoints
    • Right-click a line of executable code where you want to set a breakpoint and click Insert Breakpoint on the shortcut menu. For more information about how to manage breakpoints, see Working with Breakpoints.
    • Press Ctrl + D, B
  3. To stop the debugging process, click Stop debugging icon Stop Debugging on the Debug toolbar or press Shift + F5.
  4. To proceed with debugging, click Continue icon Continue or press Ctrl + F5.

Tips & Tricks

In the Debug layout mode, you can use the following options:

  • Continue icon Continue (or press Ctrl + F5) to proceed with the debugging process until it reaches the next breakpoint or the end of the debugging process.
  • Step into icon Step Into (or press F11) to instruct the debugger to execute the next line of the code. If the line contains a function call, Step Into executes only the call itself, then halts at the first line of the code inside the function.
  • Step over icon Step Over (or press F10) to execute the entire function, then halts at the first line outside the function. Also, the command can be used to avoid stepping into functions.
  • Step out icon Step Out (or press Shift + F11) to run the current function and it stops the execution until the current function is returned or the breakpoint occurs.

After the debugging of the stored procedure has stopped, you can execute it. For this, on the Standard toolbar, click Execute. In the pop-up window informing that the stored procedure has been executed successfully, click Ok.

To verify this, you can retrieve the data from the table. To do this, perform the following steps:

  1. In Database Explorer, choose the database from which you want to retrieve the data.
  2. Double-click the Tables folder, right-click the table you need and click Retrieve Data on the shortcut menu.

  3. In the document that opens, you can view the retrieved data on the results grid.

    Results grid