Thanks Andre, I hope I am not too wordy here.
If I were to break it down to the simplest example, I could live easily with three things, in order of importance to me (not that I am anyone special):
I want parens to left align, when formatting a SQL statement.
I want the semi-colon at the end of a SQL statement to left align with the initial SQL keyword.
I want to control the indentation of SQL sub-components (one space, two spaces, three spaces).
abc as (select * from dept d where exists (select null from emp e where e.deptno = d.deptno)) select * from abc;
abc as (
select * from dept d where exists (
select null from emp e where e.deptno = d.deptno
select * from abc
and (when other formatting options already set in Toad are applied too) this:
abc as (
from dept d
where exists (
from emp e
where e.deptno = d.deptno
Notice, that parens align to make it easy to see the nested sections of code, due to the newline and alignment and indenting. And the semi-colon clearly denotes the end of the SQL by acting as a visual end of the block of code.
Notice also please, that this is much harder to see when aligned using Toad out of the box, particularly with the dangling placement of final parens and the semi-colon. Parens and terminator character should be visually strong in providing their respective clues as to the composition of the statement and its ending. The highlighted parts in yellow below (parens and semi-colon) are massively distracting given their placement, because they hide the nesting structure of the code when compared to Allman style. Sitting here alone it may not seem so bad, but when this SQL is nested inside a PL/SQL piece of code alone side 50 more DML, it gets hard to easily see the start and stop of queries, and their nested components. I formatted the below with my current Toad settings. It is close to what I want, except for parens, newlines, and terminator character.
AS (SELECT *
FROM dept d
FROM emp e
WHERE e.deptno = d.deptno))
Some might argue that refactoring of PL/SQL with 50 DML in it would be in order and they might be right. But that observation does not help me. We outsource a large amount of development, and 5000 line packages and 300 line SQL is normal for us. So I need my tools to help me understand what I have, not what I should have, and anything that makes nesting and start/stop delineations of code blocks easier to see, does that.
I am one of the Oracle Grey Beards. I first typed SELECT 8 FROM EMP into SQL*Plus back in 1984 (I fat fingered the shift key). Having been using Oracle for 33 years, I admit my Toad skills are lacking; (old habits die hard), my favorite editor is still notepad. BUT… having to help 300+ developers overseas every day, I am REAL GLAD that everyone else uses TOAD heavily. I don’t want you to think I am down on Toad. It is the best tool of its kind.
1.860.886.7403 (Home#) (Has answering machine)
From: Andre Vergison [mailto:email@example.com]
Sent: Saturday, May 13, 2017 3:53 AM
Subject: RE: [Toad for Oracle - Discussion Forum] How to format using ALLMAN style
RE: How to format using ALLMAN style
Reply by Andre Vergison
won’t give me what I want <
Can you be precise on what exactly? Just one clause + style, not an entire program unit please.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Unsubscribe from Toad for Oracle Forum notifications altogether.
Toad for Oracle - Discussion Forum
this post as spam/abuse.
This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies.