JDBExplorer     

 

This 100% Java utility allows the user to display the objects in a number of schemas in Oracle databases, including PL/SQL code, Java server procedures, tables, views, types, triggers and sequences. It displays user permissions, object dependencies and will capture dbms_output and Java output. It can display Java and PL/SQL code in a context-sensitive editor, and allows the code to be compiled and will display any errors. With access to the plan_table it can also display explain plans for entered SQL statements.

Non-interactive PL/SQL debugging is supported where the user specifies where the breakpoints are to be, and which local variables should be displayed when the breakpoint is reached. The process is then run, and the debug output and any program dbms_output are displayed to the user in two separate windows.

Using the menu item "File/Logon on as another user" you can logon as different users on different databases, when you log a user off, all windows associated with the user will be closed.

The contents of this page are -

Startup and Logging on

On startup the window below will be displayed where the username and password of the schema to be displayed can be specified as well as the connection type and connection string. A thin connection connection string is displayed in the form machine:port:sid - the machine can be specified as a machine name or an IP address. If an OCI8 connection is to be used, then the connect string is the name of an entry in the tnsnames.ora file on this machine.

 

To log on as another user, use the File/'Logon as another user' menu option of the outermost window. This will display the same logon window and allow another schema to be displayed by opening an Explorer window per connection. Any subsequent windows opened from an explorer window have the account name included in their title to allow them to be linked to the appropriate account. All windows associated with an account use the same connection. The menu item Window/'Open Windows' lists all open windows and allows windows to be selected as the current one.

As JDBExplorer is not a multi-tasking system, any command that does not return immediately will block all interaction with JDBExplorer until it completes.

Using the toolbar button in an Explorer window logs the associated user off and closes all windows that used that connection. To exit JDBExplorer use the File/Exit menu item. Neither of these will check for outstanding changes before proceeding.

The Explorer Window

The left-hand-side of the screen is a tree containing all of the users objects and displaying the objects status. Clicking on an item in the tree will display more details on that item in the right-hand-side of the screen. In the case of tables and views their columns and data can be viewed, for tables indexes and constraints can also be seen, for views, the views SQL can be shown. For synonyms, the access the user has to that synonym is displayed.

If a code item is selected the code is displayed on the right-hand-side in an editor. Clicking on the editor toolbar buttons opens a new window which allows editing, searching, saving to a file and printing amongst the usual editing commands via the right-click pop-up menu. The code can be compiled via the toolbar or the pop-up menu using the button, the errors viewed, and new code items can be created via the toolbar button . Should compilation fail, a window will appear with the errors listed as well as the lines of the code they refer to.

Oracle types can also be displayed and can be compiled but only if the type is not being used by other objects in the database. Java code is also displayed in the tree on the left hand side, but only code compiled into the schema, rather than been loaded via the loadjava command can be displayed.

Java Server Procedures

These can be created, edited and compiled in the default schema using JDBExplorer when a license has been purchased. The classes will be compiled as java source into the database in the following way

create or replace and resolve java source named "Oscar" 
as public class Oscar {
public static String quote() { 
System.out.println("return string "); 
return "I can resist everything but temptation.";
} };

so that the source and the class file will be present in the database. Code can be edited in the java editor which will also compile the code via the toolbar button . In order to do this either of the java privileges, javasyspriv or javauserpriv, will be required as well as the create procedure privilege . These will have to be granted directly to the user account, not via a role.

PL/SQL Editor

This is used to create and edit functions, procedures, packages and package bodies. It can also edit PL/SQL object types and trigger definitions. It allows all the usual edit functions, including undo and redo, via both the toolbar and the pop-up menus. The PL/SQL editor is invoked by clicking on the button in the toolbar when a procedure, function, package, package body, trigger or object type is selected in the object tree.

Displaying Tables and their data

When displaying a table's or view's data all data is retrieved from the database. A where clause for the currently selected table can be entered using a toolbar button , and the data displayed can be ordered by double-clicking on a column's header. Double-clicking again will order by the same column descending.

A table's data can be modified and records can be added, using the toolbar button , and deleted via toolbar button , but only if an order by preference has not been set. Also, only columns of type varchar2, date, number and long can be changed in existing data, and only tables with all columns of this type can have records added.

Double-clicking on a cell in the grid initiates changing of the contents, the changes are only accepted when the cell is exited. New records are applied when the changes are saved, so errors may occur for invalid of duplicate data.

SQL Frame

The SQL Frame is activated by the toolbar button , and supports the entering of SQL statements and displays the returned data in a grid where appropriate. 

It can execute DML and DDL statements and will run anonymous PL/SQL blocks when the key is clicked. Any dbms_output output can be displayed in the output monitor invoked by clicking on the toolbar button on the main window, and then clicking on the  ‘Capture Output’ button to retrieve all outstanding output from either PL/SQL or Java code. 

The SQL Frame will also display the explain plan for the last DML statement in a separate window if it has access to a plan_table. This is done by clicking on the button.

The SQL frame is also used to edit views.

The SQL Frame is divided by a horizontal bar that can be dragged to allow either the frame containing the text or the data grid to be larger than the other. Also the black arrows on the left hand side of the bar can be used to have only the text pane or the data grid visible, so that when running PL/SQL code, the grid can be hidden.

When a DDL or some PL/SQL code has been run in this window, if any errors have occurred they are displayed, if not, a message is displayed to indicate that processing has finished.

PL/SQL Debugger

Debugging is only available when JDBExplorer has access to the DBMS_DEBUG package and the license file. If you do not have access to this package, then you need to compile the file dbmspb.sql as the SYS user. Please check the relevant Oracle documentation before proceeding.

Debugging in JDBExplorer is non-interactive. When debugging is enabled via the toolbar button, breakpoints can be created by right-clicking on the line in the Explorers PL/SQL editor for procedures, function, package bodies and type bodies. The user can the specify which local variables to output, and can specify a stack trace to be displayed or for the program to be stepped using the commands 'stack' and 'step' respectively. These commands and the variable names should be comma separated. It is also possible to set a local variable's value using the 'set' command followed by a valid PL/SQL assignment command. For example -

    set x := 3 or set name := 'Fred'

The breakpoints and the commands are displayed in a window. Debugging can only be started from a parameter-less procedure, and can be invoked from a toolbar button .

When the program being debugged finishes, the debug output from the breakpoints is displayed in a window, and any dbms_output from the program is displayed in another window. By right-clicking on a line with a breapoint, the commands can be amended, and the program under test can be re-run.

System View

If the current user has the DBA or the SELECT_CATALOG_ROLE privilege then a toolbar button will allow system information to be displayed. The window that is displayed has tabs at the top that allows session and lock information (which are both updated every 5 seconds) tablespaces, rollback segments, free space, users and their granted roles, and tables with more than 2 extents to be displayed.

Toolbar Icons

A number of toolbar icons are used through out JDBExplorer, this section is to detail their meaning wherever they are used in toolbars or pop-up toolbars.

Icon

 Meaning
Cut
Copy
Paste
Print 
Open
Undo
Redo
Goto line
Save to file or database
Find text in the displayed code
Find pervious string again
Refresh Explorer
Log off
Edit object
Run debugger
Drop object
Show objects dependencies and items dependent on it
Compile code
Load and Resolve Java Programs
Run SQL Frame
Enter where clause when displaying table of view data
Create new code object
Display DBMS_OUTPUT window
Do an explain plan for last statement - SQL Frame only

Configurable Toolbar Buttons

It is now possible to add toolbar buttons to the Explorers toolbar. Two new buttons exist on the toolbar which allow button creation and removal. These buttons will open a new SQL Frame with the contents of the file specified on creation. The file name and the button hint need to be specified for a button to be created, and the buttons will only be seen if either the next time the Explorer is opened on logon, or when JDBExplorer is restarted.

General

JDBExplorer  requires a Java 1.2.2 runtime to be installed, and has been tested on Windows 98, ME and 2000 against an Oracle 8.1.6 database using both OCI8 and thin JDBC drivers.

If you have any comments on JDBExplorer, please feel free to e-mail me.