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.

thatjeffsmith
Author

I'm a Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

203 Comments

  1. Hi Jeff,
    I have some DEV users working on PL/SQL packages under SQLDevelopper.
    My problem is that we are installing packages under SQL*plus for automation of install, and that SQL Developper is more tolerant than SQL*plus, especially for “/” after en of Create Function/package/procedure.

    Is there a way that SQLDevelopper script launching can be compatible with SQL*Plus?

    Thanks in advance,

    • thatjeffsmith

      Jerome, I’m not sure what you’re asking for exactly. So, can you give me a bit more detail on what you mean by ‘compatibility?’

    • Yes, surely. Thanks for your answer.

      We have some procedures installed on different environmment using SQL*Plus tools.

      The dev team is developping those procedures under SQLDevelopper, but never testing under SQL*Plus.

      The problem is for PLSQL, if under SQL PLus you don’t put a ‘/’ the package/procedure/function/PLSQL piece of code is still launched and executed.

      This is not working under SQL*Plus.

      set serveroutput on
      BEGIN
      DBMS_OUTPUT.PUT_LINE(‘test’);
      END;

      bloc anonyme terminΓ© –> in french sorry…
      test -> has worked.

      Where as under SQL*Plus
      SQL>
      set serveroutput on
      BEGIN
      DBMS_OUTPUT.PUT_LINE(‘test’);
      END;SQL> SQL> 2 3
      4
      5

      This is ignoring all the code until there is a ‘/’…

      My proposition would be that there could be some optional checkbox like ‘SQL*Plus buffer management’ allowing the SQLDevs tools to parse like SQL*Plus does, waiting for ‘/’ else nothing…

      Thanks for your reading,

    • thatjeffsmith

      Anything that’s going to be ran through SQL*Plus into production, needs to be tested through SQL*Plus – that doesn’t mean you can’t develop it in SQL Developer though. And SQL Developer works just fine with the slash terminators.

  2. Brad Simmmons Reply

    Great ideas and tips here!

    Not sure where else to ask, but how do I get SD to display non-printable (‘whitespace’) characters in the data grids? (Just like the sql editor option for displaying whitespace.) I work with a lot of dirty file imports and being able to visually id non-printables would be really helpful. Even a would help!

  3. Roger Rabbit Reply

    This is so funny…. I found the article and started to search all the options in my Toad for Oracle.
    I didn’t realize this article was for SQL Developer, since I had it saved in the bookmarks.
    (Not) Surprisingly most of this options are in Toad as well. πŸ™‚

    • thatjeffsmith

      The tools are different, but the users, and what they want/need are generally the same. How we deliver that is where we try to shine.

      Sorry I ‘tricked’ you πŸ˜‰

  4. Jeff, I am running Oracle SQL Developer 4.0.1.14. When I debug a package it opens a new tab of the package body. This tab seems to have a previous version of the package bodies code (It does not have my changes). I guess I have a few questions
    1) Can I stop this behavior? I really hate having tabs open, I just want to have the 1 tab that I am working in.
    2) If I must have a new tab why am I seeing previous versions of the package?
    Thanks in advance for any help.

    • WOW, I found a bug for Oracle. I usually just make bugs since I’m a developer! So I have rolled back to version 3.2.20.09. Any idea when / if there would be a new release. Any way I could be notified of a new release. My team at Hertz uses SQL Developer, we try to stay on a newish version. Thanks so much for looking into this issue. Is this a bug in all of version 4? I’ll advise my team to not use this version.

    • thatjeffsmith

      Yeah, in all of 4 I think.

      Not to trivialize the bug, but this seems more on the minor side, definitely not hard to work around. In most dev shoppes I find less than 25% of the devs even use the debugger.

      And even if you do, simply close the package when you’re done debugging.

      To answer your question, we MAY have another patch for v4.0. Either way, we’ll be sure to get this fixed for the next drop.

    • flindeberg

      Any updates on the bug? It is really irritating, using 4.x for daily work, but I must revert to 3.x for debugging. Breakpoints are being changed as well when the file is modified.

    • thatjeffsmith

      It’s fixed in our internal builds – it will be included when we ship a new release/patch.

  5. We have a 5000 lines of PLSQL codes. Don’t ask me why it’s that long. Whenever I do code format (Ctrl+F7), the IDE cursor runs to the bottom of the code making me lose my focus codes I am currently editing. Why is it like that?

  6. I’m using Oracle SQL Developer 3.0.04 here at work. I connect to our database and I see a directory structure type list of all the usual things… Tables, Views, Indexes, etc. I expected to see a list of the tables under Tables when I clicked on it (well, on the plus in a square icon). Instead, the little plus-in-a-square icon goes away and no table names are forthcoming. It is as if there were no tables. There are definitely tables … I can do Selects on them to my heart’s content. I searched online and saw that others have had a similar problem. Is there a jre compatability issues? One website suggested adding this to the sqldeveloper.conf file: AddVMOption -Doracle.net.disableOob=true

    Or even adding this if running from the cmd line: -J-Doracle.net.disableOob=true

    Neither of these work. Any ideas?

    —miserable in dallas, texas

    • thatjeffsmith

      It sounds like this is happening to you.

      Also, that version is way too old. Tell you work folks it’s time for a FREE and 5 MINUTE update πŸ™‚

  7. I am trying to set my preferences and I make the update to the preferences then press ok but when I open sql developer again the preferences didn’t save.

    What am I doing wrong?

    Thanks Cshartman

    • thatjeffsmith

      It sounds like you don’t have write permissions to the Application Data directory on your machine – assuming you’re on windows.

  8. Priority: Production issue
    while running query it is showing a result, but after updating table and rerun the query it showing the same result form sql developer.
    But at sql*plus in server showing the result which we want.
    But we want report using sql developer, which showing old result.
    How we can overcome this problem.

    • thatjeffsmith

      My first guess is you are updating the table from SQL*Plus and not committing the change, so it’s not viewable outside that session.

  9. Is there a shortcut in the Worksheet to move from SQL to SQL … either to the beginning or to the end of the SQL? Will be very handy. Also, jump from beginning to ‘from’ clause to ‘where’ clause to ‘group by’ etc etc

    I tried setting shortcut on ‘Other->Next’, but it did not do anything. What is Next/Previous for anyways?

  10. Jeff,
    SQLDeveloper is really good at many things. One thing I love most is editors regular expression! Its really great and has saved me tons of time.
    One question: when I am pulling down 30k rows from sql and exporting it to say excel format almost every time (depending upon rowlen) it tanks somewhere in between and then proceeds at a crawl pace. Same download on toad/plsqldeveloper flies. What is going on? Are you buffering data in memory? or is it being written to file in array size (any way if just showing or downloading)?

    the latest sqldeveloper 4.0.0.12.84 has sqlcli.bat without invocation to sqlcli at the end!!

    one feature by default caused me grief is nls_sort and other settings – they overwrote some other than database had got in a whole lot of trouble

    all above from 3.x version – just downloaded 4.x version

    It is much slick look and I want to use sqlcli very much –

    Thanks for hard work, its great!!

    Sudhir

    • thatjeffsmith

      Don’t use the XLS format, use the XLSX format. It will run fast and is robust to handle larger 1,000,000 row-type resultsets. The XLS format is an older format that the library reads into memory and doesn’t release…the XLSX format in v4 does not have this critical limitation.

      The NLS settings are for your client application, in this case SQL Developer. Check your registry, you’ll see for your Oracle client installs (to run the other applications you mentioned) will also have NLS settings. They don’t ‘overwrite’ the database settings, they dictate the settings for your client session.

      You want to run sdcli.exe – I’m not sure what you mean about sqlci.bat? Here’s a quick write-up on the command-line interface stuff.

  11. Is there a setting to show you how many characters your identifiers are? Oracle is set to 30 characters. It would be nice if there was a way to have it highlight the background of identifiers for the 30 characters so you could see where you are in the amount of characters you have left(or how many you have over 30).

  12. PS. I actually would have guessed that the Formatting option “Use Tabulator” would have controlled it.
    So that if it was set to “Use Tabulator”, backspace would of course delete the “TAB”, but if “Use Tabulator” was unchecked so indenting is done with spaces, then I would have expected backspace also to “use spaces.”

    Could that be an enhancement request? Pretty please? πŸ˜‰

  13. A related question:

    How to make backspace only remove one single space at a time instead of deleting back to nearest “tab stop” (as per the indentation setting) ?

    If I have my indentation set for 3 and I type three spaces at the beginning of the line, one press on backspace removes all 3 spaces. How can I avoid that? I keep on pressing backspace too often and end up deleting a character or two at the end of the line above…

    I would like backspace to only delete ONE space at a time, but the only way I have found to make it so, is to set my indentation size at 1 – which I also dislike πŸ˜‰

    Thanks for any help πŸ˜‰

    • thatjeffsmith

      I don’t think you can Kim. I’ve had set to ‘5’ for years, and I’ve never noticed this behavior before, but it is there. Apparently I don’t type enough πŸ™‚

      The backspace does indeed only delete 1 character at a time IF the editor sees a ‘word’ vs just the tab stop.

      I guess your recourse is to slow down your keyboard or use the UNDO/ctrl+Z to get your characters back after a delete frenzy?

    • It’s more a matter of typing habits. Often I get to right position I want to type in by pressing TAB-TAB-TAB-BACKSPACE rather than TAB-TAB-SPACE-SPACE-SPACE. That’s what my fingers are used to after 12 years of Toad, so it’s just an annoying (to me) little thing that keeps me from switching to SQL Developer πŸ˜‰

      Oh, well, worth a shot trying to ask you. Thanks for answering. See you in New Orleans πŸ˜€

    • thatjeffsmith

      After 10 years of using another IDE, I had many quirks to unlearn. It is possible πŸ™‚

      No need to switch tools, use what works for you!

      That being said, this doesn’t mean we can’t file an ER. So what ideally would the new behavior exhibit when using BACKSPACE? I imagine many users would be upset having to hit the key many times to undo whitespace that only required a single key to create in the first place?

    • Sure – users who are used to the SQL Developer way would be upset with my way – I understand. There are probably about as many opinions about “the right way” than there are programmers (or more…)

      I think the best probably would be a preference setting allowing the user to choose between “Backspace to tab-stop” or “Backspace single space”.

      Not a critical ER – just when convenient πŸ˜‰

  14. I am running sql Developer version 3.0.04. When I click on a Heading for a column in the data of a table the filter window pops up. The number of items in listed in the filter window is limited. I (think that I) would like to have all available entries shown so that I don’t have to “know” what I want to use as a filter.

    Is there a preference option that I can play with to change the number of items displayed in the filter window?

    • thatjeffsmith

      We don’t know what all the distinct values are, because all of the data hasn’t been fetched yet. In the grid, do a ctrl-PgDn. Then do the column value filter dialog. Beware, if there’s a million unique values on that field, then don’t complain that your computer bogs down when you do this – you get what you ask for πŸ™‚

      Also, any chance you can upgrade to version 3.2.20.09? Version 3.0 is way way way out of date.

    • When I click on a column heading I am not given that option anymore (my old computer would show it). Any idea how to enable this feature? I have scoured the internet high and low πŸ™

  15. Jeff,

    How do i control look and feel for the results window?

    Thanks
    Avinaash

    • thatjeffsmith

      You have 3 things for results windows and grids in general that you can tweak:

      1. Font Face
      2. Font Size
      3. The Checkerboard/Zebra affect

      The checkerboard preference is on the Database > Worksheet page in Tools > Preferences. The font settings are in Code Editor > Fonts, also in preferences.

  16. Carlos Pinho Reply

    Hi Jeff!
    Imagine that i have configured all my preferences in my SQL developer, and now i want to put the same preferences in my co-workers laptops. Is there any way to export my preferences and load it in their’s SQL developer? Or i really need to remake all the steps that i have made in my SQL Developer?

    Thanks in advance!

    • thatjeffsmith

      I would copy the preferences.XML file over to your co-worker’s machines. Just make sure your copy of SQLDev is closed when you copy the file and their copies of SQLDev are closed when you paste.

  17. Ragavendran Reply

    How to view the single row size in oracle using sql developer

  18. Great stuff. Thanks for educating us. Lovin the Preferences feature.
    Few comments from my side:
    -a- It wasn’t too intuitive for me personally that if you change some option and press “Esc” button the changes are not saved (especially if you do multiple changes). I wonder if the behavior is the same in chrome or other leading GUI. I would at least introduce a warning stating that if I would like to save changes I should press Enter instead of Escape at the time I press Esc button
    -b- Preferences auto search functionality is great (reminds Chrome preferences page :). However I would include individual preferences names in the search scope . e.g. tro change SQL Worksheet font you type “Font”. However to change “SQL History Limit” a user should type “Worksheet” or “SQL Array Size” => “Advanced”. IMHO: it a bit confusing and makes the preferences search a bit more difficult. If I would be able to change “SQL Array Size” typing “SQL Array” or “Array Size” it would be even more coooler πŸ™‚

    Let me know if the enhancement requests already exists for the 2 suggestions above or should I file a new once. If those exists I am happy to add my vote there πŸ™‚

    Keep up the good job you are doing man.

  19. Chris Gould Reply

    In the PL/SQL debugger, is it possible to list the member procedures/functions of a package in alphabetical order to make it easier to find the one you want to run? I can’t find an option for that anywhere.

    • thatjeffsmith

      Not today Chris, but I just added it to ‘the list,’ so stay tuned!

      For now you can use the kb to navigate the Target list. If my package has 10 procedures, I can type through the list.

    • In version 4 you can right-click on the package name and there is an ‘Order Memebers By’ option.

  20. Jeff, I have a couple of issues. I need to take an SQL certification course so I decided to do the following:
    1. Download (to my personal PC) the free SQL Developer 3.1 (SD), the JTDS file 1.2, the free download of Oracle Express 11G.
    2. Initially all seemed fine, but I could not enter and save all the recommended default values for create a new (hr) database(DB) connection. So in my smart mind I thought maybe I need Oracle loaded to my machine. Although all documentation I read did not specifically say I did need it.
    3. Downloaded 11g Express and it had it’s own set of problems (I’ve captured the problem in a screen image.
    4. So now I am still no closer to running SQL queries so I can finish up my developer exam so I can get certified and I am running out of time.
    Oh That Jeff, can you help ? I have read many documentation areas and brought it up to the SD developer forum who are really great by the way. My real consternation is do I need Oracle in order to add new connection values for the DB called HR ? And the forum user are familiar but I have received basically the answers I’ve found from reading and the recommended default values are not giving me the correct results. Which is just be able to create a new hr db connection with the recommended default values. Plus the Oracle 11g appeared to have an issue ……Help plz ??????????????? M

    • thatjeffsmith

      Oracle Express 11G will have everything you need to run queries. You say you have a screen image showing problems with your install? Where is that?

      Once you get it running on your machine you can connect via SQL*Plus or SQL Developer.

  21. We would like to capture inofmation like who, when and why a user connected to a database in production using SQL developer, so i was wondering if is it possible to customize the “Connect” option to prompt the user to enter the “Why” information at the instance when he/she connects to a database? I know “When” and “Who” can be captured behins the scenes via monitoring but we are also intersted to see if there is a capability to put additional information such as “Why” within the connect option in the SD interface.

    Tx

    • JeffS
      JeffS

      Thanks for such a great question! It’s inspired me to write today’s post!

    • Jim McCusker

      Jeff,

      Has the ability to set the default directory for saving SQL Scripts been added to SQL Developer? If so, what version was this implemented in and where is the setting located?

      –Jim

    • Jim McCusker

      Darn… I know it’s a simple feature but it’s really annoying not having a default load/save folder.

  22. Hi Jeff,

    I know you can set the default location for scripts at:
    Tools > Preferences > Database > Worksheet

    But what about a default for File Open (say when you first open SQL Dev)? Is there a default for that? If not, how can we control the options on the left of the File Open dialog?

    Regards,
    Dan

    • JeffS
      JeffS

      It’s on our to-do list Dan. I can’t tell you if and when it will happen, but I can say that I agree with you, it’s a good idea!

      There is request very similar to this on our Developer Exchange, please go vote for it! I know it says it’s targeted for v3.1, but it did not make it this time around.

    • Yes, this is the option I’m looking for. Even a little past that, when you open a database connection have the option to open a particular SQL worksheet. I run a lot of the same SQL over and over again just depending on what database I’m looking at. History is a good option, but opening a default worksheet populated with SQL of my choosing is a better option!

  23. I guess it could be this:

    Preferences->Sql Formatter->Oracle Formatting
    Choose the profile you are working on -> Edit
    Indentation->Spaces

    • JeffS
      JeffS

      Suck the gremlins out of your machine? That should be the default behavior.

Write A Comment