ThatJeffSmith

7 Ways to Avoid SELECT * FROM Queries in SQL Developer

Tell Others About This Story:

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.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Similar Posts by Content Area: , , ,