Top 10 Preferences to Tweak when using SQL Developer

thatjeffsmith SQL Developer 181 Comments

Tell Others About This Story:

Updated June 2015, current for version 4.1
There are too many options in our every day lives. We love asking for more options, but I often find myself being overwhelmed by the number of questions I have to answer before I can accomplish very mundane tasks. Do you want your latte ‘skinny’ or your cappuccino ‘wet?’ Waffle House customers are probably familiar with ‘capped, covered, smothered, & covered’ routine. Do you want a receipt with that?

So what’s the sweet spot between the Spartan-like Notepad, where we have no options, and the current lineup of Republican Presidential candidates, where we have lots of options that don’t do anything? — OK, that was a political joke, and I should refrain from doing these as I am not a comedian.

When it comes to software, you want something that pretty much works out-of-the-box. Just starting it up and using it shouldn’t require several hours of digging through options or preferences to get it to do what you want it to do. I think we’re pretty close to this with SQL Developer. I can’t think of any ‘deal breaker’ option that would put a user down the wrong road right out of the gate. Here’s an example of something I would see as ‘bad’:

Preferences – Database – Advanced – Autocommit: if this were set to ‘True’ out of the box, I would most likely freak out and remove the product immediately. But, some crazy folks out there might think this is a good idea and demand they have this level of control. These people are also probably from an evil parallel dimension where this is necessary to prevent an obnoxious level of object locking, but that’s a different story.

So most things are good out of the box. But.

What options SHOULD you be aware of?

Here are ten that I think deserve your attention. If you need help navigating to any of these preferences, simply start typing their names into the search box. DO NOT HIT ENTER, this will close the preferences dialog.

You can find the Preferences Dialog under the Tools Menu

1. Code Editor – Fonts

You’re going to be typing a LOT. Be sure to set a font that’s pleasing to the eye. I’m a fan of fixed width fonts that support Unicode. There are thousands of font out there to choose from. One to check out are the Droid fonts from Google.

2. Database – 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 editions of the database. You can disable the Tuning and Diagnostic Packs in SQL Developer to avoid accidentally using and licensing these features in the database. I recommend disabling the packs at the database level to protect yourself, but if that’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.

3. Database – Worksheet – SQL History Limit

The number of statements you want saved is defaulted to 100. I think this is probably on the low side. Bump it up to 500 if you want a few days more worth of queries in your ‘backup.’

4. Database – Worksheet – Show Query Results in new tabs

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.

Keep a running list of all of your query output

5. Debugger – Start Debugging Option

Set this to ‘Step Over.’ This allows you to start and run a debug session sans breakpoints. If 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. If you have none, it will execute to the end of your program. The ‘Step Over’ option will tell SQL Developer to stop execution at line 1 auto-magically.

6. External Editors

Spend a few minutes and setup your default editors for your JPG, XLS, XML, and PDF files. Please. This will make working with BLOBs much more pleasant. A post all about that here

7. Shortcut Keys

So here’s a trick: sort by ‘Category’ and scroll down to the ‘Other’ category. I’ve found some of the best keyboard shortcuts are ‘undocumented.’ You’ll find a lot of the fun ones here. My particular favorite is ‘SQL History: Replace with Next.’ Of course if you do not like any of these shortcuts, you can customize them to your liking. Just mind the conflicts!

8. Open Object on Single Click

Good for n00bs, probably annoying to experienced users.

Turn this off. You can then open objects with a double click instead.

Turn this off. You can then open objects with a double click instead.

9. Hiding Database Object Types from your Connection Trees

You probably don’t work with EVERYTHING in Oracle – so set your trees to show just what you need. Less scrolling, less searching. More happy.

Tell SQL Developer what database objects you want to see when clicking around the tree.

Tell SQL Developer what database objects you want to see when clicking around the tree.

10. Connection Script Startup

Don’t care to toggle on DBMS_OUTPUT? Want your script page results in groups of 25? Setup your connection startup script (LOGIN.SQL)

