PL/SQL Developer 10.0 - New Features

Below you can find a brief description of the most important new features in PL/SQL Developer 10.0.

Connection Pinning

In PL/SQL Developer 9.0 a window connection is fixed unless you explicitly select a different connection. In version 10.0 window connections can either be pinned or unpinned. When unpinned, the window connection will follow the main connection when the main connection is changed. When pinned, the window connection will remain the same when the main connection is changed. To view or change the pinning of the window connection, view or click on the button in the connection status panel:

Newly created windows can either be pinned or unpinned by default, depending on the new "Pin window connection by default" preference. By default this option is disabled so that windows will stay with the main connection. This is different from 9.0 behavior, but is less error prone and therefore preferred as a default setting.

Regardless of this preference setting, if you explicitly change a window connection it will subsequently be pinned.
 

Edition Based Redefinition

In the Connection List you can now set a new "Edition" property to indicate that a connection is for a specific edition. For all sessions that are created for this connection definition a corresponding "alter session set edition" command will be executed. This makes it easy to use objects from different editions. By using the new "Compare to database source" function of the Program Window you can quickly compare objects from different editions:

In the connection description the edition is indicated in curly brackets:

You can use the same format for connection indicator colors, so that you can visually distinguish between different editions. The following example will use a green window color for R1 and blue for R2:

To start PL/SQL Developer with for a specific edition, you can use the new Edition=<name> parameter. For example:

plsqldev.exe userid=scott/tiger@chicago edition=R2

A similar syntax can be used when connecting for a specific edition from the Command Window:

SQL> connect scott/tiger@chicago edition=R2

Furthermore the SQL Window and Program Window now support editioning views and cross edition triggers.
 

Workspace Management

In the Connection List you can now set a new "Workspace" property to indicate that a connection is for a specific workspace (see above). For all sessions that are created for this connection definition a corresponding "DBMS_WM.GotoWorkspace" call will be made. This makes it easy to work in different workspaces. When using connection lists and connection indicators, the same format as described above for editions is used.

To start PL/SQL Developer with for a specific workspace, you can use the new Workspace=<name> parameter. For example:

plsqldev.exe userid=scott/tiger@chicago workspace=W12

A similar syntax can be used when connecting for a specific workspace from the Command Window

SQL> connect scott/tiger@chicago workspace=W12
 

Editor enhancements

The editor can now mark identifiers longer than 30 characters, so that you can quickly see when you are declaring a variable name or object name that is too long:


Parentheses are now highlighted when the cursor is either before or behind a parenthesis, so that you can immediately see that your parentheses match up after typing it:


A new "Paste from Host Language" function makes it easy to copy SQL statements from a host language like C++, Java, Basic or Pascal. The host language elements are removed and string syntax elements are converted:


A new "Web Search" function allows you to quickly search on the web for text selected in the editor:

You can either show the search results in your default Web Browser or in the internal HTML Window.

You can define your own web searches through the "Configure Web Searches" function:


Other editor enhancements include:

  • A new "Remove Trailing Blanks" function in the selection menu
  • Support for record type constants for the Code Assistant
  • The Code Assistant allows you to select all parameters for a function/procedure call and all columns for an insert statement through a special <all> item.
  • New editor preference "Hyperlink navigation to read-only window"
Program Window enhancements

You can now quickly compare the current source in the Program Window to the source of a database object or file. Right-click and select a compare function from the popup-menu:

These 4 functions will show a visual line-by-line difference between 2 program sources.

"Current file"
If you have opened a file from the file system and have made some changes, this function will show the differences between the file on disk and the Program Window. After saving the Program Window these two versions will always be the same.

"Current database source"
This function will show the differences between the object source in the database and the current editor. After compilation these two versions will always be the same.

"Other file"
This function allows you compare the current Program Window to any file. After selecting the file, the differences will be displayed.

"Other database object"
This function allows you compare the current editor to any object source in the database with the same name. After selecting the connection for the database object, the differences will be displayed.


The Program Window can now check for changes in the database object, so that you are immediately aware when someone else has changed the object you are working with:

You can view the differences between your version and the version in the database and you can choose to reload the changes into the Program Window.
 

SQL Window enhancements

The "Linked Query" function can now open Master/Detail SQL Windows that remain synchronized:

If you move to a new record in the master SQL Window, the detail SQL Window will automatically be refreshed after updating the master bind variable (:m_deptno). The Custom linked query allows you to build your own detail query based on any where clause with master bind variables.


The SQL Window and Report Window now support multi-select substitution variables that allow you execute queries with set expressions:

The selector can either display the actual values or a description of the values. The screenshot above shows the department names and includes the selected department numbers in the query.


Exporters can now also export directly to the clipboard:


Variable substitution can now be enabled/disabled from the status bar:


Other SQL Window enhancements include:

  • New function key: "Execute current statement"
  • New directory preference for Excel files from "Copy to Excel" function
  • Result Grid popup menu item "Copy Header" added
Debugger enhancements

When execution of the debug session is halted at a breakpoint or when you are stepping through executed lines of code, you can now press the new "Execute SQL in debugger session" button to execute a SQL statement at that point of program execution. You can for example alter the session state, insert/update/delete records, commit or rollback a transaction, and so on. You can also execute a select statement to view a result set within the debug session:


DBMS_Output can now be viewed while debugging:


Other Debugger enhancements include:

  • New preference: "Step over Package Specification code"
  • Enable All / Disable All added to Modify Breakpoint form
Table Definition Editor enhancements

When creating a table you will often add a foreign key constraint and its corresponding columns. Assume for example that you are creating a table "NOTES" that will have a foreign key constraint to the "EMP" table. On the columns page you can press the "Add foreign key columns" button and select the "EMP" table and the "EMP_PK" primary key constraint to which the foreign key constraint will refer:

After selecting the table and key, you can enter an optional column prefix for the foreign key column(s) and the name of the new foreign key constraint. The new foreign key columns will have the same name (+ prefix), data type and comments as the primary key columns of the referenced table:


The new "Triggers" tab page shows all triggers defined for the table:

You can add a trigger by pressing the "New trigger" button. This will invoke the corresponding template for creating a trigger, which will subsequently open in a Program Window. To delete a trigger, press the "Delete trigger" button. To enable or disable a trigger, click on the "Enabled" checkbox. Press the "View SQL" button to see the drop/enable/disable trigger statements and all other changes made to the table.

To view or edit the trigger source, double-click on the trigger in the list or press the "Edit trigger" button. This will open a Program Window for the selected trigger.


Other Table Definition Editor enhancements include:

  • Support for Oracle11g invisible indexes added
DBMS Scheduler enhancements

Support has been added for Job Email Notifications:

Email Notifications allow you to send an email when specific events occur for a job. You can optionally add a filter condition that needs to be met.


Support has been added for Chains:

Chains allow multiple dependent steps to be linked together through a set of rules. Steps can be of type "Program", "Event schedule", or "Inline event". You can subsequently create jobs to run these chains.


Support has been added for Event Schedules:


Other DBMS Scheduler enhancements include:

  • Support added for Job Event Raising
  • Support added for BySetpos clause
  • New preference: "Use timestamp format for scheduler objects"
  • Drag & drop actions for all scheduler object types now include a DDL option
  • The "View SQL" button now displays creation DDL when no changes were made to an object
Object Browser enhancements
  • New "Most recently used" preference added for user selection. This preserves the most recent user selection for each connection.
  • New "Always show public database links" preference added. When disabled only private database links owned by the selected user are shown. When enabled public database links are shown as well.
  • Chains folder added
Text Importer enhancements

A new "Filter" option has been added that allows you to filter records from the file with any valid SQL expression:


 

Toolbar enhancements
  • You can now create and modify multiple toolbars for all PL/SQL Developer functions.
  • Toolbars can be docked or can float.
  • You can now add submenus to a toolbar.
  • A new customization form allows you to tailor the toolbars to you needs.


 

General IDE enhancements
  • "Session > Set Password" menu item added, which sets the password in the database and in the connection definitions.
  • Setting your password through "alter user <name> identified by <password>" now also updates the connections definitions.
  • "Session > Set Main Connection to <Current>" menu item added.
  • New Appearance preference: "Show window connection in application title".
  • Preferences will be synchronized between PL/SQL Developer instances (Tools, Documents, Reports, Object Browser Folders, Browser Filters, File Browser Folders).
  • Drag & Drop of a directory from the Windows Explorer will open all contained files in PL/SQL Developer.
  • Unused connections can now automatically be closed when a window is closed or the main connection is switched.
Command-line parameter enhancements
  • New InstantClient=<path> parameter.
  • New NLS_LANG=<language_territory.charset> parameter.
  • New TNS_ADMIN=<path> parameter.
  • New ENV:Name=<value> parameter (e.g ENV:NLS_DATE_FORMAT=DD-MM-YYYY).
  • Project=* parameter now allows you to select project from the file selector.