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 -
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 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.
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.
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.
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.
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.
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.
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 |
![]() |
|
![]() |
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 |
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.
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.