Home / Middleware / Oracle JDeveloper

25 Using the Database Tools

This chapter provides an introduction to the various tools that JDeveloper uses to help you work with and manage databases.

This chapter includes the following sections:

• Section 25.1, "Using the Databases Window"

• Section 25.2, "Using the Structure Window"

• Section 25.3, "Using the Database Reports Window"

• Section 25.4, "Using the Find Database Object Window"

• Section 25.5, "Using the SQL Worksheet"

• Section 25.6, "Using the SQL History Window"

• Section 25.7, "Using the Snippets Window"

• Section 25.8, "Using the Database Object Viewer"

• Section 25.9, "Using SQL*Plus"

• Section 25.10, "DBMS Output Window"

• Section 25.11, "OWA Output Window"

25.1 Using the Databases Window

The Databases window provides you with a complete editing environment for online databases. You can create, update and delete database objects using the Databases window.

The Databases window is integrated with:

• The SQL Worksheet.

• The Database Object Viewer.

In addition, you can drag database objects from a database connection onto a database diagram to either:

• Model the database objects on the diagram.

• Reverse engineer database objects to a project, and model the offline database objects on the diagram.

For more information about database modeling, see Section 5.8, "Modeling with Database Diagrams."

When you first open the Databases window, it appears in the default docked position, which is in the upper left-hand corner, flush with the main work area of JDeveloper. When more than one window is open in the same position, each appears with a tab displaying its name.

Right-click on a node within the Databases window to bring up a context-sensitive menu of commands. The menu commands available depend on the node selected. You can open nodes in their default editors, as well as other editors common to that node type, using the context menu.

Table 25-1 Databases Window Toolbar Icons

Icon	Name	Function
	New Connection	Click to open the Create Database Connection wizard, where you enter the details to create a connection to a database.
	Refresh	Click to synchronize the display in the Databases window with the contents of the connection.
	Apply Filter	Click to filter which objects will be displayed for a given connection. To enable the icon, select a node within the connection in the Databases window and wait for the connection to be established.

You can perform various tasks from the context menus in the Databases window. Right-click the IDE Connections node (for globally defined connections) or an application name node (for connections that are locally-scoped, and just available within the application) and select the appropriate menu item to:

• Create a new database connection.

• Import an XML file with connection definitions.

• Export current connections.

You can perform the following operations from a database connection node:

• Connect to and disconnect from the database.

• Delete the database connection.

• Generate SQL from database objects.

• Reverse engineer database objects as offline database objects to a project.

• Run SQL*Plus.

• Filter the objects displayed in the connection.

• Edit the database connection properties.

• Open the SQL Worksheet.

• Generate DB doc

• Remote Debug

• Gather Schema Statistics

• XML DB Protocol server configuration

• Perform remote debugging if you are using the Java Platform Debugger Architecture (JPDA) using a debugger to listen so that a debuggee can attach to the debugger.

There are additional options available from database object type nodes (for example, Tables, Indexes, or Procedures) or from database object nodes (such as a specific table, or a specific view). The options available depend on the node selected.

25.2 Using the Structure Window

The database view of the Structure window displays details of a database object selected in the Databases window, or an offline database object selected in the Applications window.

When you select a database object such as a table in a database connection in the Databases window or an offline database object such as a table in an offline database in the Applications window, a node for that object is shown in the Structure window. You can expand the node to see details of the sub objects that make up the database object. In the case of a table, these include sub objects such as columns, constraints, and indexes.

Table 25-2 Icons in the Structure Window

Icon	Name	Function
	Refresh	Click to refresh the content of the Structure window. You may want to do this when a database object has been changed outside JDeveloper, for example using SQL*Plus, and you want to be sure that the Structure window reflects the current state of the object.

You can perform the following operations from the database view of the Structure window:

• View properties or edit properties (offline database objects only) by choosing Properties from the context menu of an appropriate node. The Edit dialog for the object type opens. It is read only for database objects.

