The result grid of the SQL Window can be manipulated in various ways. Some cell types have special behavior, rows, columns, and cell ranges can be selected and printed, columns can be moved, rows can be sorted, you can switch to a single record view, and so on.
Recognizing null values
Null values are displayed as a cell with a light yellow background, so that you can quickly distinct them from a value with all blanks. It is also helpful to recognize null values for cells that do not display the value directly in the grid, such as longs and LOB’s. You can change the color of the null value cells through a preference (see chapter 16.19).
Viewing large data columns
The values of Long, Long Raw, CLOB, BLOB and BFILE columns are not displayed in the result grid.
Instead, they are simply displayed as <Long>, <Long Raw>. <CLOB>, <BLOB> and <BFILE>:
When you click on the cell button of such a column, the Large Data Editor is invoked, which allows you to view or edit the data in various formats. See chapter 19 for more details.
You can also click on the cell button of character columns to invoke the Large Data Editor. The cell button is only present if the column size is larger than 20 characters.
Viewing date columns
A date column has a cell button that displays a calendar with the current date highlighted. If the date also has a time fraction, you can view its value on the calendar as well:
Viewing timestamp columns
Timestamp columns are displayed in the format that is specified in the NLS_TIMESTAMP_FORMAT and NLS_TIMESTAMP_TZ_FORMAT registry settings of your Oracle Home.
Viewing XML data
There are various ways to store XML data in the database: as CLOB’s (Oracle8i), as XMLTYPE (Oracle9i), or even as Varchar2 or Long columns. In any case the Large Data Editor will recognize the XML format if it starts with the standard XML header, and will switch to the XML format. See chapter 19 for more details.
Viewing nested cursor columns
If you include a nested cursor in the field list of a select statement, the column value will initially be displayed as <Cursor>. Pressing the cell button will bring up a new SQL Window with a result grid with the cursor result set. This can be used to view simple nested queries. Note that each nested cursor value will implicitly result in an open cursor, so for large result sets you can easily run into the
OPEN_CURSORS limit and get “ORA-01000: maximum open cursors exceeded” errors.
Selecting columns, rows and cells
To select rows or columns in the result grid, just click on the row heading or column heading and drag the mouse pointer to highlight the selection:
A column selection can now be moved by releasing the mouse button, clicking on one of the selected column headings again, and dragging the selection to the new location.
To select a specific range of cells, move the mouse pointer over the left edge of a cell until its shape changes, press the mouse button, and drag the mouse to highlight the selection:
To select all rows and columns, press the upper left cell, or right-click on the grid and select the Select All item from the popup menu.
The highlighted selection can be copied or printed as usual.
Exporting data
There are several ways to export the data in the result set grid. After executing a select statement, you can select a range of cells as described above, right-click on it, and select the Export Results item from the popup menu. This will display a submenu where you can choose to export the data in CSV format (Comma Separated Values), TSV format (Tab Separated Values), HTML format, XML format, or SQL format. After selecting the format, you can specify the export file. The SQL format will generate a SQL file with insert statements for the queried table or view.
You can alternatively copy the selection to the clipboard by pressing Ctrl-C, or by right-clicking on the selection and selecting the Copy or Copy with Header item from the popup menu. You can subsequently paste this data in another application like a spreadsheet, word processor, an so on.
To quickly manipulate the result set information in Microsoft Excel, select the Copy to Excel item. This will open a new Excel instance, and all selected data will be copied.
Sorting rows
To sort the rows in a result grid, press the heading button of the column on which you want the rows to be sorted:
The rows will be sorted in ascending order, as indicated by the heading button. Pressing the heading button again will sort the rows in descending order. Pressing it a 3rd time will undo the sorting. Pressing the heading button in another column will sort the rows on this column, but will also use the previous sort column as the secondary sort column (indicated by the dot in the heading button). In the example above, the job column is the primary sort column, and the hiredate column is the secondary sort column.
Note that sorting is performed locally, and only for the rows that are already retrieved. If you retrieve additional rows after sorting the results, these new rows will be added at the end of the result grid, without any sorting. For large result sets, local sorting can take a long time. In this case it might be better to use an order by clause in the select statement and let the Oracle Server do the sorting.
Single Record View
If the result set contains many columns, it may be inconvenient that each record is displayed on a single line. You need to scroll back and forth to view related columns (though you can move the columns), and cannot view all the columns of the record at once.
By pressing the Single Record View on the grid toolbar, you can view a single record at a time:
Now each row displays a single column name and value. The Next Record and Previous Record on the grid toolbar can be used to navigate through the result set. To switch back to the Multi Record View, press the Single Record View button again.