The contents of this will be executed as each database connection is established.

The contents of this will be executed as each database connection is established.

Is that it?

Hardly. I could do an entire hour on just configuring your application preferences. But this is a Top 10 list, so it should give you an idea of what you can control via preferences.

If you need a bonus, check out this post on setting up the automatic code insight behavior. Watch the movie at the bottom of the post for insight on the preferences that affect that feature.

Related Posts

Tell Others About This Story:

Comments 181

  1. Hi Jeff,
    Is there any way to right click on an object in the left hand connections pane and debug it without having to open the object itself – then do it through the pop-up debug window and select from all the funcs/procs in a package?
    Its a feature in Allround Automations plsql tool that was amazingly handy and fast for investigating issues.
    Best Wishes,
    Simon

    1. thatjeffsmith Post
      Author
  2. Hi Jeff,
    thanks for this article.
    I have an additionaly question.
    Do you have an idea, how I can prevent SQL Developer to open the welcome page?
    thanks and kind regards
    Markus

    1. thatjeffsmith Post
      Author
  3. Is there a way to shrink the visual display of a table/object without having to constantly drag it to a smaller size? I have a table with about 30 fields. The table expands automatically (even though option is off), and runs off the page. I have to scroll down to find a field I want. I am hoping for a way that tables maybe stay at a certain length.

    1. thatjeffsmith Post
      Author
      1. Yes. When I drag tables into the query builder, they expand vertically. So I could have one table with 5 fields, and another with 30.

        If the required fields to link are not close on visual, I have to manually try and shrink the table visually. In other words, is there a way to have to resemble MS Access in the sense that the tables don’t expand vertically automatically?

      2. thatjeffsmith Post
        Author

        No, not that i know of. If we were to shrink the size of the table automatically though, you’d still have to scroll on the table to advance to the columns on the bottom. And aren’t most ID columns in a table near ‘the top?’

        I’m not saying it’s not a good idea, it’s just not in the product today.

  4. Would you please advice how to show the default startup page that is shown the first time the application started?
    I removed the checkbox then, but need it again.

    Thanks

    1. thatjeffsmith Post
      Author
  5. Hi Jeff,

    Can someone advise what am I doing wrong.
    I have following lines in login.sql file which is located on C:\app directory, I gave the complete path in “filename for connection startup script” but it doesnt work.
    SET SERVEROUTPUT ON
    BEGIN
    Dbms_Output.Put_Line(Systimestamp);
    end;
    /

    Any help in this regard is appreciated

    1. thatjeffsmith Post
      Author
      1. Hi Jeff, Thanks for quick response.
        I tried show login as you suggested but it doesnt show my file rather displays the following message in the script output.
        SP2-0158: UNKNOWN SHOW option “login”
        Assuming show login is wrong option, I tried show user command, it gives my username in script output tab.
        Sql developer version is 4.0.2.15 and build is 15.21.
        Thanks,

      2. thatjeffsmith Post
        Author
    1. thatjeffsmith Post
      Author
      1. Yes the colors, but also the indentations, formatting, etc. was hoping there was a way to import a scheme because the SQL Dev options aren’t easy to work with.

      2. thatjeffsmith Post
        Author

        Aren’t easy to work with – not sure what that means.

        We don’t offer a ‘Toad mode’ 1)because that would be weird, and 2)we think it’s ugly and overly complicated.

        With a little work you can pretty much make SQLDev look and feel any which way you’d like.

  6. Jeff, In an Oracle SQL Developer request on 2011. A request was made so that you can use the keyboard to go from the SQL Worksheet to the Query Results tab.

    Has this been actioned?

    1. thatjeffsmith Post
      Author
      1. Cancel the last request, it Alt+ the arrow keys.

        Thanks again.

        However, is there a keyboard keystroke to close the grid result tab?

      2. thatjeffsmith Post
        Author
  7. Hello,

    When Im working in plsql code for a long time, sql developer gets disconected from the database and when I try to recompile, the GUI gets freezed and nothing responds anymore. Sometimes when a little of lucky, disconnecting the VPN makes sql developer to respond again and I can continue my work, but many of the times I have to kill the process. I have seen this annoying behavior since version 3.x. Im using 4.2.0.16.260 now.

    Does anyone know if this have been fixed or reported at least?

    1. thatjeffsmith Post
      Author
      1. Thanks for your response, thatjeffsmith. I do that, when I remember to do it otherwise I deal with the app hanging…

      2. thatjeffsmith Post
        Author

        we’re left there waiting for the db to respond or for the jdbc driver to realize the connection isn’t avail anymore

        You CAN try a thick connection if you have an Oracle Client installed, that might provide some relief.

  8. Hi

    i am creating package using SQL developer.

    When I editing any thing in package. SQL developer start to check for error. because my package is very lengthy, it’s took time to re verify the whole package for any error.

    It’s very annoying . can we disable this. so we can see the error only when we compile the package
    .

    1. thatjeffsmith Post
      Author
  9. I just started using SQL Developer. I am opening a SQL script that was saved in another tool that I was using. It opens with these characters: ÿþ
    and puts a space after each letter like:
    s e l e c t * f r o m …..

    How can I fix this?

    1. thatjeffsmith Post
      Author
      1. We had written it in DBArtisan.
        Default Encoding was ANSI.
        Opens up in notepad with no proper alignment or indentation, but without space.

      2. thatjeffsmith Post
        Author
      3. Ah.. Just realized that some of my colleagues had the default encoding ‘unicode’ and that’s what is causing ÿþ and spaces.
        Thanks Jeff for pointing me to the right direction.
        🙂

  10. Hi, Im using oracle sql developer 4.1.2. I find the opening of multiple SQL query result window/tabs mildly annoying. I’m sure there are very useful cases for this feature, but my question is: Can we turn the multiple query result windows to just one (Toad style).

    I tried Tools > Preferences > Database > Worksheet >uncheck “Show query results in new tabs”

    But Still I see the same . please help me

    1. thatjeffsmith Post
      Author
      1. thatjeffsmith Post
        Author
  11. Hey Jeff,

    I am having an issue with opening a New Connection in SQL Developer. I am not sure why but everytime I right click on Connection and then choose New Connection, nothing ever happens, no box appears. Is there some type of setting that could cause this?

    thanks
    Greg Hayes

    1. thatjeffsmith Post
      Author
      1. Jeff,

        Yes, the same issue when just trying to hit the Green Plus/Add button. I currently don’t have any existing connections. I am taking a class in PL/SQL programming right now and I am trying to create the connection. So, no existing ones yet. I contacted the schools DBA Helpdesk to see if they knew. They only suggested closing SQL developer and reopening it. Which I have tried a million times, so I am at a loss for words right now.

        Greg Hayes

      2. thatjeffsmith Post
        Author
      3. I am using windows. However, SQL Developer is not downloaded to my Hard-drive. I am logging onto my schools online application desktop and then starting it through there. I don’t know if this changes anything though.

        thanks
        Greg Hayes

      4. thatjeffsmith Post
        Author

        ugh…remote desktop…not my favorite.

        ok, well it means this isn’t your problem, the team that manages that desktop environment needs to check that the install is valid. For example can the application user create a file in the AppData, Roaming Profiles directory? If not, they’ll need to tell SQLDev to write the application preferences files somewhere else.

        We have a VirtualBox appliance you could run on your own machine – it has SQL Developer and a database all setup for you to use. You get to own your own environment.

  12. Hi Jeff,
    In latest sql developer version 4.2 ; the script output is coming with a CF ; appending extra CF after every line .. For instance i ran three inserts> it is showing up as:
    inserted 1 record.

    inserted 1 record.

    inserted 1 record.

    How to make this extra CF go away ?

    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *