Working with Data Grids using Oracle SQL Developer 3.0

<Do not delete this text because it is a placeholder for the generated list of "main" topics when run in a browser>

Purpose

In this tutorial, you learn to work with Data Grid using Oracle SQL Developer 3.0.

Time to Complete

Approximately 20 minutes.

Overview

Oracle SQL Developer is a free graphical tool that enhances productivity and simplifies database development tasks. Using SQL Developer, users can browse database objects, run SQL statements, edit and debug PL/SQL statements and run reports, whether provided or created.

Default connectivity to the database is through the JDBC Thin driver, so no Oracle Home is required. To install SQL Developer, simply unzip the downloaded file. SQL Developer users can connect to any supported Oracle Database.

Oracle SQL Developer 3.0 introduces a DBA Navigator and provides support for DBMS Scheduling, to name only two of many features in the latest release; other features include a new Query Builder, SQL Tuning Advisor, and Explain Plan Diff utility. For a complete list of new features, click here.

This tutorial highlights using Data Grid in SQL Developer 3.0.

Software and Hardware Requirements

The following is a list of software requirements:

Prerequisites

Before starting this tutorial, you should:

.

Install Oracle SQL Developer 3.0 from OTN. Follow the release notes here.

.

Install Oracle Database 11g with the Sample Schemas.

.

Unlock the HR user. Login to SQL Developer as the SYS user and execute the following command:
alter user hr identified by hr account unlock;

Creating a Database Connection

Note: If you already have a database connection, you do not need to perform the following steps, instead, you can move to the Controlling column display section.

.

If you installed the SQL Developer icon on your desktop, click the icon to start your SQL Developer and move to Step 3. If you do not have the icon located on your desktop, perform the following steps to create a shortcut to launch SQL Developer 3.0 directly from your desktop.

Open the directory where the SQL Developer 3.0 is located, right-click sqldeveloper.exe (on Windows) or sqldeveloper.sh (on Linux) and select Send to > Desktop (create shortcut).

 

.

On the desktop, you will find an icon named Shortcut to sqldeveloper.exe. Double-click the icon to open SQL Developer 3.0.

Note: To rename it, select the icon and then press F2 and enter a new name.

 

.

In the Connections tab, right-click Connections and select New Connection.

Note: If this tab is not visible, select View > Connections.

 

.

Enter the following and click Test:

Connection Name: HR_ORCL
Username: hr
Password: <your_password> (Select Save Password checkbox)
Hostname: localhost
Port: 1521
SID: <your_SID>

 

.

Check for the status of the connection on the left-bottom side (above the Help button). It should read Success. Click Save. Then click Connect.

 

.

When a connection is created, a SQL Worksheet is opened automatically. The SQL Worksheet allows you to execute SQL statements against the connection you just created.

 

Controlling Column Display

In this topic, you control the column display in the data grid (or, display editor) for the EMPLOYEES table. You can selectively hide and show columns, determine the order of the column display, filter columns, highlight data values, and save the data grid in a report.

.

Select the EMPLOYEES table from the Connections Navigator. The EMPLOYEES data grid opens in a new tab.

 

.

To invoke the context menu, right-click on any of the column headers.

 

.

Select Columns....

 

.

A Manage Columns dialog opens, which allows you to reorder and hide or show the columns. To place the COLUMN_ID column after the COLUMN_NAME column, select COLUMN_ID and click the Up button three times.

 

.

To hide the NULLABLE column, select NULLABLE and click the Remove button.

 

.

Similarly, to hide the COMMENTS column, select COMMENTS and click the Remove button.

 

.

Click OK to apply the changes.

 

.

The changes you made are reflected on the data grid.

 

Sorting Colums


.

From the EMPLOYEES data grid, click the Data tab.

 

.

Invoke the context menu again by right-clicking any of the column headers.

 

.

Select Sort....

Note: Columns can also be sorted by double-clicking the sortable column headers. (Column header with this icon are sortable). However, with this method, you can perform only single column sort.

 

.

In the Sort Columns dialog, you can perform single or multiple column sorts. Confirm that the EMPLOYEE_ID column is selected and click to move it from the Available Columns list to the Selected Columns list.

 

.

To also sort by the HIRE_DATE, move it from the Available Columns list to the Selected Columns list by clicking .

 

.

