Top 10 Preferences to Tweak when using SQL Developer

thatjeffsmith SQL Developer 149 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 Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 149

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

        1. thatjeffsmith Post
          Author
          1. 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.
            πŸ™‚

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

        1. thatjeffsmith Post
          Author
          1. 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

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

  4. 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
    1. thatjeffsmith Post
      Author

      Copy over your AppData folder for sqldev.

      Or this basically…

      Windows – located in the C:\Users\$USERNAME$\AppData\Roaming\sqldeveloper…
      Version 4.0 is in a 1.0.0.0.0 subdirectory
      Version 4.1 is in a 4.1.0 subdirectory
      Mac and *Nix – located in the $HOME/.sqldeveloper/…
      Version 4.0 is in a 4.0.0 subdirectory
      Version 4.1 is in a 4.1.0 subdirectory
      Fun stuff – Windows usually hides the AppData folder – tell Explorer you want to see the hidden folders. On Mac and *NIX, dot(.) directories are hidden by default, so use the ls -a command to see it.

      1. Jeff, the only thing I am finding there is the product.conf file. Which only has the path to Java JDK and everything else is commented out. Preferences must be elsewhere.
        Confused.

        1. Ah, found it …
          Navigate to the following location,

          Step 1: C:\Documents and Settings\\Application Data\SQL Developer

          Step 2: C:\Documents and Settings\\Application Data\SQL Developer\systemXX.X.X.X.XX

          Step 3: Copy the product-preferences.xml in the location below,

          C:\Documents and Settings\\Application Data\SQL Developer\systemXX.X.X.X.XX\o.sqldeveloper.XX.X.X.XX.XX

          Step 4: Copy the connections.xml in the location below,

          C:\Documents and Settings\\Application Data\SQL Developer\systemXX.X.X.X.XX\o.jdeveloper.db.connection.XX.X.X.X.XX.XX.XX

  5. When I do a select in SQL developer (MySQL database), it creates a transaction and the table Is locked. Is there any way to avoid this lock. Am fine to only query the database to view the data.

    Thanks
    SJ

    1. thatjeffsmith Post
      Author
        1. Yes sir..how can it be possible to import XML data into tables individually like importing the data from excel or csv?

          1. thatjeffsmith Post
            Author
  6. Hello Jeff —

    Thank you for the good information. I am currently moving from Oracle SQL Developer to PL/SQL Developer 11.0.6.

    Every time I open PL/SQL Developer, it immediately prompts for a database connection. Is there a way to stop this from opening by default?

    Thank you,

    Joshua

    1. thatjeffsmith Post
      Author
  7. I am using Sql developer to Oracle 11g & Oracle 12c databases. I have it connecting to IBM MVS Databases , and am utilising the JDBC drivers. However, I have noticed that the EXPLAIN PLAN button is greyed out. I have moved the Oracle JDBC to be the first in the list for Tools\Preferences\Third Party JDBC drivers, but it is still greyed out. What should I do please to have this work with Oracle 12c & 11g databases.
    Regards Kevin

  8. Is there a way to get SQL Developer to delete previous whole words using a keyboard shortcut like CTRL+Backspace but NOT delete past the left margin?

    Example:
    SELECT COL1,
    COL2,
    COL3 <– my cursor would be here
    FROM SYS.DUAL

    I want to press ctrl+backspace to delete COL3 then press ctrl+backspace again to delete the whitespace preceding COL3 but NOT delete back to the next line placing my cursor after the comma following COL2.

    I have tried many different things and cannot find a setting that allows this. I hate that when I try to just delete the whitespace, my cursor ends up on the line before where I have to press enter and then press backsapce a few times to clear out the whitespace.

    You would be my hero if you can explain how to do this πŸ™‚

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author

          >>to delete the whitespace preceding COL3
          i press it once, deletes ‘COL3’

          i press it again, it takes out the 7 spaces after ‘COL2’.

          Where exactly do you want it to delete to?

          1. In many editors that I use and have used for development, ctrl+backspace is kind of a dual purpose. It deletes whole words and leading whitespace.

            I would like to be able to use that same functionality in SQL Developer. I would settle for deleting the leading whitespace (excluding the newline character) before COL3 and having the cursor stop on the same line that COL3 was on but stop at the very left margin). I may just have to retrain my brain to go for.

            I appreciate you taking the time to look at this!

          2. thatjeffsmith Post
            Author
          3. thatjeffsmith Post
            Author

            ahhhh, NOW i get it

            the closest i could get was to use ‘delete current line’, but that will put the cursor on the next line, as the entire line is blammo

          4. I started recording a video to show you what I was wanting…then I realized I couldn’t post it πŸ™‚

            I think it is just a matter of retraining my brain to use a different key combo to do this.

            Thanks again for looking at this and the help. This was a good article and I was so thankful for the Navigation Filters step you showed that allowed me to clean out stuff that we don’t use!

    2. Try and see if “Shift + delete” works for you.
      The difference I see is the next line shifts up, so you will have to press enter after the edited column name.

  9. Hi,

    Many times support people are running update/delete sql in production without where clause and causing issues.
    Is there any way in oracle SQL developer to restrict or check for statements without where clause and throw an error instead of sending for execution to server.
    any plugin for this or any other better way to handle it will be appreciated

    thanks
    Dinesh

    1. thatjeffsmith Post
      Author

      If they did it once, accident. If they did it again, training. If they did it a 3rd time, new job/lose production access.

      Not ONLY do they have to write the update/delete with a missing predicate, they also have to totally ignore the ‘106 rows updated’ feedback, AND then they additionally have to COMMIT the transaction.

      They don’t have AUTOCOMMIT enabled by any chance do they?

      1. Even if this happens once in a year, impact is huge
        so we want to check If there is any way in oracle SQL developer to restrict or check for statements without where clause and throw an error instead of sending for execution to server.

        1. thatjeffsmith Post
          Author

          surely…prevent access to production from folks that don’t know what they’re doing, or create an application that does the delete/update for them, or create before update/delete triggers

  10. Is there a way to change the numbers of rows that are looked at by the engine when it does an initial test of the IMPORT insert on sql developer?

    1. thatjeffsmith Post
      Author
      1. I’d like to do more, maybe 1000 or 5000 to test the data, so that i don’t go thru 50 columns and then find out it errors out.

  11. Hi Jeff,

    Is there a feature in SQL Developer which prompts a warning message if more than x number of rows are about to be updated or blocks updates or deletes.

    Regards,
    Som

    1. thatjeffsmith Post
      Author
      1. I was looking for a feature similar to “Safe Mode Update” that is available in advanced query tool.

        I do understand that it is redundant and requires firing a count query but since its configurable , its up to the user to figure out if they need it or not.

        A Safe Update Mode prevents accidental updating / deleting of more rows than intended. If more than one row is updated or deleted you will be asked whether you wish the update/delete to proceed or not. This option can be switched off or configured.

        http://www.querytool.com/help/366.htm

        1. thatjeffsmith Post
          Author
          1. Hi

            I am also looking for similar plugin or feature in oracle SQL developer where max rows update/delete can be configured or check if where clause is used or not, throw an error if where clause is not used

  12. Hi Jeffs,

    pl/sql developer 8.0 consuming more of CPU Is there a way to reduce the CPU utlization of pl/sql developer?.

    Regards,
    Max

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
  13. I noticed that with 4.1+ when running a procedure a default value of 1 has starting showing up for number fields. Is there any way to turn this off and leave it as null as it was and still is for other types such as varchar2?

    1. thatjeffsmith Post
      Author
      1. I was excited when I saw the 4.1.3 update had been released, but it still has the same bug. Any idea on when the default of 1 for a number field bug will be fixed?

        1. thatjeffsmith Post
          Author
  14. Hi,
    Is there a way to disable the commit command from running with sql developer? For example, a script has a commit command, once the user run this script, it won’t run because commit command is blocked/disabled.
    Thanks in advance.

    1. thatjeffsmith Post
      Author
  15. Great answers, very helpful already. Using Oracle SQL Developer 4.1.1.19, Looking for an option in Oracle SQL Developer to ‘ding’ when a long-running query completes – similar to MS SQL Mgmt Studio option of sound notification on query completion (helpful when multi-tasking with other apps on other monitors). Does such exist in OSD?
    Thanks

  16. How can I set a “name” to the Results tabs (not Query Result n)?
    How can I set a bookmark of table in a specify schema?
    Sometimes my table is in Another users…

    1. thatjeffsmith Post
      Author
  17. Hi Jeff,
    I am new to SQL Developer. i have a question. When i want to see the SQL of a table. it shows script with out the Drop table statement. Is there a way to add or drop the script options as in TOAD. I want Drop statement included in SQL.
    Thanks in advance.
    Aqeel

    1. thatjeffsmith Post
      Author
  18. > “Ask Tom (and Tom himself) isn’t anything BUT annoying”

    a freudian slip, or just being a little harsh today? πŸ™‚

    1. thatjeffsmith Post
      Author
  19. How can I remove that pesky “Ask Tom” search box on the top right right corner of the screen ? It’s really annoying…

    1. thatjeffsmith Post
      Author

      Upgrade to version 4.0 – we killed that. I agree, you have your Web Browser to do internet searching.

      BTW, Ask Tom (and Tom himself) isn’t anything BUT annoying πŸ™‚
      Ok, I’m an idiot – I meant to say, Ask Tom and Tom himself are NOT annoying. #BadJeff

      I think you can also go into the preferences and find an extension for that search mechanism that you can disable.

      But seriously, upgrade. Version 3.2 is almost 2 years old now.

  20. Why didn’t sql developer don’t save records and running long time with out showing any errors. I was trying to add a new record from data view

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  21. Jeff, is there a way to change the font in which the query results are displayed (i.e., NOT the font of the code editor)? I’m not seeing that option in Preferences, but then I’m very new to this product.

    1. thatjeffsmith Post
      Author

      The font preference controls both the editor and the grids. We’ve considered splitting that out to two preferences, but I’m wondering why someone would choose different ones…

      1. Well, there are always weirdos like me who could (and would) use this feature if it were made available πŸ™‚ Thanks for the response.

        1. thatjeffsmith Post
          Author

          Just to be sure – you know you can change the grid font now? It just also changes the code fonts…is there a font that you would like for data that would be drastically different than for the editor?

          Part of keeping it to one setting is keeping the number of preferences down, to keep things simple, a la KISS – but I don’t want to restrain folks unnecessarily either.

          Also, you’re not a weirdo πŸ™‚

    1. thatjeffsmith Post
      Author
  22. 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,

    1. thatjeffsmith Post
      Author
      1. 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,

        1. thatjeffsmith Post
          Author

          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.

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

  24. 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. πŸ™‚

    1. thatjeffsmith Post
      Author
  25. 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.

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
          1. 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.

          2. thatjeffsmith Post
            Author

            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.

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

          4. thatjeffsmith Post
            Author
  26. 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?

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

    1. thatjeffsmith Post
      Author
  28. 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

    1. thatjeffsmith Post
      Author
  29. 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.

    1. thatjeffsmith Post
      Author
  30. 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?

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

    1. thatjeffsmith Post
      Author

      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.

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

    1. thatjeffsmith Post
      Author
  33. 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? πŸ˜‰

  34. 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 πŸ˜‰

    1. thatjeffsmith Post
      Author

      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?

      1. 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 πŸ˜€

        1. thatjeffsmith Post
          Author

          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?

          1. 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 πŸ˜‰

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

    1. thatjeffsmith Post
      Author

      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.

      1. 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 πŸ™

        1. thatjeffsmith Post
          Author

          Email me with your details, there’s too many comments on this post to keep my head on straight πŸ™‚

          [email protected]

    1. thatjeffsmith Post
      Author

      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.

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

    1. thatjeffsmith Post
      Author

      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.

    1. thatjeffsmith Post
      Author
  37. 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.

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

    1. thatjeffsmith Post
      Author

      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.

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

    1. thatjeffsmith Post
      Author

      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.

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

    1. JeffS Post
      Author
    2. 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

      1. thatjeffsmith Post
        Author
  41. 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

    1. JeffS Post
      Author

      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.

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

  42. I guess it could be this:

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

    1. JeffS Post
      Author

Leave a Reply

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