Using Pass Counts to Turbo Charge Your PL/SQL Breakpoints

Debugging PLSQL is one of the primary features of Oracle SQL Developer. Yes, its the IDE for the database, but PLSQL is the database’s programming language. It really, really needs to support all things PLSQL. Programming languages need modern day amenities if they are to be adopted by mainstream developers. One of those amenities is a debugger.

A primary challenge I face as an advocate for Oracle, PLSQL, and our developer tools is that database developers aren’t necessarily the same breed as application developers. Chris Travers discussed this phenomena a bit in his blog yesterday. Thanks to Eddie for sharing that and making my job a bit easier!

PLSQL developers aren’t always familiar with debuggers. They don’t necessarily come from the world of Visual Studio, Eclipse, and other rich IDEs. They are given SQL*Plus and a text editor. Then they discover the many Oracle GUI IDEs, including Oracle SQL Developer. Once they get used to building their procedures and packages, then they might stumble upon the debuggers. If you haven’t had a chance to discover why debuggers are cool or how to use the debugger in SQL Developer, time’s a-wasting (your time!)

One of the neat debugger features that Oracle provides for PLSQL is the ability to set pass counts for your breakpoints.

This means instead of telling SQL Developer to break on line# 42, I can tell it to only break on line#42 after it has been executed 397 times. This is helpful for a number of reasons. The most obvious scenario plays out like this:

• LOOP this code
• iteration X, something screwy happens
• stepping through loop iterations is hard on my mouse and my patience
• let me JUMP to iteration X

Here’s how to make that happen with SQL Developer

Adding a breakpoint is easy. Just find the line you want to stop on, and mouse-click in the gutter. Making sure you are in the Procedure Editor and not the worksheet, of course!

After your break point is defined, execution will ‘break’ on that line every time. Unless we make an exception of course. If we alter the break point we can define several different conditions. One of those is ‘pass count.’ If I set it to 12, SQL Developer (actually the database, but it looks like the IDE is doing it) will wait for the line to execute 12 times before breaking execution.

One last tip

The default panel view for Breakpoints isn’t super-friendly. Go into the preferences and toggle on:

• Pass Count
• Condition

You can also set conditional breakpoints, say ‘X should be greater than 50.’ There’s no reason why you can’t combine pass count and conditional breakpoint properties!