I'd probably make a recommendation of this for your code:
CURSOR c_data IS
FROM cpp c
ORDER BY LENGTH(c.cpp);
We're only using one table/view,
cpp so aliasing it isn't strictly necessary, but it would be helpful if in future another table is added to this query.
Particularly with 2+ table queries, if the names in the SELECT are not fully qualified (mention a table name or alias before the column name) then queries can break if columns are added to tables, that have the same name as another column in another table:
PersonTable: id, name, status, addressid
AddressTable id, street
--this is ok for now
SELECT name, status, street FROM person INNER JOIN street ON person.addressid = address.id
If we add a Status column to Address, the above query fails because it's now ambiguous. Adding a column that causes this kind of problem isn't going to make the DB announce "I added it, but by the way all these other queries throughout your schema and dependent systems are now gonna fail" - you'll just have to find those failing queries by testing. Or customers complaining ;)
If we'd fully qualified it:
INNER JOIN adress a ON person.addressid = address.id
It would carry on working..
Repeating the table name with the columns is a bit tedious and wordy. Using a shorter name not only improves the readability..
INNER JOIN address a ON p.addressid = a.id
..but psychologically reminds us that we can join tables in multiple times for different reasons:
a_work.street as workstreet,
a_home.street as homestreet
INNER JOIN address a_work ON p.work_addressid = a.id --note, i've silently upgraded Person here to track two addresses
INNER JOIN address a_home ON p.home_addressid = a.id
All in, SQLDeveloper is trying to do a good thing here in that it's moving towards the good sense of:
- Give your tables a sensible, readable, contextually relevant alias
- Always fully qualify your column names with the alias
Where it's falling down is:
- It's picking a crap alias name
A1, that doesn't help you remember anything; it's no accident that I picked
p for person and
a for address, as I'm sure you can appreciate. If there was a Party, and a Project to join in I might use
par. I'd avoid
pr because Person, Party and Project all have
r as relevant initial consonants in the word, so
pr doesn't shout "it's an alias for PRoject" as obviously as using three letters does (but I would certainly accept you arguing for
pr if you want to save a few keystrokes :) )
- It's blindly ramming double quotes in everywhere, probably "for safety" but also because it's the path of least resistance - It's a lot easier to code a logic of blindly adding quotes like
builder.addcolumn( '"' || alias_name || '"."' || col_name || '",') than it is to inspect a column name and see if it might need quoting and only add them if required. This unfortunately means the code ends up an unreadable mess of
- .. and leading on from that "just blindly quote everything" is "and then make all the identifiers ALL CAPS, because right now the table/col names are not case sensitive.
SELECT pErSon.NaME ... is fine; even though the table.column is just PERSON.NAME it isn't case sens.. But when we've blindly added quotes, we then absolutely have to put the names in all caps, because adding the quotes makes them be treated in a case sensitive way!
SELECT "pErSon"."NaME" just won't work, so your carefully written out, and beautifully readable* lower case identifiers are gone..
SQLDeveloper really could go to all that introspection and logic of working out what needed to be quoted, whether its because of funky chars, spaces, case sens etc.. But it doesn't - it's taking safe and simple to code approaches like "just quote it", "just uppercase it" and "just create an alias that is some random/incremental unique thing" and that's unfortunately a bad recommendation, though the spirit of some of what it's trying is a good idea
*as kids we learn lowercase before uppercase; we're always faster at reading lowercase sentences than uppercase ones