 |
|
EnterpriseDB: PL/Debugger
Oracle Tips by
Burleson
|
The
PL/Debugger allows a developer to debug SPL and PL/pgSQL procedural
code. If you have used any debugger in the past, PL/Debugger
will be very familiar to you.
To debug
procedural code, in the navigator pane of Developer Studio, select the
procedure or function that you wish to debug (Figure 5.51).
Figure
5.51: PL/Debugger Stand-alone Code Units
If you
wish to debug a packaged procedure or function, select the package you
wish to debug and drill down to the Code Units node (Figure 5.52).
Figure
5.52: PL/Debugger Package Code Units
You can
debug both SPL and PL/pgSQL code units and you can debug both
procedures and functions.
Once you
have the appropriate code unit selected, press the debug icon (the
icon that looks like a ladybug with a circle lined through it).
This launches the PL/Debugger arguments window (Figure 5.53).
Figure
5.53: PL/Debugger Arguments Dialog
When
debugging, you would want to enter a value for each parameter.
In the case of this procedure, select_emp (which I created in Chapter
4), there is only a single parameter, p_empno. I am entering
7788.
When
ready, I can press the OK button that launches the debug window
(Figure 5.54).
Figure
5.54: PL/Debugger Main Window
The
debugger, like most other Developer Studio windows, is made up of four
panes.
The
topmost pane is the toolbar pane. From left to right is:
* Step
Into (F2) – Execute the next line by entering a debuggable procedure
if available. For example, if you are debugging an SPL procedure
that calls an SPL procedure, you can step into the second procedure.
You cannot step into built-in procedures.
* Step
Over (F3) – Execute the next line and skip over any debuggable
procedures.
*
Continue (F4) – Continue executing until completing or until hitting a
break point.
* Set a
Breakpoint (F5) – A breakpoint is a marker in the source code that
tells the debugger to pause execution.
* Clear
Breakpoint (F6) – Remove a breakpoint.
* Clear
All Breakpoints (F7) – Remove all breakpoints.
* Stop
Execution (F8) – Stop Execution and return to the parameter screen.
*
Restart (F9) – Stop Execution and return to the first line of the
procedure.
The
second pane is the code pane. Your source code is listed in this
pane. You can set a breakpoint by either clicking on a line
number or by clicking on the line and hitting the Set Breakpoint icon.
You can unset the breakpoint by clicking on the line number a second
time or by clicking the unset breakpoint icon.
In
Figure 5.55, I set a breakpoint at line 14. When I press the
continue icon (F4), the program will continue to that point.
Notice that the variables are set in pane 3.
Figure
5.55: PL/Debugger Breakpoint & Local Variables
The
third pane is the variables pane. In the first tab, you can see
and change any local variables that are defined. Click in the
Value column and you can modify the value of that variable.
The
second tab is the Parameters tab (Figure 5.56). On this tab, you
can see any parameter values that you entered to run the procedure.
Figure
5.56: PL/Debugger Parameter Display
The
third tab, DBMS Messages (Figure 5.57), will contain any dbms_output
messages.
Figure
5.57: PL/Debugger DBMS Output Display
The
fourth and final pane is the call stack. Each procedure that you
call will be listed in that pane. In the display above (Figure
5.57), there is only a single item in the call stack.
If you
run the procedure to completion (you can hit F5 to do so), then you
will see a results window (Figure 5.58).
Figure
5.58: PL/Debugger Results Window
You can
press the X in the upper right corner to close the debugger windows.
Third Party Tools
While
Developer Studio is a good tool, it may not meet your needs for some
reason or you may just prefer a different interface.
EnterpriseDB is based on PostgreSQL and as such, there are many
third-party options should you choose them. I use third-party
tools for PL/TCL and PL/Perl programming.
Note
that, as of the time I write this book, the only tool that I have been
able to find that supports EnterpriseDB SPL and Oracle connections is
Developer Studio.
Below I
will briefly review a few of these tools. Keep in mind that this
book is primarily about EnterpriseDB and its tools. The tools I
mention below include a completely free, open source IDE (pgAdmin
III), a freeware, lite version of a commercial tool (EMS SQL Manager)
and a full-blown
commercial product (Navicat). These three are three that I have
personally used and like. There are many, many more.
Do an internet search on "postgresql ide" and you should find plenty.
You will
see some common features in all of the IDEs reviewed below. All
of them require that you define a connection; they all provide a SQL
editor and a code editor; they all provide some sort of backup and
restore. What the feature is called within the product may
change and the look and feel changes, but they all provide the same
basic functionality.
If you
choose one of these IDEs, you will probably make the choice on two
criteria: price and ease of use. If you are a casual user
of PostgreSQL or just need an occasional tool to use with Developer
Studio, pgAdmin III or EMS SQL manager lite are
the logical choices.
If you
are a more frequent or heavy user (hacker even), you may want to
upgrade to Navicat or EMS SQL Manager commercial. With the
commercial version of these tools, you get a little bit more robust
interface and paid support.
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.
|
