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 Senior Principal Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

195 Comments

  1. Hi Jeff,

    I’m using ver 18.2 and cannot figure out how to prevent a couple of dozen recently-used files from opening automatically when I start SQL Developer (each in their own worksheet/tab). I have looked through every setting I can find and searched every way I can think of and haven’t found a solution.

    Thanks!

  2. Hi Jeff
    I’m using tool_env_getvar in a package. when I compile the package I get the foll. error :

    identifier ‘TOOL_ENV.GETVAR’ must be declared.

    How to fix this?

    Thanks.

    • thatjeffsmith

      The database doesn’t know what ‘TOOL_ENV.GETVAR’ is. Does it exist? Does your user have EXECUTE PRIVS for it?

  3. Jose Luis Donayre Reply

    Hi Jeff,

    I need your help. I’m using SQL Developer version 18.1 and I’m having issues with special characters. Specifically the Em Dash. (—).

    When I try to do a: SELECT ‘—’ from dual, I get a Question Mark (?) as a result. Now I know this is related with the NLS_LANG configuration. Other IDEs (like PLSQL Developer) are fixing the issue when I add an environment variable in Windows 10, with this value:

    NLS_LANG = ‘AMERICAN_AMERICA.WE8ISO8859P1’

    But it is not working in SQL Developer.

    I checked the nls_database_parameters and v$nls_parameters, and both have the value:

    NLS_CHARACTERSET = ‘WE8ISO8859P1’

    Any idea how to fix this? Appreciated.

    Thanks
    Jose

  4. Is there any way to remove or hide the ‘Oracle NoSQL Connections’ and ‘Database Schema Service Connections’ categories in the Connections panel? Yeah, they’re out of the way down at the bottom, but I have no prospects for using either one, so seeing them isn’t helpful.

    Also, a suggestion: sometimes I want to organize connections by application, other times by environment (dev/test/prod). Folders are great, but I need to pick one scheme or the other. Any chance we’ll someday be able to put a connection in more than one folder, or have nested folders, or be able to switch among connection lists?

    Thanks!

    • thatjeffsmith

      You just need to upgrade to 18.3 – nested folders, Check! Hiding unused connection types, Check!

    • Any chance we’ll get someday folders also in the drop down on the SQL Worksheet ?

      And what about a configuration flag “Show open connections first” for the drop-down list ? If you have several SQL Worksheets, the order is (open connection, closed connections), but at time of SQL Worksheet creation. Subsequent connection openings don’t show up at the top. And newly created connections always appear at the bottom. This gives a feeling of random order and is especially annoying when you have 400+ connection definitions.

    • thatjeffsmith

      Yes, that’s on the list for 19.x

      What you’re describing on the connection order is a bug. If I can replicate it, will try to get fix for 18.4

  5. Hello,

    When I open a SQL file which is attached in an Outlook mail, it opens correctly in SQL Developer, but in read-only mode as the file is temporarily saved in C:\Users\Username\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\LM9N06BP with the read-only flag set.

    How can I easily change the editor mode so that I can edit the file ? Menu ? Mouse click ? …
    I could save the file at some other place, but that will need some extra cleanup when the work is done.

    Thanks for any help.

    Regards
    — Alwin

    • thatjeffsmith

      I wonder if there’s an Outlook setting to not make those files READ ONLY, but I’m guessing it’s a SECURITY feature.

      No way to do this from inside of SQL Developer that I know of.

      I’d probably copy it out of the preview window (text to clipboard) and paste into a useable sql worksheet if you’re doing this a lot

    • Thanks for your answer.

      In outlook, you can edit the message and then open the attachment. But that way you may change inadvertently the file in the message you received, which is something I would try to avoid.

      It would be nice to have this ability directly in SQL Developer, like most text editors do. How do I submit a request for this ?

    • thatjeffsmith

      instead of emailing data around to folks to get changed and then whatever, seems like a better idea to build an APEX app so folks can edit/share the data live with just a web link

    • We’re not using it to develop code (these scripts are stored in subversion or git to keep track). It are requests to fix data or hot patches in schemas where developers have no direct access. These scripts are coming via mail or via our issue tracking system. In both cases the file opens read-only, but sometimes I need to change an ID, a value or add an alter session before running the script. That’s why I need to be able to modify the file. These small changes will never be kept.

  6. 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

    • thatjeffsmith

      it’s one click to open, and one more click to debug…and then when you are debugging, we need to have the code editor available – so, no…sorry 🙁

  7. 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

  8. 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.

    • 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?

    • thatjeffsmith

      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.

  9. Ali Alhaidare Reply

    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

  10. 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

    • 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,

  11. Tim Wimmers Reply

    Any idea how to import a pl/sql scheme so SQL Dev looks like TOAD?

    • thatjeffsmith

      How do you mean a ‘pl/sql’ scheme – you mean the code syntax colorization effects?

    • 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.

    • thatjeffsmith

      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.

  12. 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?

    • Thanks Jeff.

      And would you have the answer of changing tabs in the results grid?

    • Cancel the last request, it Alt+ the arrow keys.

      Thanks again.

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

  13. Juan Hernandez Reply

    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?

    • thatjeffsmith

      If it’s been awhile, i would right-click reconnect your connection and THEN to the compile.

    • Juan Hernandez

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

    • thatjeffsmith

      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.

  14. Sanjay Modi Reply

    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
    .

    • thatjeffsmith

      you mean the parser checks for syntax issues? We don’t check for pl/sql errors until after you do a COMPILE

  15. 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?

    • thatjeffsmith

      No idea.

      Where did the file come from?

      What’s the encoding?

      What’s the file look like in Notepad?

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

    • thatjeffsmith

      weird…it’s just a plain text file?

      are all the files like that, or just this one? can you send it to me?

    • 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.
      🙂

  16. vikranth sanka Reply

    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

    • thatjeffsmith

      if that preference is unchecked, that shouldn’t be happening. you might want to upgrade to v4.1.3

    • Vikranth Sanka

      Hi,..

      I have upgraded to V4.1.3. Still the same issue exists.

    • thatjeffsmith

      Did you start with a ‘clean’ install, or import your preferences from 4.1.2? I’m hoping it was a clean, no import?

    • vikranth sanka

      I have started with import preferences from 4.1.2. Ok let me try the other way now.

  17. Greg Hayes Reply

    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

    • thatjeffsmith

      do you have the same issue if you hit the Green Plus/Add button up on the toolbar to create a new connection?

      Are you able to edit an existing connection’s properties?

    • Greg Hayes

      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

    • Greg Hayes

      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

    • thatjeffsmith

      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.

  18. Ram Sawroop Reply

    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 ?

Write A Comment