Top 15 PL/SQL Developer Tweaks

SQL Developer is one of several integrated development environments (IDEs) that are available for Oracle. One of the things I like about it is how configurable it is - you can change almost anything, and with downloadable plug-ins such as Browser Extender you can add your own functionality. This article documents what I consider the best PL/SQL Developer tweaks.

1) Search for Preferences :

                                Navigation : Tools -> Preference

If you need help navigating to any of these preferences, simply start typing their names into the search box. It will show all the related Search Results.

2) Code Editor's Font

We need to write so many codes in the program. So the look and feel of the Editor also matters. SQL Developer has introduced thousands of new fonts in this version and one of the interesting among them is Droid fonts from Google, which is mobile friendly. Just choose your favorite font and size, to have better view and experience.

3) SQL History Limit

The number of statements you want saved is defaulted to 100. I think this is probably on the low side. It’s been super easy now to save your work in the SQL so bump it up to 500 if you want a few days more worth of queries in your ‘backup’.

4) Licensing

As much as we would like for everyone to be running Enterprise Edition with all the database pack goodies, many people are running lower licensed edition of the database. You can disable the Tuning and Diagnostics packs in SQL Developer to avoid accidentally using and licensing these features in database. Its better disabling the packs at the database level to protect yourself, but if it’s not possible then at least do it in SQL developer. The nice part is you can do so at the connection level rather than neutering SQL Developer completely. Thus, don’t forget to look this option at the connection level itself.

5) The “Single Click” Concept 

We always look for the options where we need to spend less time and have to move least of our limbs! The new “single click” feature of the SQL developer will open your objects with the first click. It’s interesting and fun for the new users, but can be irritating for professionals.

6) Query Results in New Tab

Wouldn't it be nice to keep around query results from different iterations of your query as you work through it? Enabling this feature will keep your query results open as you execute new queries. Mouse over the ‘Query Result’ labels to see the SQL statement used to populate that grid. Of course the more result sets you leave open, the more memory SQL Developer will need. So be sure to close them when you’re finished. 

Once you will perform and apply above setting then next time onward you can see all your query results in separate tabs as shown below : 

7) Improved Debugging Option

This new feature with “Step-Over” option has eased the work when in debugging mode. You can now set this option and it will stop the execution at the first line. This will allow you to run the debug session at the breakpoints. f you leave it at the default, starting a debug session will appear to ‘not work.’ This is because SQL Developer will enter debug mode and run until it finds a breakpoint, breaking condition, or exception.

8) External Editors

Opening the files in the SQL Editor is troublesome. In SQL Developer we can open all our files using an External Editor? Yes, now we can.  You simply need to define file type and set the path in the External Editor under the “Debugger” option.

9) Shortcut Keys

Short cut keys are a developer’s best friend! One of my favorite short key is 'Ctrl+F7' which i frequently use to format my code. There’s one amazing feature introduced by Oracle in the SQL Developer 4.1.1 version which allows you to set shortcut keys according to your own need. You can customize them to your liking. Just mind the conflicts.

10) Hide the Things You don’t Use

Most of us don’t use every feature in Oracle. So a clean and empty dashboard to work can make us feel happy, right? So why don’t you simply conceal database object types in connection trees you don’t want to work with? Check-mark the “Enable Navigation Tree Filtering” option and you can select the trees you want to hide. Less scrolling, less searching, more Happy.

11) NLS Parameters

This might be the most important set of preferences in Oracle SQL Developer. The settings on this page directly impact how your queries run and the results they return. Here you can set all your preferences like language, territory, date & time format, currency, etc.

12) Default Connection Script

Now you can set your default global startup connection script in the database for all the groups of files in the output.

13) Finding Database Object

You can find various types of objects (tables, columns, declarations within functions or procedures, and so on) associated with an Oracle database connection and open editing panes to work with those objects. Click the Search icon on the left to display the Find Database Object window, where you can specify a connection name and search criteria. To display all criteria, click More. (For a connection to an Oracle Database Release 11.1 or later connection, clicking More causes SQL Developer to use PL/Scope, which enables you to search for the usage, for example, for the variable declarations.)

The following figure shows part of the Find Database Objects pane with results from a search for Table definitions associated with a connection named AppsTest where the table name starts with XX_EMP.

14) Local History Backup

The Local History pane controls whether information about editing operations on files opened within SQL Developer is kept. If local history is enabled, you can specify how long information is retained and the maximum number of revisions for each file.

15) Tips of the Day

For English locales, you can display a random suggestion for effective use of SQL Developer by clicking Help, then Tip of the Day. The tip window is also displayed automatically when you start SQL Developer, unless you disable the Show tips at startup option in the tip window.