SQL Developer Quick Tip: Accessing SQL History with the Keyboard

thatjeffsmith SQL Developer 56 Comments

Tell Others About This Story:

When I’m doing my ‘Tips and Tricks’ show, there is at least one trick that is guaranteed to get the audience’s attention:

I’m in an editor – INSERT MAGIC – wham! I’m cycling through the SQL History list, no need to use the mouse.

The white screen of panic, no code, NO CODE!

Here’s a set of keyboard shortcuts I want you to memorize RIGHT NOW:
Ctrl+Down — Next
Ctrl+Up — Previous

The concept of ‘Next’ and ‘Previous’ will depend on the sort order defined in the View – SQL History (F8) dialog. I have mine sorted to Timestamp DESC, so that when I go to ‘Next’, I’m going backwards in time. If I want the query I executed 5 iterations ago, that’s CTRL+Down+Down+Down+Down.

Configure what 'previous' and 'next' mean before you start!

Now before you try this, keep in mind this will CLOBBER your workspace.

So once you get the hang of Down and Up, thrown in a SHIFT to your Ctrl+ keystroke. This will APPEND the SQL to the worksheet instead of clobbering the text.

UNIX command-line fiends already should know this trick, using the Up and Down arrow keys to recall previous commands.

Tell Others About This Story:

