To open a connection in the navigator:

The first time you connect to a database schema, whether you open an existing connection or click on Connect in the New Database Connections dialog, SQL Developer automatically expands the connection in the Connections navigator and opens a SQL Worksheet. This automatic opening of the SQL Worksheet is controlled by a preference: Open a Worksheet on connect. Select Tools | Preferences, expand the Database node and select Worksheet.

Using the HR and OE schemas is useful for illustrating various features within SQL Developer. They do not reflect reality, where you'd typically be working with many more objects or sets of objects, within a schema or across schemas, and the mere act of expanding a node exposes a long and possibly unworkable list of objects. SQL Developer provides filtering for each object type, which allows you to control the display of objects.

To use the filter, select an object node, such as Tables, and select Apply Filter... from the context menu.

Depending on the object node selected, the choice of filter criteria varies. An important feature to note is the Include Synonyms checkbox, available for tables and views. All object nodes will only display those objects that belong to the schema. If one schema has access to the objects in another schema, you can create synonyms for those objects and, using the filter, have them display in the object node. Without the filter, you need to use the Other Users node to see the objects in another schema.

In the previous screenshot, the Filter dialog is for tables. For all object filters, you can add multiple selections as shown and select the options:

The filter criteria provides lists of choices. In the first for the column, values such as CREATED and LAST_DDL_TIME, and for the operator, values such as LIKE or<>. Prior to SQL Developer 2.1, the column drop-down list displayed only four choices. These four choices have since been expanded to provide greater control of the objects displayed.

Schema level filtering

To avoid needing to apply a schema at the level of each object type in the browser, you can set a schema filter. This is particularly useful if you enforce a naming standard that requires all objects to have a prefix or suffix to designate the application or schema with which they are associated. Select Apply Filter... from the database connection for the schema to invoke the dialog:

You can override the schema level filter using a filter at the object type level. The object filter dialog includes an Override Schema Filter option.

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:

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, which are discussed in the chapter on Extending SQL Developer.

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:

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

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

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.

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.

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 package DBMS_METADATA 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:

You can control the SQL output for tables by adjusting the parameters passed to the DBMS_METADATA package. This is done by using the SQL Developer preferences in the Tools | Preferences dialog, and by setting Database | ObjectViewer options. To illustrate this, deselect Show Schema, Show Storage, and select Show Constraints as Alter and then click on OK:

Refresh the SQL editor to display the updated output. Notice how the schema prefix "HR" is no longer displayed and the storage specifics are now excluded. This makes it a very useful script to reuse on other databases, or for other schemas. You may also like to create the tables with no constraints, add data, and then apply the constraints subsequently:

The easiest way to control the column order displayed in the data grid is to use drag-and-drop. Select the column header and drag it to a new position. Alternatively, you can select Columns... from the context menu on the column heading as shown in the following screenshot:

This invokes the Manage Columns dialog, which supports reordering of columns. The same dialog also supports the ability to selectively hide and show columns. Regardless of how you reorder or change the display of columns, the new column order remains in place when you close and reopen the display editor, as well as if you close SQL Developer. Along with the other display settings, this value is persisted until you actively change the setting or clear the settings by selecting Delete Persisted Settings... from the same context menu displayed in the earlier screenshot.

The column headings also provide a context menu to Auto-Fit selections. This allows you to resize the column based on the header or the contents of the column. There is also a Best Fit option, which uses a combination of these. Using Ctrl or Shift+select, you can select multiple columns and apply these conditions to all of the columns. An additional Fit Screen option is available to ensure all of the columns fit within the extent of the display area.

With the exception of the two column name-value pair displays, you can sort the records displayed by double-clicking on most of the column headings. This is true for all of the data grids throughout SQL Developer. Columns that allow for sorting display this icon to the left of the column header. You can also perform multiple column sorts by selecting Sort... from the column header context menu. The following screenshot displays the detail of placing two sort settings on the data grid:

SQL Developer 2.1 introduced column filters, which are like a WHERE clause for the display editors. Click on the column header, or select Filter Column from the context menu on the column header. Either action opens a floating menu of choices to restrict the column:

Filtered columns display a filtered symbol. To clear these column filters, invoke the filter menu again and select All..., or select Remove All Filters from the columns context menu.

Let's move down to the context menu available on the data grids. These are available throughout SQL Developer, wherever a data grid is displayed, and tend to vary slightly. For example, the reports data grids have an additional navigation menu and the updateable data grids have an additional option for duplicating rows. In the next section, we'll consider a few of the options available.

Highlighting data

The Filter/Highlight option is available for most data grids. In the following screenshot, we have used one of the data dictionary reports, which has a larger set of data available. The report here is the All Objects report under Data Dictionary Reports | All Objects.

The Find/Highlight dialog allows you to search for a string in any cell in the grid or in a particular column in the grid. Use the drop-down list to the left-hand side of the search field to select the column for the search. The default is all columns.

The Highlight option is illustrated in the previous screenshot. Here, we applied two searches and highlighted each independently. The persisted searches are tracked in the dialog. If these are not visible, expand the floating window. These highlights persist when you close the dialog, or indeed if you close the report or data grid. Remove the persistent highlights using the same window.

The context menu on any data grid includes:

While these are useful on any data grid, they are more useful for data grids where the number of columns displayed is unwieldy, or the number of records displayed is extensive.

