Skip to main content

SQL Developer tips 1/3

H

Here are some tips for my fellow developers using SQL Developer. The web version is getting a lot of attention and getting better every day, however I use the desktop version and this article is about that. For anything about SQL Developer you should visit Jeff's blog, it is a bottomless well of wisdom.


Cleaning up

I like simple tools. I don't like clutter and too much options (like Toad), it is distracting. So lets close the panels you don't need. Are you really using Reports panel? If not, close it, you will have more space for Objects Browser and panels you really use.

On the left panel I use Connections, Find Database Objects and SQL History. You can turn them on or off in View menu. I also use Bookmarks and Code Outline at the bottom, but just when I need that. At the bottom I use Compiler Log, Bookmarks, Code Outline, but I am also closing these when they are not needed.

There are more panels, you should explore options under the main menu View, you might find something handy.

Panels I have on left side (top):

  • Object Browser - must have to navigate between connections and objects
    • organize connections into folders, I use the structure client/project/env and under that I have connections to specific applications
    • use colors for connections, I use red on all production env, green on dev env, then I use other colors on my recent projects so I can quickly find them
    • use Apply Filter on connections (right click) to filter relevant object, I use this when I have multiple apps in the same schema, so I create multiple connections to the database, but use different filters for different apps
    • you can apply filters also on specific object category too (like Tables or Views)
    • you should hide object types you don't use in Settings - Database - Navigation Filter, I usually hide a lot of things here which I don't use
    • you can set the behavior of the object browser in Settings - Database - Object Browser, I always switch off Single Click thingy, this also allows you to use the powerful drag and drop feature
    • check also Settings - Database - Drag and Drop, I like to keep this at Object Name and Prompt on, then I can easily decide what will the app provide when I drag the object to my workheet
    • setup shortcut [ctrl+n] to open a new worksheet (and not the default New Gallery dialog)
    • setup shortcut [ctrl+shift+n] to open a new independent worksheet (to run multiple statement at the same time)
    • export/import connections by right clicking at the top on Oracle Connections to transfer connection between computers and colleagues
    • did you knew that you can export APEX apps from here?
  • Find Database Object - very handy
    • leave All Schemas unchecked, check All Objects and Code, now type your word into the field above and hit Go, and the results are clickable
  • DBA - handy when you need to do DBA stuff (I usually don't do that)
  • SQL History - when you need to recover your one time query
    • don't double click on your query, use Append button at the top to just append do query to your cursor position
    • set your limits in settings Environment - Local History
    • also increase Undo Level in settings Environment, I use 500 for Undo Level and 0 for Navigation Level

Panels I have on left side (bottom) sometimes (I am closing them often):

  • Log - when my compilation doesn't work
    • I keep this in the left bottom corner so it wont distract me when compilation goes wrong
    • after I fix the issues in the object I close this so I see more in Object Browser
  • Code Outline - when I am lost in a package
    • this is a nice overview when the code it too long, but I don't use this often
    • you can actually order package modules by right clicking the package and Order Members By - Location
  • Bookmarks - when I need to jump between places
    • this is really awesome feature, imagine that you are changing a procedure in a package, which is calling another procedure and a view and you need to change all of these, then you can actually bookmark these places and quickly go to these places, no scrolling, no searching, you just press a shortcut and you are there
    • there are two modes, either you mark interesting lines by [ctrl+shift+b] and jump in between them by [ctrl+b] or you can use the Bookmark panel
    • other mode is even more interesting, you can actually assign specific numbers [ctrl+shift+number] and jump to specific place by [ctrl+number]
    • you can even jump in between different files and you can save these bookmarks for ever (see Settings - Code Editor - Bookmarks), but I discard the bookmarks when I close the app
    • adjust bookmark shortcuts in Settings - Shortcuts - Navigate


You should also remove unused extensions (Tools - Features) to make the app faster.

Arrange/split windows into multiple columns or rows if your screen allows it. I like to have 2-3 columns on my wide screen, just drag and drop the tab where you want to have it, or right click on the tab and check the options. Also check Settings - Shortcuts - Window to see how you can easily navigate between opened tabs/files. To swicth between tabs you can use [ctrl+tab+tab] shortcut.

I use at least two SQL Developers, one for work, the other for my projects. So I can't accidentally connect to my work servers, I can also have a different startup scripts and mainly I can keep my tabs/files opened when switching between work and my projects. Disadvantage is that you have to sync your settings, shortcuts, snippets...

This is in my Startup script (Settings - Database), the SQL Developer will execute this for you on every new connection (but unfortunately not if you just reconnect):

SET SERVEROUTPUT    ON
SET TIMING          ON
SET SQLBLANKLINES   ON
SET DEFINE          OFF
--
ALTER SESSION SET NLS_NUMERIC_CHARACTERS    = '. '
ALTER SESSION SET NLS_DATE_FORMAT           = 'YYYY-MM-DD HH24:MI';

You can customize you NLS settings also in Settings - Database - NLS and Settings - Database - PL/SQL Compiler (my setting is All Enable, Severe Error, Level 2, Identifiers All, Drop Force).


As a homework, go through the Settings, check what it is available in Shortcuts and then close app to save changes.

In next part I will cover Editor.


Comments