• Use a database object or offline database object such as a table as a template to create a new object by choosing Use as Template from the context menu. The Create dialog for the object type opens.

• Find usages of an offline database object such as a table by choosing Find Usages from the context menu.

25.3 Using the Database Reports Window

Use the Database Reports window to view reports about the database and its objects.

You can also create your own user defined reports.

To open a pre-defined report, expand Data Dictionary Reports and navigate to the report you want. Double-click the report name to run it. A number of dialogs may be displayed before the report is opened in the Reports Results window:

• Select Connection dialog (all reports), where you can choose an existing database connection or create a new database connection. Once you have chosen the connection, the same connection is used for subsequent reports you run.

• Enter Bind Values dialog (All Objects reports), where you can enter values for each bind variable. Bind variables enable you to restrict the output.

• Diagnostic Pack Required dialog (ASH and AWR reports). You must have a licensed copy of Oracle Diagnostic Pack running on the database to run these reports, and the dialog allows you to confirm that you have one.

You can create your own reports and store them in folders and sub-folders under the User Defined Reports node.

Some reports may take some time to run, and the time is affected by the number and complexity of objects involved, and by the speed of the network connection to the database.

From the Data Dictionary Reports node you can:

• Export a report into an XML file that can be imported later by right-clicking the report name and choosing Export.

• Create a shared report from an exported report.

User Defined reports are any reports that are created by JDeveloper users.

Information about user defined reports, including any folders for these reports, is stored in UserReports.xml in the directory for user-specific information.

You can perform the following operations from the User Defined Reports node:

• Create a user defined report by choosing Add Report from the User Defined Reports context menu.

• Organize user defined reports in folders, and create a hierarchy of folders and subfolders. Choose Add Folder from the User Defined Reports context menu.

• Import a report that had previously been exported. Select report folder in which to store the imported report, right-click, and select Import.

The Shared Reports node is displayed once you have defined the first shared report in the Preferences dialog.

For more information about creating and sharing database reports, see Section 26.9, "Working with Database Reports".

25.4 Using the Find Database Object Window

The Find Database Object Window allows you to search for and work on database objects within a live database.

The Find Database Object Window is fully integrated with the online database functionality, including the SQL Worksheet and the Database Object Viewer.

While you are using the Find Database Object Window these features are available:

• Open any currently closed window, or bring a currently open window to the foreground, using Window > window-name.

• Move, resize, float, minimize, maximize, restore or close the Find Database Object Window using the context menu available by right-clicking its tab or by pressing Alt+Minus.

Table 25-3 Find Database Object Toolbar

Name	Function
Connection	Choose the database connection to search in from the dropdown list. You must already have a connection to the database.
Name	Enter the search term. You can use the wildcard % to return a number of matching objects.
Type	Choose the type of database object to restrict the search to. The default is ALL OBJECTS.
Usage	Only for certain types of object. Choose the usage of the object, for example ALL.
Lookup	Click to display the results of the search. The results of the search are displayed in the panel. Double-click on an object to open it in the appropriate editor.

You can perform the following tasks from the Find Database Object window:

• Close or open the panel by clicking its bar.

• Change the area used by the panel by grabbing its bar and moving it up or down.

• Remove the panel from view by opening its dropdown menu (panel bar, far right) and choosing Minimize. Restore it by clicking the three dots at the very bottom of the Applications window and then clicking Recent Files.

• Open an object, or the parent object that contains the specified object, in its default editor, or bring the default editor into focus, by selecting the object in the list.

25.5 Using the SQL Worksheet

Use to enter and execute SQL, PL/SQL, and SQL*Plus statements. You can specify any actions that can be processed by the database connection associated with the worksheet, such as creating a table, inserting data, creating and editing a trigger, selecting data from a table, and saving that data to a file.

You enter SQL statements in the SQL Statement area, and use the buttons on the toolbar to perform actions.

Table 25-4 describes the icons and fields in the toolbar above the SQL Worksheet statement area.