To change the default Ascending order sort, select the Descending radio button. (Make sure that you have the HIRE_DATE highlighted in the Selected Columns list.)

 

.

Click OK to sort by the two selected columns.

 

.

The resulting output sorts the column names in ascending order (alphabetically) and the data type is sorted in descending order. The blue up and down arrows on the sorted columns represent the ascending and descending sorts. The numbers 1 and 2 signify the order of the sort, meaning, first sort by the column name, second sort is on the data type.

 

Filtering Columns

In this section, you will filter your columns based on specific criteria. For example, you might have a table that has a lot of constraints, and you only want to view the CHECK type of constraints. You can filter this information.

.

From the EMPLOYEES data grid, click the Constraints tab.

This will show you all of the constraints on the EMPLOYEES table, as well as information about the constraints.

 

.

To apply a filter on the CONSTRAINT_TYPE column, right-click the CONSTRAINT_TYPE column header to bring up a context submenu.

Select the Filter Column option.

 

.

A filter popup opens. To display only Check constraints, select Check and press the Enter key, or double-click Check.

 

.

The CONSTRAINT_TYPE column is filtered to display only the Check constraints. A filter icon is displayed on the column header of the filtered column.

 

.

To remove the filter, right-click any of the column headers and select Remove All Filters.

 

.

You have cleared the applied Check filter.

 

Using the Find/Highlight Option

With the Find/Highlight option, you can find data that fulfills a search criteria and you can highlight it to show attention to it.

.

From the EMPLOYEES data grid, click the Data tab.

 

.

Right-click any of the data cells to invoke a context menu.

 

.

Select Find/Highlight...

 

.

A Find / Highlight popup opens. You can search for a string in any cell of the grid or in a particular column. To search for names starting with 'sa', enter sa in the textbox and select the Starts With checkbox. To ignore the casing, select the Ignore Case checkbox.

 

.

Click the Find next arrow to the right of the textbox to start the search.

 

.

The search returns 40 results. This includes the SA_MAN and SA_REP values in the JOB_ID column as the search option is for all columns by default.

 

.

To search in the FIRST_NAME column only, click the Find icon to the left of the textbox and select FIRST_NAME.

 

.

Click the Find next arrow to start the search again.

 

.

The search now returns 3 results. Click the Find next arrow to see all the values.

 

.

You can highlight the rows that contain these values. Select the Highlight radio button, the Highlight Row checkbox and click the Find next arrow.

 

.

Scroll to see the highlighted rows.

 

.

You can search for different values and highlight rows with different colors. To search and highlight rows with manager ID 101, click the Find icon to the left of the textbox and select MANAGER_ID from the drop-down list.

 

.

Enter 101 in the textbox and select a pink color.

 

.

Select the Persist Highlight checkbox and click the Find next arrow.

 

.

Scroll to see the highlighted rows.

Note: The search criteria is now listed under Persisted Highlights.

 

.

To highlight rows with a salary of 3000, click the Find icon to the left of the textbox and select SALARY from the drop-down list.

 

.

Enter 3000 in the textbox, select a yellow color, and click the Find next arrow.

 

.

The rows are highlighted in yellow. To disable the manager_id= 101 (pink) highlight, deselect the checkbox next to 101 under Persisted Highlights.

 

.

The pink highlight is removed. To delete the salary = 3000 (yellow) highlight, click the red cross next to 3000 under Persisted Highlights (use the scroll in the popup to see the yellow highlight).

Note: You can resize the Find / Highlight window to view all the persisted highlights.

 

.

The yellow highlight is removed and deleted from the Persisted Highlights section.

 

.

To close the Find / Highlight popup, click the close .

 

Saving the Grid Query as a Report


.

You can save the SQL query, which produced the grid, as a User Defined Report. Right-click any of the data grid cells to invoke the context menu.

 

.

Select Save Grid as Report....

 

.

A Create Report dialog opens. Enter Employees List for name and click Apply.

 

.

To view the report, click the Reports navigator.

 

.

Expand the User Defined Reports node.

 

.

The created report is listed. To run the report, select Employees List.

 

.

Confirm that the connection is HR_ORCL and click OK.

 

.

The report data is displayed.

 

Summary

In this tutorial, you have learned how to:

Resources

Credits

Hardware and Software Engineered to Work Together About Oracle |Oracle and Sun | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Your Privacy Rights