Oracle Tips by
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.
procedural code, in the navigator pane of Developer Studio, select the
procedure or function that you wish to debug (Figure 5.51).
5.51: PL/Debugger Stand-alone Code Units
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).
5.52: PL/Debugger Package Code Units
debug both SPL and PL/pgSQL code units and you can debug both
procedures and functions.
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).
5.53: PL/Debugger Arguments Dialog
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
ready, I can press the OK button that launches the debug window
5.54: PL/Debugger Main Window
debugger, like most other Developer Studio windows, is made up of four
topmost pane is the toolbar pane. From left to right is:
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.
Over (F3) Execute the next line and skip over any debuggable
Continue (F4) Continue executing until completing or until hitting a
* Set a
Breakpoint (F5) A breakpoint is a marker in the source code that
tells the debugger to pause execution.
Breakpoint (F6) Remove a breakpoint.
All Breakpoints (F7) Remove all breakpoints.
Execution (F8) Stop Execution and return to the parameter screen.
Restart (F9) Stop Execution and return to the first line of 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.
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.
5.55: PL/Debugger Breakpoint & Local Variables
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.
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.
5.56: PL/Debugger Parameter Display
third tab, DBMS Messages (Figure 5.57), will contain any dbms_output
5.57: PL/Debugger DBMS Output Display
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.
run the procedure to completion (you can hit F5 to do so), then you
will see a results window (Figure 5.58).
5.58: PL/Debugger Results Window
press the X in the upper right corner to close the debugger windows.
Third Party Tools
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.
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
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.
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
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.
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.
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.