I said there were 8 ways to avoid using SELECT * FROM queries in SQL Developer – but I didn’t actually count them. So let’s do that now.

But wait, what’s wrong with SELECT * FROM queries?

  • you don’t need all the columns
  • columns can change
  • columns can be added
  • columns can be removed

At some point, your application (or report) will ‘break.’

So, how do we avoid this? Let’s count the ways.

Drag and Drop from the Tree

Click vs type, do whatever is faster for you.
Click vs type, do whatever is faster for you.

I like this one a lot, the order in which I select the columns in the tree is the order in which they’ll be inserted into the worksheet.

Drag and Drop from a DESC Window

SHIFT+F4 – table pops up. Select one or more columns, and drag them in.

The DESC window is 'Stay-On-Top' so you can keep an eye on it whilst you do other things in the main SQLDev window.
The DESC window is ‘Stay-On-Top’ so you can keep an eye on it whilst you do other things in the main SQLDev window.

Query Builder

Click on the columns you want. We write the query for you.

Tuning Tip: Query only what you need.
Tuning Tip: Query only what you need.

Code Insight

One or more fields, we can even alias them for you auto-mmagically.

The columns will be added to the query in the order as shown in the popup window.
The columns will be added to the query in the order as shown in the popup window.

SQL Text Expansion

A new Database 12c feature – get the REAL SQL behind your query – very helpful when dealing with VIEWs.

Put your mouse under the SELECT...get the real SQL.
Put your mouse under the SELECT…get the real SQL.

SUPER DUPER TRICK: Did you know you can change the parser squiggly line helper marks?

SELECT * Peeking/Replacement

Place your mouse over the asterisk, we’ll show you the columns underneath. If you click on them, we’ll replace it for you.

Don't click, just mouse hover to activate the tip
Don’t click, just mouse hover to activate the tip

Just take the time to type it out

Don’t laugh. Using tools has a tendency to make us code FAST. Which is good, unless you need to think something through. So while these other methods are ‘wicked-awesome’ – don’t forget about the tried and true ways…like copying and pasting code you’ve already written 🙂

There is a number 8!

Oracle ACE Sabine reminds me of this final method – copying one or more column headers from your query result grid. So if you have the text already there, you can simply grab it.

Right mouse on the column headers. not in the grid.
Right mouse on the column headers. not in the grid.
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.

8 Comments

  1. Brandon Mason Reply

    Just shared the “hover hint” at a local user group. I was surprised how many people didn’t know about it. Great tips, thanks!

    • Brandon Mason

      Also, I’m glad you recognized me as “The Cowboy” (who isn’t really a cowboy)…

  2. Jeff!

    I’ve been using SQL Developer off and on for years but just recently started running into more complicated needs and I’m hoping you can point me in the right direction.

    My PL SQL knowledge is below average and I’m looking for a place to learn but I’m in need of training in the real world, on live databases where I’ve only been given read-only access. Until now simple functions like “With” & “Case” have provided my users what they need but I’d like to know what other options are available to a restricted application developer that isn’t in the IT clan?

    • Go get yourself this – and then you can do anything you want.

      Also go check out the tutorials on LiveSQL – I talk about that here.

    • Thank you so much!

      This does consolidate and make the learning process much more efficient. I had to pull much of this together separately over time and I really never mastered getting it all working 100%. So I greatly appreciate the work you’ve done here.

      I was hoping however… I am in need of narrowing the playing field a bit. Is there a resource for “read-only” SQL or PL/SQL that will help me? There seems to be a lot that can be done with the “Create” function but it’s not something I can use at the moment and I need to focus on learning what I can do to support my users first.

  3. Some great tips in here, Jeff!
    I really like that “peeking/replacement” feature. I’ve never used it but I often write SELECT * in a Dev environment to have a look at the table. So I think I’ll use this peeking feature quite a lot now!

  4. Parley Kennelly Reply

    “A new Database 12c feature – get the REAL SQL behind your query – very helpful when dealing with VIEWs.”

    May be reason enough to upgrade.

Write A Comment