troubleshooting Question

looking for guidance on Oracle Sql Formatting standards

Avatar of Wm Peck 1958
Wm Peck 1958Flag for United States of America asked on
Oracle DatabaseSQL
13 Comments3 Solutions270 ViewsLast Modified:
I'm looking for some general guidance on Sql formatting, in PL-SQL, views, etc.

Here in our Nazi-driven shop, the standards are, shall we say, "exacting". I'd like something in between - standardized, readable, but not to the point of straightjackets.

One of my primary arguments for compact code is that IT SAVES TIME from scrolling up and down ALL DAY LONG. PLUS - it increases comprehension, productivity, etc. and MOST OF TIME TIME you're not really focusing on the columns, you're focusing on the logic (WHERE ...)

So I'm looking for standards but also the *reason* for the standards.

In our shop it's one line for SELECT, FROM, WHERE, ORDER BY, etc. That drives me nuts, because it's unnecessary, in my (not so) humble opinion. I can't see any good reason for it. When you're looking at code ALL DAY LONG, it doesn't help to put the keywords ON THEIR OWN LINE. The eye/brain is quite capable of processing these words which are ALWAYS ON THE LEFT and then if your other code is reasonably formatted, you save time and aggravation.

Also, I find it unnecessary to put EVERY COLUMN in the select on its own line, again because it takes up space and causes unnecessary scrolling up and down, and loss of "context" of the code.

Also, on simple case statements, it seems reasonable to put on one line, e.g.,
case when ac.Embark_Dt is not null then ac.Embark_Dt else ay.start_date end as Embark_Dt,

Any developer beyond the newbie stage can read this without much difficulty at all.

Also, thoughts on UPPERCASE vs lower, mixed case. Again, I fail to see why keywords in UPPERCASE help all that much. With tools like PL-SQL Developer, they're highlighted anyway, plus anyone past the newbie stage KNOWS WHATS GOING ON.

I think Right-Align is goofy too, although I do like alignment. for example,
  SELECT   last_name, first_name
    FROM   employee
WHERE     department_id = 15
     AND     hire_date < SYSDATE;

I think is better as left-aligned
SELECT     last_name, first_name
FROM       employee
WHERE     department_id = 15
AND          hire_date < SYSDATE;

minor difference but to me your brain skips by those words anyway, they don't need to be thrown in your face as you're reading the logic.

Here's some compact code which to me is better, because MOST OF THE TIME you're really not scanning the columns, you're looking at the WHERE logic. So, to me, it's a WASTE OF TIME scrolling up and down all day long, plus you miss "seeing the picture" because of the incessant scrolling.

COMPACT CODE
select owner c1, object_type c3, object_name c2
from dba_objects
where status != 'VALID'
order by owner, object_type;

much better than the Ask Tom long, drawn out code
select
         owner c1,
         object_type c3,
         object_name c2
from
         dba_objects
where
          status != 'VALID'
order by
           owner,
           object_type;

if you have 20+ columns, you're wasting a lot of space and scrolling as opposed to thinking.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 3 Answers and 13 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros