Debugging PL/SQL with SQL Developer: Episode IV

Can you tell us more about the debugger? Can you show us the debugger? What about the debugger? Can you spell ‘debugger’? Why can’t the debugger show X? What is COMPILE WITH DEBUG anyway? Debugger?

• Skip to Episode V – Starting the Debugger

In the past 10 years I have probably heard these questions and countless variations more than any other when it comes to the topic of PL/SQL interactive development environments (IDE.) Debugging seems to be the holy grail of software development, i.e. ‘said to possess miraculous powers.’

I understand this. Software vendors have been focused on convincing users that they need to have a debugger, and that they are worth paying for. And it is true they provide a TON of value. I’m convinced that PL/SQL developers view debugging and debuggers unlike more traditional software engineers. When your first exposure to a programming language is Notepad/SQL*Plus versus a more integrated environment like Visual Studio or Eclipse, this shouldn’t be very surprising. Add to this that most folks that work with PL/SQL are first and foremost ‘the database geeks’ versus ‘hackers and coders’ and you get an audience that has a completely different set of expectations from their tools.

For 85% of the people out there using PL/SQL, their primary debug tool is DBMS_OUTPUT. This is NOT a bad thing. Using it is easy, quick, and flexible. As a matter of fact, the Oracle DOCS even list debugging as a valid use case for the package

You can also debug stored subprograms and triggers using the Oracle package DBMS_OUTPUT. Put PUT and PUT_LINE statements in your code to output the value of variables and expressions to your terminal.

So I want to address two camps with blog series:

• Those who have seen the light of using a debugger, but need help getting started with SQL Developer
• Those who have never used a debugger and are wondering what all the fuss is about

Let’s start with the n00b crowd, because many of the debugger pros out there might not be aware of just what the debugger can actually do.

Why the debugger can be more useful than DBMS_OUTPUT

Adding PUT_LINE() calls throughout your program is pretty easy. Remembering to comment them out or removing them completely before checking it in can be a pain. But probably the biggest drawback to using the output package showcases the debugger’s biggest advantage – lack of instant feedback. The DBMS_OUTPUT buffer isn’t available for viewing until AFTER a program is finished running, but the debugger is able to show you what is going on AS the program is running.

The debugger can allow you to remain the casual bystander or can empower you to jump into the action and make changes on-the-fly to see ‘what happens IF.’ Here’s a quick example of making a change to the run time environment via the debugger in SQL Developer. Let’s say you are stepping through a LOOP construct, and you’re curious to see what would happen if the loop iterator suddenly were equal to X+5000.

Of course you could manually hack up your code with a line to artificially change the value, but instead I’m going to ask the debugger to do this for me. No touching the source code PLUS no chance for me to break anything = WIN. This is just an example of the power of a debugger, we’ll get to the actual debugger tutorial in a later (or earlier?) episode.

So if I can’t sell you on the debugger on the variable watch stuff, then I usually give up and help you get the most out of DBMS_OUTPUT. But let me throw just another few things out there just so I can say I did my due diligence:

• you can step into other pl/sql objects as they are called and also debug them
• you can use breakpoints to ‘jump to’ specific lines of code
• you can define breaking expressions to find where your code ‘goes screwy’
• you can start execution of your web app and continue into your PL/SQL code, then let SQL Developer ‘take over’

Still unmoved? OK.

Again, nothing ‘wrong’ with that, and it’s pretty easy to see DBMS_OUTPUT in SQL Developer. Actually a mix of DBMS_OUTPUT and the debugger can make a lot of sense. You might want certain text available AFTER your debug session – so using DBMS_OUTPUT or even UTL_FILE is a really good fit.

When using the debugger, SQL Developer automatically shows you the DBMS_OUTPUT alongside the rest of the execution and debugging messages

I’ll tackle how to get started with the debugger in our next episode. The good news is that you won’t have to wait several years for it, AND there’s zero chance of Ewoks making an appearance. The bad news is that I probably won’t be able to get my boss to approve any AT-ATs purchases for ‘research.’

May the CBO be with you.