Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




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.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter