JDBExplorer: a test-drive

Even though I started a journey into the realms of Open Source software available for Oracle , I’m taking a small detour now. JDBExplorer was suggested to me via a comment on Oracle Open Source: TOra (toolkit for Oracle).
As promised I have taken a look at it and now I’m posting my findings.

This 100% Java utility supports PL/SQL and Java development in Oracle Databases. Currently it’s only available for the Windows platform. The website indicates a linux-version to be underway.

This utility is not an Open Source project. It is available for download, but only as a trial. In order to use it with all of the advertised features, you’ll need to obtain a license. On the website there’s no information regarding the licensing fee or how to obtain one.

There are some limitations if you don’t have a license, like creating Java Server Procedures or debugging.
I played around with this tool for a while, and I must say it has some really nice features. One of the things I like is the customizable toolbar. You can add your own SQL-scripts under a button for easy execution. It will open up a SQL-Editor and load the specified file into it. It’s a lot easier than using a button to open a SQL-Editor, use the ‘Get SQL File’-button, find your script and then finally execute it.

While in the SQL-Editor, a nice touch is the “matching parenthesis functionality�.

As shown in the above example, my caret (cursor) is on the line that starts with select, right after multiset, the highlight is the last parenthesis on the screen. The editor shows a box around the parenthesis. Maybe it would be nice if this functionality is also available to match up your “ifâ€? or “loopâ€??
What I’m missing in the editors is an auto-indentation feature, the next line you start to code starts at the beginning. You have to add your own spaces in order to properly format your code.

Creating stored procedures gives you appropriate feedback of your actions. Either you’re shown a window displaying the errors encountered or you’re shown a pop-up that compilation was successful.
Too bad I wasn’t able to test the debugging functionality. According to the help files, it’s non-interactive. I’m curious how this would work, and especially if it’s workable. Until now I’ve only worked with interactive debuggers, allowing you to step-into, step-over, step-out of source code, so this would have been a new experience. Too bad.

When connecting to JDBExplorer it opens up a nice and big explorer window. It’s easy to navigate to the different types of database object you want to explore. The left side shows the navigation-tree and the right side shows you different tab-pages. If you highlight a table the table-tab-page is shown and shows you the relevant information on the right side. The same is true for the other database objects. The right side stays in sync with the left.
DBMS_OUTPUT can be monitored in a separate window, which requires a button to retrieve the output.
Opening multiple versions of the same source code is possible, this can lead to some undesired effects. This only happens if you’re not careful about what you’re doing.

Modifying table data via JDBExplorer has some severe limitations. This example shows some of these limitations.

create table d (id number, name varchar2(100), dt date);

It’s not possible to insert a new record into this table using the “Create New Row� button in the toolbar. Instead it shows you an error message:

After alteration of the table (I dropped the date-column), records could be inserted. This also has a strange side-effect. If you don’t navigate out of the inserted fields, they’re not considered filled. Commiting leads to null value in the database. By just navigating out of the field and using commit again, another record is inserted into the database. This new record is not reflected in JDBExplorer at first, after refreshing the data it is.
Updating values in a table can only be commited if you navigate out of the changed field.
I couldn’t delete the last record out of this table. Other records could be deleted, but not the last one.
There’s also a restriction on inserting or deleting, you can’t add or remove records if you apply an order by clause to the data.
This part of JDBExplorer could use some improvements. While you’re at it, maybe a single record view? 😉

Overall conclusion: JDBExplorer is a nice utility, it has some really nice features. It also has some limitations. If you use it primarily to change data in the database you’re probably going to be frustrated. If you’re going to use it to develop stored procedures in the database, it’s fun to play with this tool. Too bad I couldn’t test the debugging-functionality it has to offer.
Maybe there’s someone who has worked with the non-interactive debugger and can shed some light one the experience?