Table 25-4 Icons in the SQL Worksheet Toolbar

Icon	Name	Function
	Run Statement (Ctrl+Enter)	Click to execute the statement at the mouse pointer in the SQL statement area. The SQL statements can include bind variables and substitution variables of type VARCHAR2. If necessary, VARCHAR2 is automatically converted to NUMBER. If you use variable values, a window is displayed for you to enter them.
	Run Script (F5)	Click to execute all statements in the SQL statement area. The SQL statements can include bind variables and substitution variables of type VARCHAR2. If necessary, VARCHAR2 is automatically converted to NUMBER. If you use variable values, a window is displayed for you to enter them.
	Autotrace (F6)	Click to generate trace information for the statement. To see trace information, click the Autotrace tab.
	Explain Plan (F10)	Click to generate the execution plan for the statement, which internally executes the EXPLAIN PLAN statement. Trace information is shown in the Explain Plan Results window.
	Commit (F11)	Click to write any changes to the database. This ends the transaction and clears any output in the Results and Script Output tabs.
	Rollback (F12)	Click to discard any changes without writing them to the database. This ends the transaction and clears any output in the Results and Script Output tabs.
	Unshared SQL Worksheet (Ctrl+Shift+N)	Click to open a new unshared SQL Worksheet for a different connection.
	To Upper/Lower/Inicat (Ctrl+Quote)	Click to switch the selected text between upper case, lower case, and initial capitals.
	Clear (Ctrl+D)	Click to erase the statement or statements in the Enter SQL Statement area.
	Cancel	(Only displayed while a script is running) Click to stop execution of the script.
	SQL History	Click to open the SQL History window.
		(Only displayed once a statement or script has run) Displays the time it took to execute a statement or run a script. This can be used with Explain Plan to provide useful tuning information.
		Use to choose a different database connection.

The results area has a number of tabs:

• Results tab, Displays the results of clicking Run Statement.

• Script Output tab, which displays the results of clicking Run Script.

• Autotrace tab, which displays output as a result of clicking Autotrace.

• Explain tab, which displays output as a result of clicking Explain Plan.

The SQL Worksheet provides code insight for SQL code. When you type a word, a dropdown menu of valid code appears. For example:

• If you type select, SELECT is displayed.

• If you type select *, a list containing BULK, FROM, and INTO is displayed.

• If you are connected to the HR schema and type select * from em, a list containing the table employees and the view emp_details_view is displayed.

To configure Code Insight for the SQL Worksheet:

1. Select Tools > Preferences > Code Editor > Code Insight.

2. In the Code Insight page, adjust font size or font type, and completion insight and parameter insight timing.

3. Click OK. Your changes are active the next time you use the editor.

To open the SQL Worksheet:

1. Choose Window > Database > Databases window.

2. Expand IDE Connections or Application Connections.

3. Right-click the connection in the window, and choose Open SQL Worksheet.

   Alternatively, click the SQL Worksheet button on the JDeveloper toolbar.

   For more information at any time, press F1 or choose Help from within the SQL Worksheet.

Alternatively, from the main toolbar, click and choose the database connection from the Choose Connection dialog.

You can create a SELECT statement by dragging and dropping table and view names, and by graphically specifying columns and other elements of the query using Query Builder. You can run the statement within Query Builder to see the results, and when you close Query Builder, the resulting SELECT statement is inserted into the SQL Worksheet.

To use Query Builder:

1. Open the SQL Worksheet.

2. Select the Query Builder tab. For more information at any time, press F1 or click Help from within the SQL Worksheet.

3. Select the schema you want, and drag the table you want to base the query on onto the main pane of the dialog. There will be a delay of a few seconds while Query Builder connects to the database and loads information about the table.

4. The first time, you are prompted to choose the type of statement you want to use.

5. Use the SQL Worksheet buttons to perform the action you want, for example to run a query.

To execute a SQL statement:

1. Enter a SQL statement in the worksheet's upper pane.

2. Do any one of the following:

   • Press Ctrl+Enter.

   • Click the Execute the statement button on the toolbar.

   • Right-click, and select Execute SQL Statement from the context menu.

3. View the data returned by the statement in the lower pane.

For more information, see "Using the SQL Worksheet" in the Oracle Database SQLJ Developer's Guide.

25.5.1 Using Execution Plan

An execution plan is the sequence of operations that will be performed to execute the statement, and you can use the SQL Worksheet to inspect the execution plans chosen by the Oracle optimizer for SQL SELECT, UPDATE, INSERT, and DELETE statements. You can also view explain plan for the SQL code for the query part of a view definition.

An execution plan shows a row source tree, which is the hierarchy of operations that comprise the statement. For each operation it shows the following information:

• An ordering of the tables referenced by the statement

• An access method for each table mentioned in the statement

• A join method for tables affected by join operations in the statement

• Data operations such as filter, sort, or aggregation

In addition to the row source tree, the plan table displays the following information about selected operations:

• Optimization, such as the cost and cardinality of each operation

• Partitioning, such as the set of accessed partitions

• Parallel execution, such as the distribution method of join inputs

For more information, see "Using EXPLAIN PLAN" in the Oracle Database SQL Tuning Guide.

An additional source of information that can be used to tune SQL queries is the elapsed time that is displayed in the toolbar of the SQL Worksheet when statements are executed or scripts are run.

To view a SQL statement's execution plan:

1. If necessary, open the SQL Worksheet.

2. Enter a SQL statement in the worksheet's upper pane.

3. Do one of the following:

   • Click the Explain Plan button on the toolbar.

   • Right-click to open the context menu, and select Execute Explain Plan.

   The Explain Plan tab shows the explain plan information for the SQL statement.

25.5.2 How to Recall Statements from the SQL Worksheet History

The statements executed in a session with the SQL Worksheet are preserved in a history list. You can retrieve previous statements from the history, and re-execute them or view their execution plans.

To recall a statement from the SQL Worksheet history:

1. Do either of the following:

   • Click the SQL History button.

   • Right-click in the SQL Worksheet to open the context menu, and select History.

   A window showing the list of the statements previously entered is displayed.

2. Select the desired statement from the window.

3. Click OK.

The statement is displayed in the upper pane of the worksheet.

25.6 Using the SQL History Window

The SQL History Window allows you to reuse statements previously executed in a session with the SQL Worksheet.

SQL statements and scripts that you have executed are listed in the window, and you can select one or more statements to have them either replace the statements currently on the SQL Worksheet or be added to the statements currently on the SQL Worksheet.

While you are using the SQL History Window these features are available:

• Open any currently closed window, or bring a currently open window to the foreground, by choosing it from the Window menu.

• Move, size, float, minimize, maximize, restore or close the Find Database Object Window using the context menu available by right-clicking its tab or by pressing Alt+Minus.

Table 25-5 SQL History Toolbar Icons

Icon	Name	Function
	Append	Click to append the selected statement or statements to any statements currently on the SQL Worksheet. You can also append the selected statement or statements by dragging them from the SQL History window and dropping them at the desired location on the SQL Worksheet.
	Replace	Click to replace any statements currently on the SQL Worksheet with the selected statement or statements.
	Clear History	Click to remove all statements from the SQL history.
	Filter	Use to filter the SQL statements visible in the SQL History window. Type a string in the text box and click Filter. Only SQL statements containing that string are listed. To remove the filter, delete the string in the field and click Filter again.

25.7 Using the Snippets Window

Snippets are code fragments, such as SQL functions, Optimizer hints, and miscellaneous PL/SQL programming techniques. Some snippets are just syntax, and others are examples. The Snippets Window is integrated with the SQL Worksheet and when you are creating or editing a PL/SQL function or procedure.

In the Snippets Window, the snippets are organized in categories in the drop-down list, such as Aggregate Functions or Character Functions. You can create new snippets and add them to an existing category, or to a new category. To see a brief description of a snippet, hover the mouse pointer over the function name.

To insert a snippet into your code in a SQL Worksheet or in a PL/SQL function or procedure, drag the snippet from the snippets window and drop it into the desired place in your code; then edit the syntax so that the SQL function is valid in the current context.

For example, you could type SELECT and then drag CONCAT(char1, char2) from the Character Functions group. Then, edit the CONCAT function syntax and type the rest, as in this example:

SELECT CONCAT(title, ' is a book in the library.') FROM books; 

Table 25-6 Snippets Window Toolbar Icons

Icon	Name	Function
	Add Snippets	Click to open the Save Snippet dialog where you can create a new snippet and save it in an existing group or in a new group.
	Edit User Snippets	Click to open the Edit Snippets dialog which lists user snippets. You can create, edit and delete snippets in this dialog.

From the Snippets window, you can:

• Display snippets by choosing the category from the list.

• Add a snippet to the cursor position in a file such as a SQL file by double-clicking it.

25.8 Using the Database Object Viewer

The Database Object Viewer allows you to manage the structure and contents of objects in a database. The tabs available depend on the type of object being viewed.

You can edit the value in any of the cells by double-clicking the cell to select it, then clicking ... to open the Edit Value dialog.

Information about the object is contained in a number of tabs.

• Columns, which shows the columns comprising the object.

• Data, which shows the data in this object. You can edit the value in any of the cells by double-clicking the cell to select it, then clicking ... to open the Edit Value dialog.

• Constraints, which shows the details of any constraints.

• Grants, which shows privilege details.

• Statistics, which shows statistical information.

• Triggers, which shows information about triggers.

• Dependencies, which shows information about references.

• Indexes, which displays details of any indexes.

• Details, which displays details of the object.

• SQL, which displays the SQL that represents this object.

25.8.1 Database Object Viewer Tabs Toolbars

The specific buttons on the toolbar vary from tab to tab.

Table 25-7 Database Object Viewer Tabs Toolbar Icons

Icon	Name	Function
	Freeze and Unfreeze View	Use to toggle freezing the table viewer on the current view.
	Edit	Click to open the Edit Table dialog.
	Refresh	Click to refresh the data.
	Insert Row	Click to insert a new blank row below the row where the focus is.
	Delete Selected Row(s)	Click to delete the selected rows of data.
	Commit	Click to commit the changes to the database. The changes are logged in the Data Editor log window, and commit will fail if there is an error, such as a unique constraint violation.
	Rollback	Click to rollback database changes already made. The Data Editor log window reports on whether the rollback has succeeded.
		Click to open the Sort dialog where you specify the columns to sort by and the sort order.
		Enter a value to reduce the number of records displayed, for example DEPARTMENT_ID>20.
		Click to perform one of a range of common table actions.

25.9 Using SQL*Plus

SQL*Plus is an interactive and batch query tool that is installed with every Oracle Server or Client installation. It has a command-line user interface. You can launch SQL*Plus from within JDeveloper. For more information, see the SQL*Plus User's Guide and Reference.

In most cases, using SQL Worksheet is preferable to using SQL*Plus as it is fully integrated with JDeveloper, and you can use SQL Worksheet to enter and execute SQL, PL/SQL, and some SQL*Plus statements.

SQL*Plus can use parameter substitution. The default escape character is '&', thus any comments that have '&' in them may cause an error. Additionally, the character used in the SQL*Plus session that runs the script can be changed from the default using SET DEFINE, so JDeveloper cannot look for the parameter substitution character in comments and warn you. If you encounter this error in a script, you can use SET DEFINE OFF to ignore the parameter substitution character or remove the character from the comment. For more information, see "'Using Scripts in SQL*Plus'" in the SQL*Plus User's Guide and Reference.

In order to launch SQL*Plus from JDeveloper, you must have SQL*Plus installed on your machine. For information about installing a SQL*Plus client, see the information about Oracle Database Instant Client at http://www.oracle.com/technetwork/database/features/instant-client/index-100365.html.

You can launch SQL*Plus from:

• The Tools menu

• A database connection in the Databases window

• A SQL file in the Applications window

If you have not already specified the SQL*Plus executable in JDeveloper, you will able to do so when you launch SQL*Plus. Alternatively, you can specify the SQL*Plus executable in the Preferences dialog. You only need to perform this task once.

To specify the SQL*Plus executable:

1. Choose Tools > Preferences, and select Database Connections.

2. Specify the path to the SQL*Plus executable.

3. Click OK to close the dialog. Now the SQL*Plus item is active in the Tools menu.

4. Select a database connection in the Databases window, then choose Tools > Database > SQL*Plus. If the path specified in step 2 is correct, a SQL *Plus command window will open.

Note:

On Unix, use xterm to create a terminal window to run the SQL*Plus command in.

To launch SQL*Plus from a connection:

1. Choose Window > Database > Databases window.

2. Right-click the connection, and choose SQL*Plus.

To launch SQL*Plus from a SQL file:

1. In the Applications window, navigate to a SQL file.

2. Right-click the SQL file, and choose Run in SQL*Plus.

3. In the submenu, select the connection you wish to use. If you have not already specified the location of the SQL *Plus executable, you will be prompted for that first.

25.10 DBMS Output Window

The PL/SQL DBMS_OUTPUT package enables you to send messages from stored procedures, packages, and triggers. The PUT and PUT_LINE procedures in this package enable you to place information in a buffer that can be read by another trigger, procedure, or package. In a separate PL/SQL procedure or anonymous block, you can display the buffered information by calling the GET_LINE procedure. The DBMS Output window is used to display the output of that buffer.

Add New DBMS Output Tab: Prompts you to specify a database connection, after which a tab is opened within the DBMS Output pane for that connection, and the SET SERVEROUTPUT setting is turned on so that any output is displayed in that tab. (To stop displaying output for that connection, close the tab.)

Table 25-8 DBMS Output Window Toolbar Icons

Icon	Name	Function
	Enable DMBS Output	Click to toggle the SET SERVEROUTPUT setting between ON and OFF. Setting server output ON checks for any output that is placed in the DBMS_OUTPUT buffer, and any output is displayed in this tab.
	Clear	Click to erase the content of this tab.
	Save File	Click to open the Save dialog where you can enter a filename to save the results in this tab.
	Print	Click to open the Print dialog, where you can choose the printer to print the content of this tab.
	Buffer Size	For databases before Oracle Database 10.2, click to limit the amount of data that can be stored in the DBMS_OUTPUT buffer. The buffer size can be between 1 and 1000000 (1 million).
	Poll	Move the slider to set the interval (in seconds) at which JDeveloper checks the DBMS_OUTPUT buffer to see if there is data to print. The poll rate can be between 1 and 15.
	Choose DB Connection	Change to a different database connection by choosing it from the list.

25.11 OWA Output Window

OWA (Oracle Web Agent) or MOD_PLSQL is an Apache (Web Server) extension module that enables you to create dynamic Web pages from PL/SQL packages and stored procedures. The OWA Output window enables you to see the HTML output of MOD_PLSQL actions that have been executed in the SQL Worksheet.

Table 25-9 OWA Output Window Toolbar Icons

Icon	Name	Function
	Enable OWA Output	Click to toggle the SET SERVEROUTPUT setting between ON and OFF. Setting server output ON checks for any output that is placed in the DBMS_OUTPUT buffer, and any output is displayed in this tab.
	Clear	Click to erase the content of this tab.
	Save File	Click to open the Save dialog where you can enter a filename to save the results in this tab.
	Print	Click to open the Print dialog, where you can choose the printer to print the content of this tab.
	Choose DB Connection	Change to a different database connection by choosing it from the list.