Display editors

Once you have expanded an object type node in the Connections navigator, selecting any object in that node opens a window of tabs, called display editors, which define the object. Each editor displays a data grid of details describing the selected object. These are based on queries against the data dictionary and you can get the same results by writing the SQL yourself.

The number and types of editors displayed will vary depending on the object or database that you are connected to. If you are connected to Oracle Database 11g or above, then an additional Flashback editor displays with the other table display editors.

Note

The Partitions tab is permanently displayed from SQL Developer 2.1.

General display editors

Instead of itemizing each of the object types and the different collections of display editors, we'll use the Tables node to review some of the display editor details. Using the HR connection, select EMPLOYEES in the Tables node to see the general display editors, as shown in the following screenshot:

The Columns editor displays all of the column detail, including column comments. To get a feel for queries that run behind the editors, run the following query in the SQL Worksheet:

SELECT COLUMN_NAME,DATA_TYPE
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME ='EMPLOYEES'
AND OWNER = 'HR';

The output from the query matches the first two columns of the Columns display editor (this is a simplified example). If you need to find out more information about any object in the database without a GUI tool, you need to start by querying the data dictionary to determine which tables hold the metadata about your objects. From there, you must decide what detail you need from these tables, in our example it was the single ALL_TAB_COLUMNS table, and then write the join clause to query all of the selected tables.

There is a set of editors for each of the object types. For tables, these include Constraints, Grants, Statistics, Triggers, and Partitions. The data in each data grid is a result of a query joining a number of data dictionary tables. SQL Developer provides a framework for you to create and add your own display editors. You can do this with user extensions.

Some of the editors display master-detail output. To see the detail result set, you need to select an individual record in the master result set. The following screenshot shows the EMP_NAME_IX for the EMPLOYEES table. By selecting the index, you can quickly see that this index is made up of two columns, as shown in the following screenshot:

To create the index using SQL, use the following query:

CREATE INDEX "EMP_NAME_IX" ON "EMPLOYEES" ("LAST_NAME", "FIRST_NAME");

Working with the display editors

Each new object selected in the navigator replaces the last, regardless of object type or connection, unless you click on the Freeze View pin button () on the object display. This locks the window and a new window opens with a new set of editors.

Note

Freezing the Object Viewer

You can control whether each new object select opens a new set of editors. Select Tools | Preferences in the tree display, expand Database. Next, click on ObjectViewer and select Automatically Freeze Object Viewer Windows.

To see this feature in action, expand the HR Tables node. Select the EMPLOYEES table and note the set of display editors. Now select the DEPARTMENTS table, and note that it replaces the details of the EMPLOYEES table. Expand the Sequences node and click through each of the sequences available. These now replace the tables which were previously displayed. This replacing feature is very useful as it saves screen real estate and keeps the screen and work area uncluttered. However, it is also very useful to be able to compare two sets of data, side by side. Therefore, by selecting the Freeze View pin, each new object selected opens in a new window and you can then tile the windows.

Tip

SQL Developer automatically opens the display editors as you click on the object, or if you navigate down through the objects using the keyboard. You can control this by changing the default Open Object on Single Click behavior. Go to Tools | Preferences in the tree displayed, expand Database, select ObjectViewer, and deselect Open Object on Single Click.

Using the SQL display editor

The SQL editor is displayed at the end of the set of shipped display editors and is available for most object types (any editors you create are added after the SQL editor). The SQL editor displays the query required to recreate the object selected. When you select the SQL editor, SQL Developer uses the DBMS_METADATA package to query the database and return the SQL required for recreating the object selected. So, clicking on the editor with a table selected displays the SQL (DDL) for that table.

Select the COUNTRIES table in the Connections navigator. The default display provides the full CREATE TABLE SQL in a single statement, as shown in the following screenshot:

Tip

Downloading the example code

You can download the example code files from your account at http://www.packtpub.com for all the Packt Publishing books you have purchased. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.