Skip Headers
Oracle® Fusion Middleware User's Guide for Oracle JDeveloper
11g Release 2 (11.1.2.3.0)

Part Number E17455-04
Go to Documentation Home
Home
Go to Table of Contents
Contents
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

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:

25.1 Using the Database Navigator

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

The Database Navigator is integrated with:

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

For more information about database modeling, see Section 23.5, "Database Diagram."

When you first open the Database Navigator, it appears in the docked position, along with any other open navigators. Its default docked position is in the upper left-hand corner, flush with the main work area of JDeveloper. When more than one navigator is open, each appears with a tab displaying its name.

Right-click on a node within the Database Navigator 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 Database Navigator Toolbar Icons

Icon Name Function
New connection

New Connection

Click to open the Create Database Connection wizard, where you enter the details to create a connection to a database.

Refresh

Refresh

Click to synchronize the display in the navigator with the contents of the connection.

Filter

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 navigator and wait for the connection to be established.


You can perform various tasks from the context menus in the Database Navigator. 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:

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

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 Database Navigator, or an offline database object selected in the Application Navigator.

When you select a database object such as a table in a database connection in the Database Navigator or an offline database object such as a table in an offline database in the Application Navigator, 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 SQL Worksheet Toolbar

Icon Name Function
refresh button

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:

25.3 Using the Database Reports Navigator

Use the Database Reports Navigator 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:

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:

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:

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.8, "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:

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:

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

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

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

Autotrace (F6)

Click to generate trace information for the statement. Too see trace information, click the Autotrace tab.

Explain plan

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

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

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

Unshared SQL Worksheet (Ctrl+Shift+N)

Click to open a new unshared SQL Worksheet for a different connection.

Switch between upper case, lower case and init caps

To Upper/Lower/Inicat (Ctrl+Quote)

Click to switch the selected text between upper case, lower case, and initial capitals.

Clear

Clear (Ctrl+D)

Click to erase the statement or statements in the Enter SQL Statement area.

Cancel

Cancel

(Only displayed while a script is running) Click to stop execution of the script.

Time taken to execute  

(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.

DB connection  

Use to choose a different database connection.


The results area has a number of tabs:

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

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 View > Database > Database Navigator.

  2. Expand IDE Connections or Application Connections.

  3. Right-click the connection in the Navigator, 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. Right-click and choose Query Builder. For more information at any time, press F1 or click Help from within the Query Builder.

  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. Choose the columns you want to use in the query from the dialog that is displayed.

  5. To add a WHERE clause, click the Create Where Clause tab, and enter values for the clause.

  6. View the SQL comprising the query in the View SQL tab.

  7. You can see the results of the query by selecting the View Results tab, and clicking Run Results.

  8. When you press Apply in the Query Builder dialog, the dialog closes and the query is inserted into the SQL Worksheet.

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 Performance 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 View History dialog button.

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

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

  2. Select the desired statement from the dialog.

  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:

Table 25-5 SQL History Toolbar Icons

Icon Name Function
Append

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

Replace

Click to replace any statements currently on the SQL Worksheet with the selected statement or statements.

Clear history

Clear History

Click to remove all statements from the SQL history.

Filter

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

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

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:

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.

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 view Unfreeze view

Freeze and Unfreeze View

Use to toggle freezing the table viewer on the current view.

Edit

Edit

Click to open the Edit Table dialog.

Refresh

Refresh

Click to refresh the data.

Insert row

Insert Row

Click to insert a new blank row below the row where the focus is.

Delete selected rows

Delete Selected Row(s)

Click to delete the selected rows of data.

Commit

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

Rollback

Click to rollback database changes already made. The Data Editor log window reports on whether the rollback has succeeded.

Sort  

Click to open the Sort dialog where you specify the columns to sort by and the sort order.

Filter  

Enter a value to reduce the number of records displayed, for example DEPARTMENT_ID>20.

Actions  

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:

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 Database Navigator, 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 View > Database > Database Navigator.

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

To launch SQL*Plus from a SQL file:

  1. In the Application Navigator, 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 DBMS output

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

Clear

Click to erase the content of this tab.

Save file

Save File

Click to open the Save dialog where you can enter a filename to save the results in this tab.

Print

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 DBMS output

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

Clear

Click to erase the content of this tab.

Save file

Save File

Click to open the Save dialog where you can enter a filename to save the results in this tab.

Print

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.