Comments 56

  1. So the SQL Developer’s history function makes more victims. I’ve just lost a long script because I’ve pressed too many CTRL-Down/Up (eclipse habits) so the Undo was useless.

    There are two things that should have been different with regards to how this feature was implemented and that can be corrected in a future release:

    1. The workspace (that can contain a not-run-yet script) should be retrievable using CTRL+Down/Up. Please see the bash history function (Up/Down keys).

    2. The default number of Undo should be greater. There is no reason to have such a low default.

    At least the second one is feasible to be implemented in the next release.
    The people who posted here are the proof that this function is error prone and that a correction is required, because it’s a good application.

    1. thatjeffsmith Post
      Author
  2. I know this is an old thread…

    Unfortunately I cannot share your enthusiasm for this particular keyboard shortcut….

    I was unaware the effect of Ctrl Up and Ctrl Down until I “clobbered my workspace” with no kind of warnings whatsoever. I didn’t immediately notice that I’d at some point replaced my “masterpiece”(!) with something from history. It was some days later long after I’d “saved” my (now clobbered) work that I discovered the terrible effect of Ctrl Up and Ctrl Down, and I was too late to retrieve the old code from history either (another normally really good feature) because for some reason, the history did not go back beyond the point where Ctrl Up or Ctrl Down had silently deleted my work.

    The history feature is great. The trouble is that this is Ctrl Up and Ctrl Down are a common cursor moving shortcut in many editors and applications (Word: start/end para, Excel Start/End of block, and so on(, and I could not believe it had been turned into something potentially destructive here (yes I know we have undo, but that’s only if you realise you need to undo!) .

    So first thing for me to do was go delete those shortcut mappings. I use many applications and “muscle memory” makes it far too dangerous.

    It feels similar to if you’d mapped Ctrl+S to an “Exit without saving” function.

    1. thatjeffsmith Post
      Author

      If we changed it now, I’d probably upset the 1.5M users that don’t use that kb shortcut in the other editors you mentioned. So yeah, I can see why it might not be the most awesome shortcut, but you can change them. You can also increase the number of UNDOs to persist, the default is 50.

      1. Worst two shortcuts in SQL Developer, best SQL tool. I can’t count how many time I lost new queries (not yet executed) due to this ‘feature’. Why not turn it off by default and let less than 10% of the 1.5M users who probably really need it to turn it on manually in preferences? You probably already upset much more % of users than you thought… but you can change it. Please remove this 2 shortcuts, don’t hang on annoying features. Thanks a lot.

        1. thatjeffsmith Post
          Author

          I’ve heard this from 2 or 3 users. Maybe it could be better. What would you suggest? What have you re-mapped it to? And how can I train everyone else to learn the new shortcuts?

          You don’t lose the query by the way, you just use Undo (Ctrl+Z) to get it back.

          1. I would propose to unmap both and leave it. I don’t think you need to train others, showing note about it where to find keyboard mappings should be enough. I’ve cleared it and didn’t remap it as I’m fine with calling history window with F8. There (when history window is opened for 1st time after install) could be one time popup/notification about history shortcuts mapping.

            Maybe I was just annoyed yesterday because I forgot on Ctrl+Z and pressed Ctrl+Up and Down multiple times and could not find what I was showing to someone on other PC (default SQLDev settings). Or there may be probably more important features to cover that this… Have a good weekend 🙂

    1. thatjeffsmith Post
      Author

      best case scenario, the desktop just got messed up
      Window > Reset Windows to Factory Settings

      worst case scenario, the history xml index file got corrupted and you’ll need to ‘repair’ it or nuke it and start from scratch

      Try the best case first and we’ll go from there

      1. Thanks a lot…

        However, I found another workaround…

        #1 – go to C:\Users\\AppData\Roaming\SQL Developer\SqlHistory folder on my windows machine
        #2 – There were .xml files till 30-JUL-2017
        #3 – I have closed the SQLDeveloper and deleted all the .xml files dated 30-JUL-2016, (considering there is some corrupt .xml files).
        #4 – Restarted SQLDeveloper and now I am able see the SQL History and all Shortcuts mentioned above are working fine now

        Thanks,
        Umesh Namdeo

        1. thatjeffsmith Post
          Author
  3. How to find the update query from the history of SQL developer. In case if it is deleted intentionally, it is it possible to retrieve?

    1. thatjeffsmith Post
      Author
  4. This is easily the most annoying and potentially destructive “feature” in SQL Developer. I bump into it on a regular basis, and end up doing a lot of ctrl-Z ing to back out. Because you never press ctrl-down just once, it’s always somewhere between 7 and 33 times.

    Anyhow, the answer is simple, you really can disable it! Go into Preferences / Accelerators and scroll down to:
    SQL History: Append from Next and then click Remove! Then do the same with SQL History: Append from Previous, SQL History: Replace with Next & SQL History: Replace with Previous.

    And that’s it. Now your editor will behave like every other windows text editor, and you will never get caught out again!

    Yay!

    1. thatjeffsmith Post
      Author

      Yup, what some people love, others hate.

      Of course, if you just wanted every other windows text editor, that’s where you’d be?

      I just noticed ctrl+down doesn’t do anything in Notepad++, so I’m not sure why you’re pressing it 33 times in the first place?

      1. Well… Ctrl+Up and Ctrl+Down DO do something in notepad++.

        It’s admittedly subtle, but it moves the scroll bar without changing the selection (unless the selection moves off the visible screen)

        Ctrl+Up and Ctrl+Down is a common mapping in many applications. The mapping isn’t always the same but in all cases I’ve seen it has something to do with cursor movement. To map it to something unrelated to moving the cursor, with potentially destructive consequences… hmmm… now let me think on that a moment and see if I can imagine why some people might hate it…

      1. thatjeffsmith Post
        Author
    2. Was hitting the same problem on a Mac. I just wanted to navigate within my SQL file, and hitting Command+Down/Command+Up would erase everything I had written. On Mac, I went to Preferences > Shortcut Keys and in the search box type “History”. Then simply delete the shortcut, and Command+Up/Command+Down will get the default behaviour on the OS again.

  5. Jeff,

    Thank you for a great article, I’ve been looking for years for the ability to scroll SQL history.
    It seems to me, the failed commands don’t go into SQL History. Is there a way to make failed SQL commands to go into SQL History?
    Thank you in advance.

    1. thatjeffsmith Post
      Author
      1. True. Here is my scenario, I am working on a SELECT statement that has an error, I ran it 3 times and it failed 3 times. I am on the 3rd iteration and it just occurred to me the 1st iteration is what I need. There is no way to retrieve the 1st (failed) iteration thus do no try to develop with errors in SQL Developer. I would like to have at least the option of keeping the failed SQL in SQL History.

        1. thatjeffsmith Post
          Author

          yeah, I can see that

          when i’m working on a new query, i keep each iteration of it in my worksheet, and if it’s REALLY important, I use a file, and save it after each change – then i can take advantage of the automatic file history feature

          1. We follow the same strategy. I just keep everything in a file and constantly switch between SQL Developer and my file editor… but you know how it is, sometimes I just get too excited or I am in too much of a hurry and then I loose some of my work. It is also very time consuming to constantly switch between SQL Developer and the file editor. The old non-SQL Developer SQL*Worksheet saved both successful and failed SQLs in SQL History and I would like to have this option in SQL Developer.
            Jeff, your web site is the greatest for SQL Developer! Many thanks.

  6. I just discovered this feature by accident. So now I’ve completely lost the query that I was working on at the time.
    I think you really need to save the current workspace as a version in the history before replacing it with the previously run statement.
    Rather annoying!

    1. thatjeffsmith Post
      Author
  7. I’m on 4.0.3.16. I used to be able to double click on query from SQL History and it would append the query into current Worksheet, but not in 4.0.3.16

    Is there a way to control that behavior ?

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
  8. Jeff, my version of SQL Developer apparently shipped with the following. After trying your suggestion and finding that it mysteriously apparently did nothing, I noticed the note at the bottom of the Shortcut Keys window – “Conflicting shortcuts will be removed if the key combination is reassigned.” I decided to sort the table by Shortcut, and sure enough:
    Code Editor Scroll Line Down Ctrl-Down
    Other SQL History: Replace with Next Ctrl-Down
    Code Editor Scroll Line Up Ctrl-Up
    Other SQL History: Replace with Previous Ctrl-Up

    I tried to reassign the Code Editor ones to Alt- combinations, but counterintuitively, the tool ADDS a rule rather than replacing the existing. So I then deleted original Code Editor shortcuts.

    After that, the Code Editor keys worked, but the Ctrl-Up and Down still do nothing if the the cursor is in the worksheet. If I’m in history, it simply scrolls through the list. Ideas?

    1. Jeff, disregard my question. I was running an old version of SQL Developer. I upgraded and the Ctrl-Up and Ctrl-Down work as described.

  9. HI Jeff,

    As you said the the default limit for SQL history is 100. I m not getting the queries that i run yesterday. There are queries available from 31 July 2014 but in the list there only 20 queries. there are only 4 items in the list that were run yesterday.
    Also i have not cleared the Sql history.

    How can i get those missing queries or only of yesterdays missing queries.

    1. thatjeffsmith Post
      Author

      Weird. So you have the sql history panel open – as you run queries, do you see them being added?

      If not, it sounds like the file is bad, and you might need to delete it and force SQL Dev to build you a new one from scratch.

      Before that, make sure you don’t have a filter enabled on your SQL History panel.

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
  10. Is there a way to have the SQL History store more queries?

    It looks like it defaults to hold the last 100 queries executed.

    1. thatjeffsmith Post
      Author
  11. Hi Jeff,
    How on earth can I disable this ‘feature’?

    There is nothing quite like seeing a page and a half long SQL statement disappearing into the void when one accidentally hits CTRL+Arrow

    1. thatjeffsmith Post
      Author
      1. Thank you Jeff, I will certainly remember this tip.

        I have to say that SQL Developer improved by leaps and bounds in the last couple of years.
        The brand name Oracle IDE’s out there should start taking notes.

        1. thatjeffsmith Post
          Author
  12. Hi Jeff,
    great webinar yesterday. thanks 4 that
    when scolling thru the sql history using the keyborad, my sql-developer always appends the sql commands in the worksheet.
    can you tell me how to change the default behaviour of the sql historiy from append to override?

    regards
    peter

    1. thatjeffsmith Post
      Author

Leave a Reply

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