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.
@MarkusHohloch @tobias_arnhold there's about 8 ways to get columns to your query
— OracleSQLDeveloper (@OracleSQLDev) November 23, 2016
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
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.
Query Builder
Click on the columns you want. We write the query for you.
Code Insight
One or more fields, we can even alias them for you auto-mmagically.
SQL Text Expansion
A new Database 12c feature – get the REAL SQL behind your query – very helpful when dealing with VIEWs.
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.
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.
8 Comments
Just shared the “hover hint” at a local user group. I was surprised how many people didn’t know about it. Great tips, thanks!
Also, I’m glad you recognized me as “The Cowboy” (who isn’t really a cowboy)…
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.
If you’re on Database 12c – you can use inline PLSQL with your SQL using a WITH clause – no need to be able to create stored procedures
here’s a nice writeup on that
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!
“A new Database 12c feature – get the REAL SQL behind your query – very helpful when dealing with VIEWs.”
May be reason enough to upgrade.