PL/SQL Developer 7.0 - New Features

Below you can find a brief description of the the most important new features in PL/SQL Developer 7.0. You can also download a PDF document describing these new features.
Diagram Window

The Diagram Window allows you to create a graphical representation of a selection of objects. This way you can easily visualize (a part of) the database objects of your application or project and their relations:

You can add all relevant object types to a diagram, and include the relations between them. Foreign key relations between tables are automatically included. For large diagrams you can add additional structure by including object groups.

A diagram can be used for documentation purposes, but can also serve as a workspace. Right-clicking on an object provides access to all object functions, and double-clicking on an object will invoke the default action for the object type.

Graph Window

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.

ODBC Importer

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:

Compare Table Data

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:

Test Manager

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.

Dockable Tools

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

Search Bar

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.

Program Window Enhancements

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.

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.

SQL Window Enhancements

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.

Command Window Enhancements

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.

Session Window Enhancements

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.

Editor Enhancements

The following enhancements have been made to the editors.

Global navigation
A global Navigate back and Navigate forward button have been added to the main toolbar. Whenever you move to an absolute location within the same editor or in a different editor, a navigation bookmark is added to which you can return later. This way you can quickly find your way back if a function takes you to a different location, such as a find, hyperlink navigation, opening a new window, and so on.

Global bookmark list
The Bookmark list item of the Edit menu will bring up an global hierarchical display of all editor bookmarks:

In this tree view you can see the windows, editors, and lines where a bookmark is located. Clicking on a bookmark will bring the window and editor to the front, will navigate to that bookmark. You can dock the bookmark list to make it accessible at all times without blocking any other windows.

Color marks
You can add color marks in an editor to highlight specific sections. Just select the text you want to mark and press the Color marker button on the toolbar. The background color of the selected text will change to the current marker color:

Selection submenu
Right-clicking on a selection in an editor will now include a Selection submenu, with the same items as the Selection submenu from the Edit main menu.

Grid Enhancements

The following enhancements have been made to the grids.

Object Grid Filters
For all functions that display an object grid (export user objects, compare table data, and so on) you can define an object filter by double-clicking on a column and entering the filter criteria for that column. You can use wildcard characters to match multiple characters (% or *) or a single character (_ or ?). For example, double-clicking on the Name column of an object grid and entering EMP% will display all objects that start with EMP.
After defining a filter you can save it by right-clicking on the grid and selecting Save Object Filter from the popup menu. To reload the object filter later, right-click on a grid and select Load Object Filter.

Find function
The standard Find function now also works on grid data, in exactly the same way as in an editor. Just press Ctrl-F or select Find from the Edit menu when the grid has the focus.

File Format Preferences

The following options are available on the Files > Format preference page:

The Linebreaks option controls how lines are terminated in text files. The can either be terminated in Windows style by CR/LF character pairs, or in Unix/Linux style by a single LF character. You can additionally specify that files are in Unix format on specific locations, and in Windows format otherwise.

The Export with smart quotes controls how values are exported in CSV format. When disabled, all values are enclosed in quotes. When enabled, only those values that require them are enclosed in quotes. This are values that contain comma’s or quotes.

Query Builder Plug-Ins

PL/SQL Developer 7.0 introduces a new Query Builder Plug-In interface for 3rd party query builders. With the release of PL/SQL Developer 7.0 we have a Plug-In available for Active Query Builder from Active Database Software, Ltd:

If a Query Builder Plug-In is installed, then the standard Query Builder function will invoke the Plug-In. Right-clicking on the Query Builder button allows you to select a query builder from a list of all installed query builders. The most recently used query builder will be invoked by default when you subsequently use the Query Builder function again.

Oracle File System (OFS)

The OFS allows you to store all your files (sources, scripts, reports, and so on) in the Oracle database. This has the benefit that both the database objects as the files that operate on these objects can be stored in the same database, and can be accessed by all users that have access to this database. Furthermore, it is guaranteed that the database objects and files are consistent, and are backed up and recovered in a consistent way.

Files can be saved and opened in the usual way, with a standard file open/save dialog with some extensions.

OFS Manager
Before anybody can use the OFS from within PL/SQL Developer, you have to use the OFS Manager to define an OFS Location Directory with one or more OFS Locations. For each location you need to install the OFS database objects:

The OFS Manager allows you to manage your OFS Location Directory, for which you can add locations, and which you can save in an OFS Location Directory file (.ldf). This file is used from within PL/SQL Developer to browse the directory and to connect to locations and browse the files: