The main interfaces in SQL Developer are:
- Connection Manager and Object Navigator
- SQL Worksheet
- Code Editor
SQL Developer also includes these features:
- A GUI interface to create and edit each database object
- Templates ("snippets")
- An excellent help interface, well-integrated into tool
- Enhancements to SQL Worksheet, including an Explain Plan window, a DBMS Output window, an OWA Output window, and a Results Grid window
- Data export
- Reports, including preset and customizable reports
- Run manager
Let's examine these features in turn.
Managing Connections and Objects with the Connection Manager
The Connection Manager pane (see Figure 1
), which serves as the starting point for both connection management and object management, defaults to a pane on the left-hand side. However, as with all other panes in the SQL Developer workspace, you can drag and dock the Connection Manager to wherever you find it most convenient. Like the connection pane in commercial tools such as Aqua Data Studio, each connection expands to an object tree.
|Figure 1. Connection Manager, Starting Point for Connection and Object Management|
To create a new connection, left-click on the "Connections" heading in the Connections pane and choose New Database Connection. The dialog box that pops up provides you with three options for specifying connections:
- Basic: You get just a hostname, port, and SID/Service name.
- TNS Alias: SQL Developer will import TNS aliases from your local tnsnames.ora, if it exists; or you can enter a TNS connect string yourself.
- JDBC: Enter a custom JDBC URL.
Passwords for all of these methods are stored encrypted. I had some difficulties using my local TNS alias for aliases with complex TNS connection stringsin my case, a string that included load balancing between RAC nodes. Entering the TNS connection string manually didn't solve the problem. However, I was able to enter it as a custom JDBC URL.
Once you've created one or more connections, you can open them simply by clicking on the "+" next to the connection name. SQL Developer opens the connection and drops down an object tree for all objects visible with that connection.
If you open two SQL Worksheets to the same connection, they share a sessionunlike tools such as PL/SQL developer, which you can configure to open multiple connections. If you need separate SQL worksheets to have separate connections, create two named connections that connect to the same user@db. Open both connections, one SQL Worksheet per connection.
In addition to managing connections, the Connection Manager is the central interface for SQL Developer's functionality. For instance, the menu has no "create table" option. To create a table, open the connection, click on the "+" beside the connection to drop down the object tree, right-click on the "Table" icon, and choose "Create Table."
SQL Developer provides object modification and creation GUI dialogs for each type of object in the tree. The dialogs are full-featured and easy to use.
In addition, the Connection Manager provides full-featured object inspection for each object type in the tree. For example, clicking on a table opens a window with tabs for the table's columns, data, indexes, constraints, grants, statistics, column statistics, triggers, dependencies, other table properties, and table creation DDL (see Figure 2).
|Figure 2. Click on a Table Name in the Connection Manager to See Table Information and Data|
By offering a wealth of features beyond simple SQL execution and command history, Oracle has brought SQL Worksheet in line with SQL editors in other tools, such as Free Toad. The new features include:
- Syntax highlighting
- Auto-complete suggestions via Code Insight
- SQL formatting (Ctrl-B)
- Ability to split the worksheet into multiple panes that edit the same document (useful when editing a long script)
- Visual Explain Plan
- SQL History: pops in a new window, shows info on which connection last ran the statement and when, and is searchable.
- Multiple result panes for each worksheet: a results grid, a script output pane (for output formatted the way SQL*Plus would format it), a DBMS Output pane, and an OWA output tab that Web developers will find useful
Figure 3 shows a screenshot of SQL Developer's SQL Worksheet.
Users with policies or non-standard date formats will run into a couple of annoyances. SQL Developer has no "glogin.sql" equivalent to run ALTER SESSION SET... commands at each login. Neither does it have options in the Preferences pane to set commonly set session parameters such as
NLS_DATE_FORMAT. Therefore, you must re-run policy statements (such as
fnd_global.apps_initialize) and date formats (such as
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY') every time you open SQL Developer or reconnect a connection.