Server and connection status views
The status views for your database server and the current connection are available via the corner menu of the Connection list (Database Navigator).
Display Connection Information
If you need to find out more about your database server release or the release number of your JDBC driver take a look at the Connection Information window. To open it select "Display Connection Information..." from the Connection list's corner menu (Database Navigator).
Display Server Status
SQL4X Manager J includes over 15 pre-defined status views to retrieve more information about your database server's current workload and status. All status views are available via Database Navigator -> [your Oracle connection] -> corner menu -> Special (see snapshot on the left side).
Actually the views retrieve the current database status from the Oracle Data Dictionary. This means your login users needs the access privileges to read data from dba views to display most status views. If your login user does not have enough access privileges the error message "table or view does not exist" is displayed.
The server status views are optimized for Oracle 9i. Some of the views will not show any values with earlier Oracle releases as older Oracle database servers do not provide all the necessary data. For more information on how to read the displayed values see your Oracle Data Dictionary documentation.
Modifying Status Views
SQL4X Manager J includes a Script Editor to edit the SQL scripts that are used to retrieve the status views. Select Tools menu -> Script Editor to start the Script Editor and load the script file. Select your Database name (this is the name reported back by your JDBC driver). The Section list defines where a script is displayed. The scripts defined in the CONNECTION, CATALOG, SCHEMA and TABLE section are added to the appropriate section of the Database Navigators Corner Menu.
After changing a script with the Script Editor you have to re-open the Database Navigator window. The script file is only parsed once while the Database Navigator window is opened.
Manage Database Objects (Stored Procedures, Functions, Trigger, etc.)
SQL4X Manager J includes a special assistant to manage Oracle database objects, display the object source code (including PL/SQL), change the objects (and re-compile PL/SQL if necessary) and display update errors.
To access the Oracle Object Manager use Database Navigator, select your Oracle connection and the schema your objects are in. Now open the corner menu form the Objects list (see snapshot on the left side) and select "Object Manager..." to open the Object Manager window.
To display your objects select the object type (Function, Java Source, Package, Package Body, Procedure, Trigger, Type or Type Body) from the Type selection and click Reload. All objects of the selected type available in the current schema are displayed in the list.
Display and Edit Object Source Code
To display the object's source code select the object in the list. The source code is displayed on the right side. You can modify the source code and click "Update Object" to save the changes back to Oracle.
Renaming Objects
If you change the object name in the source code view and click "Update Object" a new copy of this object is created with the new name. The original object is not modified by this action. To rename a object you need to delete the original after creating a copy.
Adding new Objects
Click the + sign on the right side of the object list to create a new object. The Create Database Object sheet is displayed. Enter the new object's name and type in the sheet and click create. You can now enter the source code for your object in the source code view. Click "Update Object" to actually create the object.
Removing Objects
Select the object you want to delete in the object list and click the - sign on the right side of the object list to drop the selecte object.
Executing anonymous PL/SQL blocks and stored procedures
Use the SQL Console to run anonymous PL/SQL code blocks. The processing is quite similar to Oracle's SQL*Plus. Statements beginning with declare or begin are passed on to the Oracle database server for processing.
The following script can be executed with SQL Console to demonstrate how PL/SQL blocks are executed:
create table tmpval(first int);
select * |
Please note the slash (/) on a line of it's own after the PL/SQL code block to start the execution.
To execute stored procedures you have to enclose the procedure call in an anonymous PL/SQL block like above (begin/end block).
Using the DBMS_OUTPUT package
Introduced with SQL4X Manager J 3.4.1
SQL4X Manager J can process DBMS_OUTPUT results and display them. To enable DBMS_OUTPUT display the command "set serveroutput on" or "DBMS_OUTPUT.ENABLE" can be used.
The following script demonstrates how to turn the output on with DBMS_OUTPUT.ENABLE:
declare |
The command "set serveroutput off" or "DBMS_OUTPUT.DISABLE" can be used to turn off the serveroutput display again. All other options to the set serveroutput command are ignored by SQL4X Manager J.
Other SQL Console extensions
Oracle delivers with SQL*Plus a SQL front end with many enhanced commands that are not processed by the database server but by SQL*Plus itself. We have rebuilt some common and useful SQL*Plus commands with SQL Console, further enhancements will follow.
Special Commands
desc |
Use the describe command to retrieve a table definition: desc tablename displays all column definitions for tablename. |
|
| / | The slash (/) on a line of it's own delimits code blocks and starts execution. It's necessary to add a / after a PL/SQL code block to run it. | |
| set serveroutput on/off | Turns status value display generated with the DBMS_OUTPUT package on and off. | |