24 Basic Tips on Using SQL Developer

I have been using SQL Developer since it required some goodwill to work. Some of the motivation came from an annoyance with Toad sessions in the database. I remember that I back then googled "how to block Toad users from the database".  

Anyway, these days most developers around me are using SQL Developer. But quite a few are learning only the least to get their job done. The following list is what I use often, and some of it results in "How did you do that?" when they are looking over my shoulder. The shortcuts here refer to the Windows version since that is what I'm currently using at work. You can easily remap them from Preferences.

1. Use templates from preferences to create shortcuts to frequently used SQL statements, or fraction of it. You'll find it under Database - SQL Editor Code Templates
2. Speaking of preferences, there are lots of stuff to tweak, if you are looking for something special use the search field in the upper left corner
   
3. Templates are useful, so are snippets which can be anything from functions and hints to small scripts. Check out View -> Snippets, and when you see something cool, just drag it into the editor window.
4. Drag the table from the list of tables in the Connections window to the worksheet, and it will offer to generate SQL for you (INSERT, SELECT, etc).
5. Copy rows from Query Result with Shift-Ctrl-C to get the column headers. Paste it directly into Excel or in Outlook Email. In the latter you can use the "Convert to text" function to get a nice looking table in a few steps.
6. Attach shortcuts to worksheets with Alt-Shift-number (1-9). Switch between them with Alt-number
7. If you have used one of those tools that requires you to mark the statement you want to execute, you don't have to do this with SQL Dev; with the cursor somewhere on the statement just hit Ctrl-Enter.
8. To convert text to and from upper, lower, or initcap, mark the text and hit Ctrl-' (this one was not set on my Mac version, if that is your case, go to Preferences, search for shortcut, and in that window search for 'other'). Repeating it will cycle through upper, lower, and initcap.
9. How to insert a list you receive into a table: Say someone sends you a list of 10 rows in an email, go to the Data tab for the table and add 10 rows. Copy the rows from the email and put the cursor on the first row in the data tab, and paste them in with Ctrl-V. SQL Dev will paste them in as you expect in Excel. This even works with two or more columns if the columns are separated with tabs. 
10. Using Subversion (or Git) from SQL Dev saves you a mental context switch compared to using an external client. By opening SQL scripts in SQL Developer, edit, save, and commit them (to the source control system) your work flows better.
11. Reformat code with Ctrl-F7. Makes it easier to read when you have copied and pasted generated code from somewhere.
12. Get instant info about a table. Put the cursor on the table name in the worksheet and hit Shift-F4. A window pops up with lots of vital information, and it is quicker than to search for it in the hierarchy. 
13. Hit Ctrl-G to go to a specific line in your editor window. 
14. After you have become more productive and live your life in SQL Dev, it may need more memory.  See this post for details: http://www.thatjeffsmith.com/archive/2014/06/configuring-memory-usage-in-oracle-sql-developer/ In short, uncomment the line in product.conf that starts with AddVMOption, and change the value from 800m (default) to say 1024m. 
15. Save query output for future reference by using the red pin in the Query result window. You can go back and see what SQL produced it by clicking the blue "SQL".
16. When you look at the indexes for a table in the Pop-up info box (Shift-F4), you can open the declaration for the index by right-clicking on a row for an index, and choose Go To...
17. Right-click on an editor tab and choose Float. It will move the worksheet tab to a separate window. Then you can click on other tabs and drag them to that window and group connections that belong together.
18. Change the DATE-format in the Preferences, the default format yyyy.mm.dd is usually not enough for me, so I add HH24:MI:SS 
19. DBAs and others often work on many tasks at the same time, some scripts or SQL commands take long time to execute. Instead of checking back once in a while for its completion open the Task Progress from the View menu. It will show tasks that are running, completed or failed. Click the icon on the right side in order to remove finished tasks.
20. SQL History (F8) is useful, even as backup of previous complex SQL that you have been working on, but didn't save before the crash...
21. Use Connection Color when setting up a connection towards a production database. I use red and it will add a discrete line around the edge to remind me not to do something stupid. This is much better than having the entire background bleeding when doing serious business.
22. Hit F10 to get the execution plan for the statement where your cursor is.
23. If you need to get to your database through a jump server, see my blogpost about it.
24. Code completion is very useful, but sometimes it doesn't work. There is a limit to how much processing and parsing SQL Developer can do before your patience runs out. It helps to at least finish previous statement with a semi-colon or something that clearly marks the start of next statement, and write correct SQL.

There is a lot smartness built into the tool, sometimes a bit hidden; to not get in your way. If you think SQL Developer can't do something, just try it out. So far I have never received any obscene error messages.