We help IT Professionals succeed at work.

looking for guidance on Oracle Sql Formatting standards

Gadsden Consulting
on
237 Views
Last Modified: 2018-02-23
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.
Comment
Watch Question

Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Gadsden ConsultingIT Specialist

Author

Commented:
Ok, thank you Alexander and slightwv.

Specific question, I really don't see the point of putting everyone column on its own row. Once the code is written, the columns become less important as far as readability is concerned. To me it's like writing
like
this.
what's
the
point?

plus it keeps it nice and compact, so then you're thinking about the logic and not scrolling up and down all day.

What do you think?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Gadsden ConsultingIT Specialist

Author

Commented:
very good, slightwv, thanks. I like that approach.
Gadsden ConsultingIT Specialist

Author

Commented:
our code SWAT team will turn back code because there are three spaces for indentation as opposed to the required four. Numerous good people have moved on as a result of this.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>our code SWAT team will turn back code

That is a little OCD but I suppose rulz iz rulz!

Personally I HATE space indentation.  I thought that was what tab stops were invented for!

>>Numerous good people have moved on as a result of this

I can imagine!

I remember back in the college days one teachers aid that graded the instructors code always complained if code wasn't commented enough.

One "enterprising" student had enough and turned in a program that had ZERO white space and if memory serves, was fixed width. so everything was a SOLID block of characters.  He said it was worth the rewrite he had to do!!!
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Decided to play using SQL.

Give them this:
/*Hello*/select/*This*//*will*//*actually*/sysdate/*run*//*from*/
/*sqlplus*/from/*on*//*my*//*setup*/dual/*that*//*should*//*teach*/
/*them!*/;

Open in new window

Gadsden ConsultingIT Specialist

Author

Commented:
slightwv - - - ha ! good ones.
Gadsden ConsultingIT Specialist

Author

Commented:
FYI - still irritated that columns are required one per line. Reading comprehension is left to right, can't really see any benefit with this approach.

I saw a comment yesterday about writing procedures, packages, etc., how it's nice to "package" up small, similar snippets of code for ease in maintainability. The author said he always has the (logical) begin/end of a code block within the size of his monitor.

Compare what I think is readable code to what I'm required to produce;

<my view of readable code - compact but readable, and can be looked at with one glance>
    CURSOR c_rotc_basic is
       select m.id as student_Id, m.student_num, m.last_name, m.first_name, substr(m.middle_name,1,1) as MI,
              case when m.pref_name = m.first_name then null else m.pref_name end as Preferred_Name, m.formatted_name,
              m.gen as Gender, m.class_applied_for as Class_Yr, ru_pkg.STUDENT_CLASS(m.id) as Class, m.itl as Intl,
              lpad(to_char(m.rotc_co_nbr),2,'0') as Co, ms.title as Status, m.rotc_in_brig as In_Brigade, m.proj_grad_date, m.grad_date,
              m.rotc_commission_date, m.rotc_commission_title,
              trunc(months_between(sysdate,m.birth_date)/12) as Student_Age, m.adas_id, m.high_school_source,
              m.high_satv, m.high_satm, m.commission_legal_code, m.former_mil_svc_code
        from rotc_students m
             inner join mid_statuses ms on ms.code = m.mist_code
        where m.mist_in_brig = 'Y';

<the required rigid standard means you're scrolling up and down (and up and down) and not really able to focus on the actual logic, imo>
    CURSOR c_rotc_basic is
       SELECT
              m.id as student_Id,
              m.student_num,
              m.last_name,
              m.first_name,
              substr(m.middle_name,1,1) as MI,
              CASE
                     when m.pref_name = m.first_name
                                then null
                     else m.pref_name
             END as Preferred_Name,
              m.formatted_name,
              m.gen as Gender,
              m.class_applied_for as Class_Yr,
              ru_pkg.STUDENT_CLASS(m.id) as Class,
              m.itl as Intl,
              lpad(to_char(m.rotc_co_nbr),2,'0') as Co,
              ms.title as Status,
              m.rotc_in_brig as In_Brigade,
              m.proj_grad_date,
              m.grad_date,
              m.rotc_commission_date,
              m.rotc_commission_title,
              trunc(months_between(sysdate,m.birth_date)/12) as Student_Age,
              m.adas_id, m.high_school_source,
              m.high_satv,
              m.high_satm,
              m.commission_code,
              m.former_mil_svc_code
        from rotc_students m
             inner join mid_statuses ms on ms.code = m.mist_code
        where m.mist_in_brig = 'Y';

Plus, in regards to my recent question on lines of code, the rigid standard above results in a misleading LINE_COUNT when trying to gauge level of effort.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I find the required format MUCH EASIER to follow.  I believe you will find my fellow DBA's/Developers will as well.

That is how I write my code and how most SQL beautifiers will generate things as well.

When columns are mixed and matched it is really confusing.  What is a column, what is a function, what is a string literal is all jumbled up.

>>the rigid standard above results in a misleading LINE_COUNT when trying to gauge level of effort.

I can make a 1,000 procedure compile on a single line.  What is the level of effort to maintain it?

I already pointed out one reason why lines of code is a bad method:  Comments!

There are several others.
Gadsden ConsultingIT Specialist

Author

Commented:
ok ok ... :-(

but it seems like the scrolling up and down all day long isn't worth it

>>What is a column, what is a function, what is a string literal is all jumbled up.
- ok, then do a separate line when it's a function or sting manipulation or case statement. But one line for straight columns? Not buying that.

>>I can make a 1,000 procedure compile on a single line.
- understood, I'm not advocating that. Readability is key. With modifications to my code to put string manipulation etc on its separate line, I'd say it's readable.

>>one reason why lines of code is a bad method:  Comments!
- yes, agree, but I wouldn't say it's a bad measure, it's like sailing - it's one measure that has some value.

but thanks for the discussion!
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>but thanks for the discussion!

Any time!  We're always here.