PL/SQL Developer 7.0 - New Features

The Graph Window can be used to quickly visualize column data of a SQL Window or Report Window. Consider the following query:

To see a graph of the salaries of the department you can press the new Graph button. This will start the Graph Wizard, which allows you to quickly select the column for the X-axis, the column(s) for Y-axis and the graph style, after which the Graph Window is displayed:

The Graph Window includes a toolbar that allows you to edit, copy, print and export the graph.

The ODBC Importer tool allows you to import data from any ODBC data source into an Oracle table:

On the first page of the ODBC Importer you can select the ODBC data source and select a table or enter a query text, after which the Result Preview pane will be populated. After entering the source specifications, you can switch to the Data to Oracle tab page to select the destination table, to specify the column mapping, and to start the import:

After inserting, updating and deleting records in one or more tables during development, it may be useful to propagate these changes to the same table owned by a different user. This may be a propagation from a development to a test database, or to a production database, or to another project member.

First you have to select the table(s) you want to compare:

On the Options tab page you can specify how you want to compare the tables, and how to apply the changes:

After comparing the tables, you can view and apply the changes:

To perform regression testing for your Oracle stored program units you can use the Test Manager. It allows you to define a Test Set, which is a collection of Test Scripts with input variable values and required output. It additionally allows you to specify required performance:

For this example the Test Set consists of 3 Test Scripts. For the selected DeptName.tst script the p_empno variable will get the value 7499 on input, and after execution the result variable value must be SALES. Running a Test Set will quickly reveal if the tested program units still function correctly and/or with the required performance:

All failed Test Scripts will have a red indicator and will be placed at the top. In this case the DeptName(7499) script failed, because the specification required that the result is “SALES” instead of the actual “Sales” value.

Many tools are now dockable at the left, right, top or bottom of the work area:

The Search Bar is available from the Edit menu and can be displayed as a floating tool, or docked at the top or bottom of the work area. In its docked position, it can be permanently available to perform searches across multiple editors and multiple windows:

The search results will be highlighted in the editor(s), and you can press the up/down buttons to navigate the search results. Te dockable search list can be of additional help to navigate the search results:

In this tree view you can see the windows, editors, and lines where the search results are found.

The following enhancements have been made to the Program Window.

Naming conventions
You can define naming conventions that will be checked when a program unit is compiled, or when the Show Compiler Hints function is invoked. A hint will be displayed for each element in the program unit source that does not meet these naming conventions. The naming conventions list looks as follows:

For each naming convention you can define the element type (parameter, variable, and so on), the required prefix for the element type, the possible values for the first and subsequent characters (after the prefix), the required suffix, and a description for the hint that should be displayed when the naming convention is not met.

Highlight variables
If the Program Window preference Highlight variables is enabled, then all occurrences of the variable under the cursor will automatically be highlighted:

If the preference is disabled, you need to explicitly use the Find Matches function of the Edit menu.

Save window state preference
If the Program Window preference Save window state is enabled, then the following window state will be automatically be saved and restored for a file or database source: • Window position & size • Current editor • Cursor position • Code Contents size • Bookmarks • Color marks • Substitution variable values.

Substitution variables
If a program file contains one or more substitution variables (prefixed by an ampersand), then you can provide a value for these variables by pressing the & button at the lower left of the window. The substitution variables are replaced by their values in the PL/SQL source before it is sent to the server for compilation.

Refactoring
The refactoring function allows you to quickly reorganize your PL/SQL code. It works on the selected code, or – if no selection is made – on the current statement. Refactoring functions include • Rename item • Convert selection to procedure • Convert selection to local constant • Convert selection to global constant • Replace assignment with initialization.

Code Contents layout
The layout of the Code Contents are changed so that variables, constants, types, and exceptions are now placed in a separate folder instead of the root of the tree view.

The following enhancements have been made to the SQL Window.

Multiple statements
The SQL Window can now execute multiple statements simultaneously, and display all results on separate tab pages:

When switching between result tabs, the corresponding statement or PL/SQL Block will be highlighted in the SQL Editor. Statements that raised an error are marked with a red X on the corresponding tab. The error positions are marked with a red underscore in the SQL Editor.

Graph Window
You can create a Graph Window from the current result set by pressing the Graph button and following the wizard. To pre-select the data for the X and Y axis, you can select the columns in the grid. You can also select a range of cells or rows to limit the data.

Alternate grid row colors
The SQL Window can now display alternate rows with alternate colors, so that long rows can easily be identified:

The alternate color can be enabled or disabled through a preference, and you can also set the actual color. The null value cell color preference will take precedence over the alternate row color to indicate null values.

Grid column totals
By right-clicking on a result set you can display a column total by selecting one of the functions of the corresponding submenu. The total will be displayed in a bar below the grid:

When editing the result set, the totals will dynamically be updated.

Grid selection indicator
When selecting a range of cells, or when selecting rows or columns, the status bar will indicate the number of selected rows and columns (RxC).

Excel export
The Excel export function now adds the SQL Text on a second page of the excel sheet.

The Command Window now supports the _USER and _CONNECT_IDENTIFIER variables for SQL*Plus 10g compatibility.

The SET COLSEP command has been added to control the column separator. Use SET COLSEP OFF for an empty separator.

The COLUMN command has been extended to support the NEW_VALUE option, so that variable values can be set by executing a select statement.

The SET SPOOLDIRECTORY command can be used to specify the default location for SPOOL files. This only affects SPOOL commands where no absolute path is specified.

The following enhancements have been made to the Session Window.

Session Filters
You can define session filters to limit the sessions displayed and/or to define which columns you want to see and in which order. You can include/omit columns from the v$session table, or add additional column joined from other tables. At the top of the Session Window you can select which filter you want to use:

In this case the All sessions filter is used. To view or edit the filters, press the Define Session Queries button on the toolbar:

Here you can define the Session Filters on the first tab page. The Caption will be displayed in the filter selection list. The Query will be executed to populate the session list.

Concatenate hint
To concatenate the value(s) of multiple rows of a session detail query, you can use the /* concatenate */ hint in the SQL text. For example:

select sql_text from v$sqltext_with_newlines
where address = hextoraw(:sql_address)
and hash_value = :sql_hash_value
order by piece
/* concatenate */

The sql_text column of all rows returned by this query will be concatenated, and displayed as one value in the session detail grid.

CLOB and LONG columns
CLOB and LONG columns are now also supported in the session details. When a CLOB or LONG value consists of multiple lines, the height of the corresponding row will automatically be increased to show all lines.