Toad World® Forums

How to format using ALLMAN style

Hi all. I like Toad formatter, but I want to change the way it formats parentheses and semi-colon with SQL Statements. Here is a sample to explain what it current does (after I have fiddled with formatter settings) and what I would like to get (for which I cannot find any settings to affect the change).

Notice please two things: (1) the open/close parens are aligned, 2) the semi-colon that ends the sql is also aligned with the original DML statment on its own line.

How do I get Toad formatter to do this? The first formatting is highly annoying to look at as it makes it very difficult to know where pieces of the statement start and stop.

    PROCEDURE add_tab_privs(grantee_p VARCHAR2)

    IS

    BEGIN

        SELECT CAST(MULTISET(SELECT *

                             FROM (SELECT * FROM TABLE(CAST(o_kev_privilege_set_v.tab_priv AS c_kev_tab_priv))

                                   UNION

                                   SELECT DISTINCT grantee, owner, table_name

                                                 , privilege

                                   FROM dba_tab_privs

                                   WHERE grantee = grantee_p

                                   ORDER BY 1, 2, 3

                                          , 4)) AS c_kev_tab_priv)

        INTO o_kev_privilege_set_v.tab_priv

        FROM DUAL;

    END;



  PROCEDURE add_tab_privs(grantee_p VARCHAR2)

    IS

    BEGIN

        SELECT CAST(

                    MULTISET(

                             SELECT *

                             FROM (

                                   SELECT *

                                   FROM TABLE(

                                              CAST(

                                                   o_kev_privilege_set_v.tab_priv AS

                                                      c_kev_tab_priv

                                                  )

                                             )

                                   UNION

                                   SELECT DISTINCT grantee, owner, table_name

                                                 , privilege

                                   FROM dba_tab_privs

                                   WHERE grantee = grantee_p

                                   ORDER BY 1, 2, 3

                                          , 4

                                  )

                            ) AS c_kev_tab_priv

                   )

        INTO o_kev_privilege_set_v.tab_priv

        FROM DUAL

        ;

    END;

Kevin.

Hi Kevin,

In https://en.wikipedia.org/wiki/Indent_style Allman seems to be defined as something slightly different:

    while (x == y)
{
something();
somethingelse();
}

Whatever it may be, in the Toad formatter options, List Arrangements, List Defaults, if I select Parentheses arrangement option 5 then I’m already getting pretty close to what you want. Or select option 6 for the Wikipedia Allman style.

my_proc (
par1, par2,
par3
)

You must have selected a Tabular list format. If I do this in List Default then I’m getting the following:

PROCEDURE add_tab_privs (grantee_p VARCHAR2)
IS
BEGIN
SELECT CAST (
MULTISET (
SELECT *
FROM (SELECT *
FROM TABLE (CAST (o_kev_privilege_set_v.tab_priv AS c_kev_tab_priv))
UNION
SELECT DISTINCT grantee, owner, table_name
, privilege
FROM dba_tab_privs
WHERE grantee = grantee_p
ORDER BY
1, 2, 3
, 4)
) AS c_kev_tab_priv
)
INTO o_kev_privilege_set_v.tab_priv
FROM DUAL;
END;

The only main difference left is that we don’t treat FROM as a function. SELECT statements have their own format, see “DML statements” tab.

Hope this helps. In case it doesn’t feel free to post you formatter settings (go to the top tab and click on Copy Options to Clipboard), or send me an email at andre.vergison somewhere at Quest.

Thanks,
Andre

Thanks Andre. I will give it a second look. I am pretty sure though that I won’t give me what I want. That is OK though. It is in the grand scheme, a minor annoyance.

Kevin Meade

1.860.886.7403 (Home#) (Has answering machine)

*** Due to a special family need, I will not be available from 7am to 11am Friday (tomorrow). 11am is the worst case scenario. If you have an emergency during my absecence or otherwise cannot wait till my return, please contact my Boss, Tom Hurlebaus and he will redirect you to someone who can assist you.***

  • I am a remote worker. To contact me first look for me online and send me a chat or email. Otherwise feel free please to call 860.886.7403 (home# with answering machine). If I do not respond within 10 minutes or you have an emergency, please call 860.334.6001(my wife’s cell#).*

From: Andre Vergison [mailto:bounce-avergison@toadworld.com]

Sent: Friday, May 12, 2017 3:16 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] How to format using ALLMAN style

RE: How to format using ALLMAN style

Reply by Andre Vergison

Hi Kevin,

In
en.wikipedia.org/…/Indent_style Allman seems to be defined as something slightly different:

while (x == y)
{
something();
somethingelse();
}

Whatever it may be, in the Toad formatter options, List Arrangements, List Defaults, if I select Parentheses arrangement option 5 then I’m already getting pretty close to what you want. Or select option 6 for the Wikipedia Allman style.

my_proc (

par1, par2,

par3

)

You must have selected a Tabular list format. If I do this in List Default then I’m getting the following:

PROCEDURE add_tab_privs (grantee_p VARCHAR2)

IS

BEGIN

SELECT CAST (

         MULTISET (

            SELECT *

              FROM (SELECT *

                      FROM TABLE (CAST (o_kev_privilege_set_v.tab_priv AS c_kev_tab_priv))

                    UNION

                    SELECT DISTINCT grantee, owner, table_name

                                  , privilege

                      FROM dba_tab_privs

                     WHERE grantee = grantee_p

                    ORDER BY

                       1, 2, 3

                     , 4)

         ) AS c_kev_tab_priv

      )

 INTO o_kev_privilege_set_v.tab_priv

 FROM DUAL;

END;

The only main difference left is that we don’t treat FROM as a function. SELECT statements have their own format, see “DML statements” tab.

Hope this helps. In case it doesn’t feel free to post you formatter settings (go to the top tab and click on Copy Options to Clipboard), or send me an email at andre.vergison somewhere at Quest.

Thanks,

Andre

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
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.


Kevin,

won’t give me what I want <

Can you be precise on what exactly? Just one clause + style, not an entire program unit please.

Thanks,
Andre

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):

  1. I want parens to left align, when formatting a SQL statement.

  2. I want the semi-colon at the end of a SQL statement to left align with the initial SQL keyword.

  3. I want to control the indentation of SQL sub-components (one space, two spaces, three spaces).

For example:

with

  abc as (select * from dept d where exists (select null from emp e where e.deptno = d.deptno)) select * from abc;

Becomes:

with

  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:

with

  abc as (

            select *

            from dept d

            where exists (

                            select null

                            from emp e

                            where e.deptno = d.deptno

                         )

         )

select *

from abc

;

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.

WITH abc

 AS (SELECT *

     FROM dept d

     WHERE EXISTS

               (SELECT NULL

                FROM emp e

                WHERE e.deptno = d.deptno))

SELECT *

FROM abc;

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.

Kevin Meade

1.860.886.7403 (Home#) (Has answering machine)

  • I am a remote worker. To contact me first look for me online and send me a chat or email. Otherwise feel free please to call 860.886.7403 (home# with answering machine). If I do not respond within 10 minutes or you have an emergency, please call 860.334.6001(my wife’s cell#).*

From: Andre Vergison [mailto:bounce-avergison@toadworld.com]

Sent: Saturday, May 13, 2017 3:53 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] How to format using ALLMAN style

RE: How to format using ALLMAN style

Reply by Andre Vergison

Kevin,

won’t give me what I want <

Can you be precise on what exactly? Just one clause + style, not an entire program unit please.

Thanks,

Andre

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
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.