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.

Still good, even in 2020!

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.

If you mouse hover over the Query Result panel tab, SQL Developer will tell you the query used to populate that 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.

Unshared? What is that again?

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.

The pin is pushed – the editor is now ‘frozen’

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

Tab Groups are shown concurrently in the SQL Developer desktop display

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!

Look for stuff in your database – click the binoculars/search button on the main toolbar.

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.

Faster Query Builder

There’s the * expansion feature.

Mouse over the ‘*’ to see the list of column. Click on them to replace. Ctrl+Z to get your * back.

There’s the SQL Text expansion feature.

oracle sql developer database 12c sql text expansion
Put your mouse under the SELECT…get the real SQL. You need to be on 12c and higher to use this one.

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.

Click, click, click, drag, drop. Done.

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

And finally there’s Code Insight.

The columns will be added to the query in the order you select them in the popup window.

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!

Make this bigger for other folks in the room – or maybe you’re taking screenshots for tech papers, blogs, etc..or just getting old like me 🙂

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.

Sold yet?

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.

I know, I know, I forgot your FAVORITE ONE. But, you already know your favorites, yes?

9. Recognize repetitive action, automate them!

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

This is ACTUALLY 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!

Wait Jeff, you HATE SELECT * FROM queries!

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?

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.

This is the newest one, json-formatted.

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.

Don’t like, or even HATE, these keyboard shortcuts? Change ’em.

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

What did we run, with what :BINDs, how long did it take to run?
thatjeffsmith
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

5 Comments

  1. Java’s not on my path or anywhere I can locate it (Start Menu or Explorer) but the Help – About dialog did it – thanks again Jeff!

    Java(TM) Platform 1.8.0_212

    • thatjeffsmith

      yeah that’s not bad, fairly recent. We’ll require at least 1.8.0_175, give or take with version 20.2

    • I’m probably beating a dead horse but I found my Java path via Help – About Properties “Name” java.home and was able to run java -version after cd’ing to the “Value” C:\Users\bob\Oracle\sqldeveloper\jdk\jre\bin I found there.
      (and my apologies for breaking threading on my replies!)

  2. These tips are great, thanks Jeff. Re: #6: how can I determine my Java version on Win10? Google searches couldn’t help me!

    • thatjeffsmith

      open a CMD prompt

      run java -version

      if you’re running SQL Developer, check the Help – About dialog, then see the Version page.

Write A Comment