Exploring Oracle SQL Developer
by Przemyslaw Piotrowski
Published May 2012
Oracle’s flagship (and free) product for developing, managing and tuning databases has come a long way since its inception in 2006.
My experience with Oracle SQL Developer began in 2005, when the first early adopter release became available under Project Raptor codename. Upon reaching the 1.0 milestone in 2006, Project Raptor was re-branded Oracle SQL Developer and began receiving regular improvements. Today, Oracle SQL Developer, which is not only free but also supported for Oracle Database customers, in my opinion offers the best coverage of Oracle Database features compared to alternatives, including out-of-the-box support for Oracle Application Express, Edition Based Redefinition, RMAN, Job Scheduler, and Data Pump, to name just a few.
The current release as of this writing, 3.1, offers yet more enhancements. Read on to learn about my daily experience with the tool and how to get the most out of it.
Perhaps Oracle SQL Developer’s best, yet most overlooked, feature is its code editor (called Worksheet). As a committed command-line user, I tend to stay away from GUIs, but thanks to this feature I have in fact improved my productivity, especially when developing PL/SQL code and troubleshooting performance issues. My approach is to fill worksheet with a dozen queries and then switch between them and their results in a nonlinear fashion, which proves to be a really efficient way of working with the tool. This ability to move back to previous results, something largely unavailable in other tools, is what makes the command line so productive.
Figure 1: Worksheet feature with multiple tabs with results, explain plans and script output.
Release 3.1 has also improved the Autocompletion Insight feature, which not only completes schema and object names but also can come up with automatic GROUP BY clause that updates progressively as you add to or remove columns from a query. Autocompletion Insight can be invoked either manually with CTRL+space or automatically after a configurable pause.
To further assist code editing, as of 3.1 the embedded formatter supports 43 formatting options in seven different categories, allowing for precise control over the look of queries and PL/SQL blocks. It’s also worth noting that formatter settings can be easily moved across computers and also support the syntax of IBM DB2, Microsoft Access, SQL Server and Sybase.
Figure 2: SQL Formatter settings for Oracle SQL dialect.
I deal with hundreds of databases in my day job, so a standard set of SQL scripts is not always useful – rather, I frequently need to revisit previously entered statements. The SQL History feature - opened with the F8 key - provides a quick way to return to all previously run commands with instantaneous, find-as-you-type search and chronological execution order.
In Oracle SQL Developer, data grids show up as results of queries and also when displaying object properties. Having them everywhere in 3.1 brings several usability enhancements, from basic ones like filtering and sorting to complex search and highlight actions.
A contextual menu on grids presents a number of handy options for quickly creating new snippets or reports. Persistent highlights, dynamic filters, or publishing results directly to Oracle Express are a few popular options. Also, there are a number of format choices for exporting all results ranging from plain text and delimited formats, through HTML, INSERT or SQL*Loader format, to PDF and Microsoft Excel. The source query of any grid can be previewed via the Save Grid as Report operation.
Figure 3: Data dictionary grid view with persistent highlights, dynamic filters and custom sort.
Laying It Out
Oracle SQL Developer owes its bluish shade to the original platform from which it descended: Oracle JDeveloper. Thanks to its deep extensibility, the Java-based JDeveloper served as the base platform for many tools that are now part of the Oracle Fusion Middleware family, including Oracle SOA Suite, Oracle BPEL Process Manager, and Oracle Data Integrator. The implication for Oracle SQL Developer is that all the usability characteristics are derived.
For example, two small but important features, when combined, are the cornerstone of all my window arrangements within Oracle SQL Developer. Window-splitting and pinning can ultimately divide screen workspace to the last available pixel, allowing for comfortable work between results sets, object definitions, reports, views, and any other window type.
Pinning, sometimes referred to as “freezing”, can keep the window on screen without the risk of being reused by another open action. You can precisely set up the behavior of automatic pinning through preferences under Code Editor > Autopin PL/SQL editors, Database > Object Viewer > Automatically Freeze Object Viewer Windows and Database > Worksheet > Show query results in new tabs. When not set up for automatic mode, each window can be pinned and split manually and then laid out side by side. For scripts and queries each result can be pinned individually, causing results of the next query to open up in a new tab, numbered automatically.
Figure 4: Pinned windows arrangement with pop up switcher.
Windows are split by dragging tabs to screen edges, and there’s no limit within the desktop itself to how many windows can be aligned side by side. This can be a very effective way of working with multiple database objects without having to choose which single one to leave on screen. Double-clicking on a tab causes it to go full screen, and dragging the tab to the center of the window area attaches it to the tab group beneath.
PL - SQL - Model
There are some compelling reasons for Project Raptor having gotten the “Developer” as part of its name. First and foremost, it comes with a very intuitive code editor tightly integrated with the PL/SQL engine, something especially true with respect to the Oracle Database 11g profiler and debugger. Basically, coding with SQL Developer turns out to be a seamless experience like the one you would expect from a programming language IDE.
Figure 5: Debugging window and code editor with breakpoints set.
No true development can happen today without extensive unit testing, and Oracle SQL Developer addresses that requirement extensively via integration of user interface with the database-persistent unit-testing repository. Another essential aspect of programming workflow, code versioning, is an integral part of the tool and in addition to Subversion provides integration with a local file system. This means that every file you edit with SQL Developer is automatically versioned, enabling you to quickly grasp differences between consequent or distant edits and merge them as needed.
Figure 6: Versioning view with file history and graphical diff.
Having tried at least a dozen of data modeling tools, a single one in particular has caught my attention for a little longer and become my software of choice for all 3NF needs. SQL Developer Data Modeler was initially offered at around one third the price of a Standard Edition One license and shipped as a standalone program. However, it wasn’t long before Data Modeler became free and was also incorporated into Oracle SQL Developer itself (with release 3.0). While Modeler’s feature set is outside the scope of this article, few of my personal favorites include flawless forward- and reverse-engineering capabilities, naming and design rules for keeping models coherent (Tools > Preferences > Data Modeler > Naming Standards and Tools > Data Modeler > Design Rules), and the only auto-layout functionality that ever worked for me in an ERD tool.
Figure 7: Data Modeler reverse engineered relational model with auto layout.
Data Modeler is still available as a standalone tool, but with the bundle not even being 200MB to download, you may as well get the complete suite in one convenient package.
Developer versus DBA
Since its 3.0 release, Oracle SQL Developer has been earning its reputation as a versatile, all-purpose database tool. Good-old reports functionality has been vastly enhanced and as of 3.1 offers a packed built-in library that can be easily extended with custom reports.
Figure 8: Reports view with graphical report of waits over last hour.
A feature that most DBAs will consider priceless is deep compatibility with SQL*Plus, a unique characteristic among database tools. Support for SHOW, SET, or even PAUSE and ACCEPT is natural part of SQL Developer’s ScriptRunner component, which is used to render all database scripts on the worksheet. Release 3.1 brings many enhancements to this support: almost all commands have been implemented in Java and there is only a minimal gap before Oracle SQL Developer reaches full SQL*Plus compatibility. Thus there are no more roadblocks for those willing to switch from the timeless command line tool. Even though it is rather unlikely that Oracle SQL Developer will eventually replace its command line equivalent completely, it is equally true that I personally reach out to SQL*Plus less and less often.
An entire new window view called DBA is at the essence of usability enhancements targeted at admins who prefer to work with GUIs. That view can be a very time-effective way of looking at Resource Manager allocation plans, RMAN backups, or database security attributes, among other things.
Before release 3.0 you needed Change Management Pack to be able to leverage the database diff functionality (Tools > Database Diff). New releases have revamped this functionality completely and additional licensing is no longer required.
For those moving from MySQL, Microsoft SQL Server, Sybase Adaptive Server, Microsoft Access, Teradata or IBM DB2, Oracle SQL Developer offers a migration assistant to assist with precise, lossless transition to Oracle platform.
Tricks of the Trade
Getting along with Oracle SQL Developer for a couple of years certainly pays off, given how many features it offers behind the menus. Most of the less obvious ones that are absolute time-savers can be found in a single place: the shortcuts configuration screen (Tools > Preferences > Shortcut Keys). Lots of them remain unmapped by default while others simply lack menu presentation.
Figure 9: Shortcut keys preferences with incremental search and conflict detection field.
After reading this article and splitting windows to the limits, a convenient way to switch between them is to use CTRL+TAB combination, which displays a list of currently opened windows and cycles through them (similarly to an operating system task switcher.) If you grew accustomed to back and forward buttons in your Web browser, there’s no reason to lose this habit under Oracle SQL Developer, which uses ALT+LEFT and ALT+RIGHT, respectively, to move between successively opened views.
As much as I enjoy working with the Schema Browser tab, I find another feature ultimately quicker to use. With the View > Find DB Object window you can instantly look up any object and move to its definition without traversing object trees and schema drop-downs. Useful but somehow buried within the interface is the Compare Plans feature -- to use it, just open and pin two explain plans, then right-click on plan tab and choose Compare with …
The Code Editor is the window in which you would usually spent most of your time. Usability enhancements in this field are among my favorites and include instant type-ahead search with CTRL+E (with inverse equivalent under CTRL+SHIFT+E), ability to comment and uncomment code with only shortcut CTRL+/ (dash) and an amazing multi-clipboard that remembers everything you copy from Worksheet (and pastes it back with CTRL+SHIFT+V).
Figure 10: Type-ahead search (Incremental Find Forward) and multi-clipboard paste.
Oracle SQL Developer’s user community is flourishing. At the SQL Developer Exchange you can suggest, rate, and discuss the roadmap, share code, and learn about what else you could do with the tool. Its discussion forum remains one of the most active, providing rapid answers to most vague questions. For years Oracle SQL Developer has ranked among the top downloads on Oracle Technology Network. Also, there is a ton of demos and videos on Oracle Learning Library.
For those interested it might be just the time to give Oracle SQL Developer a shot. Rest assured that all time invested in learning its features pays off well in the long run and it can eventually become your tool of choice as well. Highly recommended.
Przemyslaw Piotrowski is an information technology specialist working with emerging technologies and dynamic, agile development environments. Having a strong IT background that includes administration, development and design, he finds many paths of software interoperability.