Your message has been sent.
This article has been saved to your account.
Go to my account
This article has been emailed to your Kindle.
Send this article
Once your PL/SQL code has successfully compiled, it is important to review it to make sure it does what is required and that it performs well. You can consider a number of approaches when tuning and testing code. These approaches include:
- Debugging—run the code and add break points to stop and inspect areas of concern.
- SQL performance—use Explain Plan results to review the performance.
- PL/SQL performance—use the PL/SQL Hierarchical Profiler to identify bottlenecks.
- Unit testing—review edge cases and general function testing. Does the code do what you intended it to do?
In this article by Sue Harper, author of Oracle SQL Developer 2.1, we'll review the debugger. We will see how to debug PL/SQL packages, procedures, and functions.
Debugging PL/SQL code
SQL and PL/SQL code may execute cleanly, and even produce an output. PL/SQL code may compile and produce results, but this is part of the task. Does it do what you are expecting it to do? Are the results accurate? Does it behave as expected for high and low values, odd dates or names? Does it behave the same way when it's called from within a program as it does when tested in isolation? Does it perform as well for massive sets of data as it does for a small test case? All of these are aspects to consider when testing code, and many can been tracked by debugging the code.
Using the debugging mechanism in SQL Developer
You will need a piece of compiled, working code. For this exercise, we will use the following piece of code:
CURSOR EMPDEPT_CURSOR IS
SELECT D.DEPARTMENT_NAME, E.LAST_NAME, J.JOB_TITLE
FROM DEPARTMENTS D, EMPLOYEES E, JOBS J
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
AND E.JOB_ID = J.JOB_ID;
EMP_RECORD EMPDEPT_CURSOR % ROWTYPE;
TYPE EMP_TAB_TYPE IS TABLE OF EMPDEPT_CURSOR % ROWTYPE INDEX BY
I NUMBER := 1;
EMP_TAB(I) := EMP_RECORD;
WHILE((EMPDEPT_CURSOR % FOUND) AND(I <= P_MAXROWS))
LOOP I := I + 1;
EMP_TAB(I) := EMP_RECORD;
CLOSE EMPDEPT_CURSOR; FOR J IN REVERSE 1 .. I
LOOP DBMS_OUTPUT.PUT_LINE('THE EMPLOYEE '|| EMP_TAB(J).LAST_NAME ||
' WORKS IN DEPARTMENT '|| EMP_TAB(J).DEPARTMENT_NAME);
Before you can debug code, you need to have the following privileges:
- EXECUTE and DEBUG—you need to be able to execute the required procedure
- DEBUG CONNECT SESSION—to be able to debug procedures you execute in the session
Note, when granting the system privilege DEBUG ANY PROCEDURE, you are granting access to debug any procedure that you have execute privilege for and has been compiled for debug.
Using the Oracle debugging packages
Oracle provides two packages for debugging PL/SQL code. The first, DBMS_DEBUG, was introduced in Oracle 8i and is not used by newer IDEs. The second, DBMS_DEBUG_JWP, was introduced in Oracle 9i Release 2, and is used in SQL Developer when debugging sub-programs.
When preparing to debug any code, you need to set at least one break point, and then you should select Compile for Debug. In the following screenshot, the breakpoint is set at the opening of the cursor, and the Compile for Debug option is shown in the drop-down list:
Instead of using the drop-down list to select the Compile or Compile for Debug options, just click the Compile button. This compiles the PL/SQL code using the optimization level set in the Preferences. Select Database PL/SQL Compiler|. By setting the Optimization Level preference to 0 or 1 the PL/SQL is compiled with debugging information.
Any PL/SQL code that has been compiled for debugging will show the little green bug overlaying the regular icon in the Connections navigator. The next screenshot shows the EMP_DEPTS procedure and the GET_SALARY function have both been compiled for debug:
Compile for debug
Once you have completed a debugging session, be sure to compile again afterwards to remove any debug compiler directives. While negligible, omitting this step can have a performance impact on the PL/SQL program.
You are now ready to debug. To debug, click the Debug button in the toolbar. SQL Developer then sets the sessions to a debug session and issues the command DBMS_DEBUG_JDWP.CONNECT_TCP (hostname, port); and sets up the debug windows as shown in the following screenshot:
This connects you to a debugger session in the database. In some instances, the port selected is not open, due to firewall or other restrictions. In this case, you can have SQL Developer prompt you for the port. To set this option, open the Preferences dialog, and select the Debugger node. You can also specify the port range available for SQL Developer to use. These options mean that you can have more control over the ports used.
Navigating through the code
The PL/SQL debugger provides a selection of buttons (or menu items) to step through individual lines of code, or to step over blocks of code. You can step through or over procedures, navigating to the point of contention or the area you wish to inspect.
Once you start stepping into the code, you can track the data as it changes. The data is displayed in a second set of tabbed dialogs. In this example, we are looping through a set of records in order for you to see how each of the windows behaves. As you start stepping into the code, the Data tab starts to display the values:
This Data tab continues to collect all of the variables as you continue to step through the code. Even if you step over and skip blocks of code, all of the code is executed and the results are gathered here.
The Smart Data tab keeps track of the same detail, but only the values immediately related to the area you are working in. This is more useful in a large procedure than in a small one like the example shown.
The context menu provides you with a set of options while debugging. These include:
- Run to Cursor—allows you to start debugging and then to quickly move to another part of the code. The code in-between is quickly executed and you can continue debugging.
- Watch—allows you to watch an expression or code while you are debugging.
- Inspect—allows you to watch values as you debug.
In the following screenshot, the current execution point is at the start of the WHILE loop. If the loop is required to loop multiple times, you can skip that and have the code execute to a point further down in the code, in this case after the cursor has been completed and closed:
The Watch and Inspect options remain set up if you stop and restart the debug session. This allows you to stop, change the input values, and start debugging and these will change according to the new parameters. You do not need to set up watch or inspector values each time you debug the procedure. The values appear in dockable windows, so you can dock or float them near the code as required:
You can modify values that you are watching. In the following example, 'i' is the counter that we're using in the loop. You can modify this value to skip over chunks of the loop, and then continue from a particular point.
Modifying values in the middle of the code can be useful, as you might want to test how the program reacts in certain circumstances. For example, before the millennium, testers may have wanted to see how code behaved, or output changed once the date switched over to the year 2000.
eBook Price: $32.99
Book Price: $54.99
You can debug any sub-program that you have EXECUTE and DEBUG access to, as previously mentioned. So, even if a database procedure is being called or accessed from within an application, you can debug it using SQL Developer. However, some of the problems appear when a procedure is being called, so you need to debug it in place. This is called remote debugging.
The easiest way to illustrate remote debugging is to use SQL Developer together with another client application, in this case SQL*Plus. Once we have prepared the scene, we can then expand that to consider other scenarios.
Preparing for remote debugging
Regardless of where the sub-program resides, if you are going to use SQL Developer to debug the code, you need to start by preparing SQL Developer. You do this by selecting the connection that will be used to debug the code and selecting Remote Debug... from the context menu:
You can set the range of ports using in the Tools | Preferences dialog, using the Debugging Port Range under the Debugger preferences.
This prepares SQL Developer for the connection to the debugger and opens the process. Once done, you need to prepare the remote site. In the same way that SQL Developer executes DBMS_DEBUG_JDWP.CONNECT_TCP (the procedure to connect to the debugger) you need to execute this in the remote site. In this example, we need to use the procedure call:
EXECUTE DBMS_DEBUG_JDWP.CONNECT_TCP ('127.0.0.1', 4000);
Now, execute the procedure you are going to debug. In the following screenshot, the debug package has been executed and the procedure to be debugged has been called:
Instead of executing the DBMS_DEBUG_JDWP command, you can set an environment variable before running your application. For example, using the same parameters as shown above, use:
At this point, control is passed back to SQL Developer and the rest of the execution is exactly as is described for local debugging. Once complete, the control is passed back to the calling program, in this case SQL*Plus.
If you had issued a SET SERVEROUTPUT ON command before executing the procedure, the output appears on screen as shown:
Remote debugging with Application Express
You can use SQL Developer to browse to and compile the PL/SQL code being called in Oracle APEX. If you are debugging, you need to select Compile for Debug and include a breakpoint.
In this case, you need to include the DBMS_DEBUG_JDWP.CONNECT_TCP ('127.0.0.1', 4000); call in Oracle APEX. For releases prior to Oracle APEX 3.1.1, you need to edit the procedure in Oracle APEX to include this call, but from Oracle APEX 3.1.1, if you change the YES/NO debugging parameter to REMOTE, this will execute the CONNECT_TCP call. For example, in the following Oracle APEX URL, change the NO to REMOTE:
As soon as the CONNECT_TCP has been set up and you invoke the desired procedure, the control passes back to SQL Developer and you can debug the code as before.
SQL Developer supports creating, editing, and compiling PL/SQL code. The SQL and PL/SQL parsing capabilities provided by the tool mean that additional utilities, such as code insight and completion, can be an invaluable resource to developers working with many objects to hand. Utilities as simple as a small dialog, save time by providing frameworks of code, while code templates and code snippets mean that frequently used code is not far out of reach. Using the utilities provided, and adding your own code to them, can be an invaluable coding resource.
eBook Price: $32.99
Book Price: $54.99
About the Author :
Sue Harper was a senior school mathematics and science teacher by profession. It was this teaching qualification that opened the door to Oracle in South Africa, where Sue started as a SQL, PL/SQL, SQL Forms, and Reports instructor in 1992. Before long she'd added Oracle Designer to her repertoire and soon left for the UK, to join the Designer curriculum development team. Sue was a course writer and then product manager for Oracle Designer for many years, traveling extensively meeting customers and training Oracle staff and instructors. Sue was later product manager for Oracle JDeveloper working with database and modeling tools in that product. Sue is currently product manager for Oracle SQL Developer, SQL Developer Data Modeler, and SQL Developer Migrations. Based at home, Sue works with her team, scattered around the world and customers, running web-based training sessions or helping with individual queries. She frequently attends conferences where she presents SQL Developer material and works with customers on their queries.
Sue lives in West London, where she enjoys walking with her camera and her dog in the extensive local parks and further afield throughout the UK.