Top 10 SQL Developer Tips for 2020

I love tips, and I love lists. I’ve written a top 10 tips post, but it was way back in 2012. And those tips are still good, but the software and my perspective have definitely changed since then.

So what do you really need to know, to really get the most out of your favorite Oracle Database IDE and GUI? Let’s jump in and get you rocking and rolling!

1. Running multiple queries

There’s two takes on this:

• sequentially
• concurrently

Sequentially, you can select as many queries as you want, and hit ctrl+enter…and we’ll run each and throw each result set into a grid.

Concurrently, you can have SQL Developer run 2 or more queries at the same time. Each query will need its OWN connection.

So how do you do that?

Ctrl+Shift+N will open a new ‘DEDICATED’ connection to your database, so you can run your next query. OR you can use this button on the worksheet.

2. Working with multiple things

Maybe you want to work with two tables, or a table and a stored procedure, or a report, a query, and a table.

You can have as many editors open at a time as you want. You’ll want to mind the ‘Pins.’ Say, when you open one table, you need to ‘pin’ it, before you open the second table…otherwise you’ll just have table one close to make room for table two.

But, if you want to SEE these things, all at once, then you need to learn how to master Tab Groups.

3. Searching stuff

Yes, the Editors support Search and Replace. But, what if you need to FIND something in your database? Hit the binoculars button!

4. Getting help with SELECTs

There’s the Query Builder – don’t laugh! You can use it to do a lot of cool stuff, like generate aliases automatically for your columns when joining multiple tables.

There’s the * expansion feature.

There’s the SQL Text expansion feature.

There’s drag and drop.

You can drag and drop from the tree, you can drag and drop from grids…both will give you comma separated lists of values. Useful for SELECT and WHERE IN (…) lists.

There’s refactoring…maybe you want an ANSI Style SQL Join, or an Oracle Style? Watch the movie.

And finally there’s Code Insight.

Master all these things, and you’ll be doing a lot less typing, and making a many fewer mistakes.

5. Make it look ‘pretty’

Pick a font, a code editor style to you liking. If you’re experiencing eye strain, bump up the FONTs!

6. Mind your versions

Try to stay a release or 3 within the latest and greatest. So right now 19.4 is the latest, and we’re about to release 20.2. You probably don’t want to be running anything older than 19.2.1.

AND, you need to keep your Java up to date too! More recent versions of Java will be more secure AND faster! Java 8 update 200+ or Java 11 update 7 are good places to start.

7. Jump to the cmd prompt or shell as needed

Some things are just easier in a bash terminal. And we’ve given you everything you like in SQL Developer in a more modern SQL*Plus, it’s called SQLcl.

8. Master those keyboard shortcuts

Even if you’re in a GUI, you’re still going to be using your keyboard a lot. Avoid the mouse, and you could save yourself even more time. Learn these.

9. Recognize repetitive action, automate them!

Running the same query over and over again? Save it as a report!

Or maybe it’s just a bit of code you find yourself adding to your SQL or PL/SQL? If so, save it as a Code Template!

10. Always be learning

You don’t know what you don’t know, that’s so often our biggest problem in life…not just tech. If you find something that’s harder than it should be, ask yourself – there must be a better way?

This is your cue to pick up the Google machine.

Or cry for help on StackOverflow or Twitter or the Forums.

There’s a great chance someone else has already figured out your issue! You just need to ask the question!

Isn’t right right, Martin?

How have I used SQL*Developer for so long and not known you can open a new, unshared connection with CTRL-SHIFT-N, so you can run two queries against the same DB at the same time?@thatjeffsmith

— Martin Widlake (@MDWidlake) June 23, 2020

Bonus Tip #1 – Formatting Hints

I led off this post of a screenshot of this feature. You can tell SQL Developer in advance that you want your query results to come back as CSV, HTML, JSON, and more.

You can use SET SQLFORMAT or the /*format*/ comment directly in your query, then run through the script engine.

Bonus Tip #2 – SQL History & Statement Logging

We record all of the code you execute in a worksheet. You can see it in the SQL History panel, or you can use the keyboard to recall it.

But, we also LOG all the SQL and PL/SQL we run across your Oracle connection. Great for debugging or ‘borrowing’ our code.