The Single Record View... switches the display of a single record to a form layout. This allows you to see the contents of the cells more clearly as you are now able to scroll down through the column heading, and not across the page.

The Data and Flashback editors are slightly different to the read-only display editors in that they are related to instance data. The Data editor displays the table data in a grid. You can update, delete, or insert new data using the grid. The Flashback editor also displays the data in the table, and focuses on the changes made to the data.

In the next section, we'll look at the Data editor and focus on the different features available when working with this updatable data grid. To invoke an updatable data grid, select any table in the Connections navigator, and click on the Data editor.

Sorting and filtering data

Like the previously discussed data grids, the Data editor data grid has two sort options. The first option is on the column heading, where only one sort applies at a time and each sort replaces the last. The second option allows you to sort on multiple columns. To sort columns on multiple criteria, select the Sort... option just above the data grid. This allows you to sort on a selection of columns, which can be sorted in ascending or descending order.

Filters are restrictions on the displayed data. When you add a filter to the data grid, you are in effect adding a WHERE clause to the select statement. An example of adding a filter to the COUNTRIES data grid is COUNTRY_NAME LIKE 'A%' OR COUNTRY_NAME LIKE 'C%'. Each time you invoke the Data editor for a table, the full set of data is returned, that is, without any filters applied. However, any previous filters on the table are stored and you can invoke them from the Filter drop-down list.

The following screenshot displays the EMPLOYEES table with a multiple column sort applied, as indicated by the bold Sort... option. The Filter drop-down list indicates a selection of previous filters for the table:

To invoke the drop-down list, place the cursor in the filter field and start typing or use the down arrow.

To update any data item in the grid, select the item and start typing. As soon as you are in edit-mode, the Edit Value dialog button displays. Clicking on this button invokes an edit dialog, which is useful for larger text fields such as comments:

Working with LOBS in the data grid

For LOB columns, the value stored is not displayed in the data grid. Instead, the word (BLOB) displays. To see the value, such as the image stored, you can edit the value by selecting the Edit Value dialog button. In the following example, the image is stored in the database. To view the image select the Image checkbox. To use the data grid to add an image, or file to the database, click on Load and select the file. This is not saved to the database until you have committed the changes. Click on OK to close the dialog and then click on the Commit Changes button. You can now view the image or file by once again clicking on the (BLOB) field:

Using flashback

The Flashback display editor is displayed when you are connected to an Oracle Database 11g. To see the Flashback feature in action, select the Data editor for one of the HR tables, such as DEPARTMENTS. Modify a record and Commit the changes. Now, select the Flashback display editor. You should see Update in the Operation column. If it is not immediately apparent, double-click on the Operation column to sort the data. Select the updated record. The updated record is displayed in the Data editor below the records. Select the Undo SQL tab to view the query required to reinstate the original's re cord. The Undo SQL tab shows the SQL needed to restore the state for the whole transaction.

Note

To review the Undo SQL syntax in the Flashback editor, grant the schema the SELECT ANY TRANSACTION system privilege (for example, GRANT SELECT ANY TRANSACTION TO HR;).

Triggers, functions, procedures, and packages all have their own separate nodes in the Connections navigator. Prior to SQL Developer 2.1, a single-click on any object in these PL/SQL nodes behaved as for all other database objects, that is, a set of defining display editors opens. In order to edit a PL/SQL object, you needed to select the Edit... menu item, or click on the Edit button. Historically, conforming to that standard served only to confuse users wanting to open and edit PL/SQL code.

SQL Developer 2.1 changed this behavior by opening a single set of editors, with the first being the editable PL/SQL code:

In the screenshot just displayed, the initial Code editor is the editable PL/SQL code editor. This Code editor is included in the set of display editors for the selected procedure. This differs from earlier product releases, where the code editor was a separate window.

Very often, you may have the connection details for one schema that has access to objects in another schema. One of the most frequently asked questions on the SQL Developer forum is about viewing objects that the schema does not own.

Synonyms and filters

You can expand and work with objects you have access to in the Other Users node. You can also create synonyms for these objects and then, using Filters, display the objects within each respective object type node in your connection.

Consider the following example. The HR schema has access to query the OE's CUSTOMERS table. HR can query the table, using SELECT * from OE.CUSTOMERS;. Now, create a synonym using, CREATE SYNONYM CUSTOMERS FOR OE.CUSTOMERS;. This means the HR schema can write the queries without having to refer to the OE schema.

In SQL Developer, the synonym definition appears in the Synonyms node. The CUSTOMERS object will appear in the Tables node by setting the Include Synonyms option in the Filter dialog.

In the following screenshot, the tooltip shows that the CUSTOMERS table belongs to OE. The Tables node is also marked as Filtered:

The Recycle Bin was introduced to Oracle databases in Oracle Database 10g. SQL Developer displays the contents of the Recycle Bin in the Recycle Bin node. For supported databases, the Recycle Bin provides a listing of all objects deleted from the schema. The information displayed about dropped objects, includes the date the object was created and dropped, and whether the object can be undropped (reinstated) or purged. SQL Developer displays the original name of the object in the Connections navigator for ease of use. However, once dropped, the object has a new name. This allows you to drop objects and create new ones without the dropped object name blocking the action. In SQL Developer, displaying the old name makes it easier to decide what object you are dealing with, unless of course you repeatedly recreate and dropped the object.