racle recently released version 1.0 of SQL Developer, its free PL/SQL development IDE. Formerly known as Project Raptor, Oracle SQL Developer is based on the same IDE platform as JDeveloper (known internally at Oracle as the “Fusion Client Platform“), and it feels like a cross between the SQL Worksheet tool and a stripped-down version of JDeveloper. In fact, Oracle will stop supporting its standalone SQL Worksheet client in favor of SQL Developer, and post-10.1.3 JDeveloper releases will include SQL Developer functionality.
Although it is a new tool, SQL Developer’s use of a mature IDE platform gives it a stability, usability, and attention to detail that I’ve rarely found in version 1.0 releases. While test driving the tool for this review, I encountered fewer problems with SQL Developer hangs and crashes than I’ve had with many expensive commercial products in this category?despite an array of capabilities including:
- All panes are dockable pretty much anywhere you would want them.
- Editing panes can be split.
- Tabs can be grouped however you like.
- The help is well integrated into the application.
True, SQL Developer is not as full-featured as some commercial tools like PL/SQL Developer and Toad. However, with a SQL worksheet, code editor, object creation and editing GUIs, and debugger, it’s complete enough to use as the development platform for a complex PL/SQL project. Besides, it’s only the 1.0 release and it’s free.
What SQL Developer may lack in features, it makes up for with one advantage over many of its competitors: it is multi-platform. Since it’s written in Java, the same tool runs on Windows, Linux, and Mac OS X. Mac OS X particularly is underserved in this market, and SQL Developer could well displace Aqua Data Studio as the best Oracle development tool for that operating system?for those with sufficiently powerful machines to run it.
Installation in an Instant
Installing SQL Developer couldn’t be easier. Make sure that your PC meets the minimum requirements for your platform (see the “Oracle SQL Developer Installation Guide“), and then follow these simple steps:
- Download the software from the Oracle SQL Developer homepage. Non-Windows users who don’t already have the appropriate JDK on their machines will need to download and install the JDK first.
- Extract the .zip to the desired location.
- Open the “sqldeveloper” folder and run “sqldeveloper.”
An existing oracle_home is NOT required. You don’t need to have any other Oracle products?including Oracle client software?installed on your machine. You don’t need to set any environment variables or anything in the registry either. Simply download, unzip, click, and run.
The Features
The main interfaces in SQL Developer are:
- Connection Manager and Object Navigator
- SQL Worksheet
- Debugger
- 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 strings?in 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 session?unlike 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 |
SQL Worksheet
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. SQL Developer’s SQL Worksheet |
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.
Exporting Your Resultset with Data Export
SQL Developer’s Data Export lets you export any resultset to CSV, XML, text, an INSERT statement, or a SQL*Loader control file with inline data. The last two formats aren’t shared by many other tools?and working DBAs will find them very useful.
To export a resultset, simply right-click in the results grid and select “Export” from the context menu. After you choose your format, SQL Developer pops up a dialog box that lets you fine-tune your selection. You can export to Clipboard instead of a file, which is particularly handy for generating INSERT or SQL*Loader statements.
One particularly nice feature is the ability to export the whole resultset, a cell, a column, or a row?whatever you select. You can also choose which columns to export, or even rewrite the query, in the Export dialog box.
However, be careful: SQL Developer exports exactly what you see. If your NLS_DATE_SETTING defaults to ‘MON-RR’, then the Export statement pulls dates in MON-RR format, ignoring any greater precision stored in the database. This could be a bit of a pitfall if you’re using Export to generate an INSERT statement or a SQL*Loader control file. The statement Export generates will reflect your NLS_DATE_SETTING, not the full date in the database. Likewise, be mindful of your session NLS_TIMESTAMP_FORMAT and NLS_TIMESTAMP_TZ_FORMAT settings when exporting TIMESTAMP WITH TIME ZONE data.
Code Editor
To open a Code Editor window, navigate to a package spec, package body, procedure, or function in the Object Tree, and then either click “Open” in the context menu (right-click), or click the “Edit” icon in the view window to the right of the Connection Manager.
Like SQL Worksheet, Code Editor has syntax highlighting, SQL formatting, split panes, and auto-completion. Its other nice features include:
- Expand and collapse code sections
- Code Insight, which offers auto-complete suggestions, as in SQL Worksheet, and related tools: Ctrl-space activates completion insight; ctrl-shift-space gets you parameter insight; ctrl-alt-space gets you “smart” completion insight
All the keyboard shortcuts in SQL Developer are user-configurable via the Preferences dialog, under “Accelerators.” SQL Developer also comes with a variety of useful keyboard shortcut templates, such as “Emacs”, which provides keyboard shortcuts for common Emacs operations (e.g., Ctrl-Y for Yank and Meta-Y for Pop). You can change these under “Accelerators” as well.
Editing Saved Code Is a Little Ugly
Code Editor saves package specs and bodies as separate files, and I had difficulty getting it to recognize specs and bodies I’d saved as code files?they opened in SQL Worksheet instead, or opened in Code Editor without syntax highlighting or the ability to compile. As a workaround, I ran the code in SQL Worksheet to create it, then selected the package in the Connection Manager to open it in the Code Editor. It works, but it’s not pretty. I hope future releases of Raptor will make it easier to edit saved code.
Snippets
Both the SQL Worksheet and the Code Editor can make use of the Snippets tool. Snippets is a library of common built-in functions, optimizer hints, and PL/SQL constructs, like the Templates pane in SQL Developer. To use Snippets, simply select a code snippet and drag it into your editing window, or double-click to have it appear at the cursor.
Reports
The Reports tool is essentially a set of hierarchically organized queries that you can run under any of your connections. These queries are organized into two folders: Data Dictionary and User Defined.
Data Dictionary reports are a set of Oracle-supplied queries, neatly organized into folders, that provide basic information from the data dictionary (V$_ and DBA_ views) in categories such as Top SQL, Sessions, Jobs, Objects, and so on. To run a Data Dictionary report, double-click on the report name. SQL Developer prompts you for the connection and for any (optional) report parameters. The query’s results open in a new window that’s essentially an oversized results grid.
In addition to the Data Dictionary reports, the Reports tool has a folder for user-defined reports. You can supply any query you want (e.g., custom DBA queries, ASH queries, queries on your application’s log and session tables, etc.). You can group the reports into folders and run a custom report just as you would run a Data Dictionary report.
Debugger
SQL Developer’s debugger covers all the basics?breakpoints, watches, step over, step into, etc.?and works well, but it is a little slow on each step. It’s very functional and sure to be loved by developers who have struggled with the debuggers in other tools. Its nice features include the “Data” and “Smart Data” tabs, which show the current values of local variables without requiring you to set watches (see Figure 4).
 | |
| Figure 4. SQL Developer’s Debugger Layout |
Like JDeveloper, SQL Developer remote debugging uses DBMS_DEBUG_JDWP, a server-side package based on Sun’s Java Debug Wire Protocol. (It’s a little-known fact that you can also use JDeveloper to debug PL/SQL that executes both locally and remotely, using the same mechanism.)
DBMS_DEBUG_JDWP runs in the remote database and establishes a direct connection back to your machine, which means it needs to be configured if you’re using a firewall or VPN (see Sidebar 1. Using Debugger with a Firewall or VPN).
To get started with the Debugger, right-click on a package, function, or procedure in the Connection Manager and choose Debug. (Note: The Debugger provides more information if you choose a package, procedure, or function that’s been compiled for debug. To compile an object for debug, right-click on the object in the Connection Manager and choose “Compile for Debug”.)
SQL Developer pops up a dialog box that suggests an anonymous PL/SQL block you can use to debug your procedure. For example, to debug the procedure hello_world(), SQL Developer would suggest the following block:
BEGIN HELLO_WORLD() ;END;You can edit this block as you please?for example, to set parameters. Once you click OK, the debugger will establish the JDWP connection between the remote database and your machine (refer back to Sidebar 1. Using Debugger with a Firewall or VPN if you have trouble with this step). Once the connection has been established, you’ll see the debugging layout shown in Figure 4.
Note: According to a forum post by Kris Rice, one of SQL Developer’s creators, the debugger doesn’t work against databases on VMS. This issue will be addressed in a future release.
Help Is a Feature in Itself
The Help function deserves mention as a feature because, first, it’s so well integrated into SQL Developer, and second, it goes beyond SQL Developer help to provide what’s essentially a basic Oracle tutorial. In addition to SQL Developer help, Help chapters cover the SQL basics, including Oracle object management basics, PL/SQL basics, triggers, SQL*Plus, which can be launched from SQL Developer, and an overview of globalization support.
For support beyond the Help file, you have two choices. If you’re using SQL Developer to access databases running on licensed Oracle software, you can post support requests on MetaLink for SQL Developer. If you’re running a development or evaluation copy of the database software, or running Oracle’s free XE database, turn to the SQL Developer forum on OTN for support. The Project Raptor team monitors the form and responds quickly to most posted questions. The forum itself was been down intermittently during the writing of this article, perhaps due to SQL Developer’s popularity, but it usually came back up within a few hours.
Run Manager
The Run Manager keeps track of interruptible processes. Running queries don’t seem to show up, but debug sessions do. Click the STOP icon (red square) to stop a running debug session.
Extensions
Like JDeveloper, Oracle’s SQL Developer supports plug-ins, or “extensions.” To add an extension, download the file from the author’s site and unzip it into
- Two performance tuning and monitoring extensions are available at FourthElephant.com.
- GeoRaptor is a GIS extension for SQL Developer on SourceForge. GeoRaptor is at 5?Production/Stable.
Current SQL Developer Limitations
In addition to the usual handful of bugs that accompany a first release, this first version of SQL Developer has some big headaches for the visually impaired and for non-English speakers.
For the visually impaired, some known issues around accessibility with JAWS prevent SQL Developer from reading some information. See the README documentation for details.
For non-English-speaking users, Version 1.0 has very limited support for globalization. Currently, the ASCII character set is supported for entry and display. Non-ASCII characters can be displayed only if the following conditions are met (to quote the README):
- “The character belongs to the database or to the national character set, depending on the datatype of the value containing the character, and
- “The script to which the character belongs is supported by the JRE installation on which SQL Developer is running?for example, appropriate fonts are available?and
- “The script does not require complex rendering, e.g. it is written left-to-right with no ligatures or character reordering.”
The README goes on to note that complex or bidirectional scripts, like Arabic, Hebrew, or Devanagari, may work, but they are untested, not guaranteed, and may not align properly or appear in the right direction. “Full support for those languages is planned for a future release.”
Further, the SQL Developer interface in v. 1.0 is provided in English only, with more languages planned for a future release. The README also warns of a dozen known globalization bugs.
An Auspicious Debut
SQL Developer is a good tool with a lot of promise. Installation is a snap, and it’s supported, stable, and free. Its mature IDE platform presents a well-thought-out interface that’s very usable and very functional. I found SQL Developer to be stable with relatively few bugs outside the known issues, though some users may find the known issues insurmountable. I hope to see the few quirks that SQL Developer does have, such as the difficulty of editing or debugging code from a file, ironed out.
The debugger is slower stepping through code than I’d like, and SQL Developer doesn’t have all the functionality of the most popular commercial tools. That being said, for a version 1.0 release of a free tool, it exceeded my expectations. And I’m eager to see it mature.
