How to Debug a Stored Procedure in Oracle Database
Last modified: March 28, 2025
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:
- In Database Explorer, right-click the Procedures folder and click New Procedure on the shortcut menu.
- 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;
/
- 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:
- In Database Explorer, choose your test database and double-click the Procedures folder to open it.
- Right-click the FillSchedule procedure and click Execute on the shortcut menu.
-
In the Edit Parameters:<procedure_name> dialog box, enter input parameters.
The pop-up window informing about the successful completion and the execution time appears.
- 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:
-
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.
-
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:
-
On the Debug toolbar, click
Start Debugging or press Ctrl + F5.
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.
-
To set a breakpoint, use one of the following options:
- On the Debug toolbar, click
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
- On the Debug toolbar, click
- 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.
Tips & Tricks
In the Debug layout mode, you can use the following options:
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 (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 (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 (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:
- In Database Explorer, choose the database from which you want to retrieve the data.
- Double-click the Tables folder, right-click the table you need and click Retrieve Data on the shortcut menu.
-
In the document that opens, you can view the retrieved data on the results grid.
Want to find out more?
Overview
Take a quick tour to learn all about the key benefits delivered by dbForge Studio for Oracle.
All features
Get acquainted with the rich features and capabilities of the tool in less than 5 minutes.
Request a demo
If you consider employing this tool for your business, request a demo to see it in action.