Beautifier enhancements

[Ed Holloman]

Hi,

I'd like to note that the features of the beautifier have improved markedly from 5.x to 6.x. I use it all the time and it's an extremely useful feature (version 6.0.5.931).

I have a few requests (challenges) for the code beautifier, which I think the following examples illustrate.

In addition to the following examples, would it be possible to have the option to make any declared constants appear as all caps when beautified?

Thanks for all the improvements.

Ed Holloman

-------------------------------------------------------------------------------
DECLARE

   v_select   VARCHAR2(200);
   loc_tab    my_tt; -- nested table of object type my_ot

BEGIN
   -------------------------------------------------------------------------------
   -- string representation of select statement
   -- current beautifier result
   v_select := 'SELECT a.col1,' || '       b.col1,' || '       c.col1' ||
               '  FROM tab1 a,' || '       tab2 b,' || '       tab3 c' ||
               ' WHERE a.col1 = b.col1' || '   AND b.col1 = c.col1';

   -- preferred beautifier result (controlled by addition of '--' comment characters at end of each line)
   v_select := 'SELECT a.col1,' || --
               '       b.col1,' || --
               '       c.col1' || --
               '  FROM tab1 a,' || --
               '       tab2 b,' || --
               '       tab3 c' || --
               ' WHERE a.col1 = b.col1' || --
               '   AND b.col1 = c.col1';

   -------------------------------------------------------------------------------
   -- collections, bulk collect into
   -- current beautifier result
   SELECT my_ot(col1, col2, col3, col4) BULK COLLECT
     INTO loc_tab
     FROM my_table;

   -- preferred beautifier result; a little more readable
   SELECT my_ot(col1, col2, col3, col4) --
   BULK COLLECT
     INTO loc_tab
     FROM my_table;
   -------------------------------------------------------------------------------
   -- decode, nested CASE statements
   -- Most of the time I like to have items one item per line (select list, table list in FROM clause, etc.) for readability
   -- The notable exception is the decode() statement, which if one item per line, takes up more vertical space
   -- than I'd like
   SELECT decode(a.col1,  -- current beautifier result
                 'val1',
                 1,
                 'val2',
                 2,
                 'val3',
                 3,
                 'val4',
                 4,
                 'val5',
                 5,
                 'val6',
                 6) sort_order,
          decode(a.col1, -- preferred beautifier result
                 'val1', 1,
                 'val2', 2,
                 'val3', 3,
                 'val4', 4,
                 'val5', 5,
                 'val6', 6) sort_order2,
          CASE -- single level CASE just fine
             WHEN b.col1 > 0 THEN
              'valid'
             ELSE
              'not valid'
          END a,
          CASE -- current beautifier result - nested CASE;  difficult to read
             WHEN b.col1 > 0 THEN
              CASE
             WHEN b.col1 < 10 THEN
              'range1'
             ELSE
              'out of range'
          END ELSE 'not valid' END b,
          CASE -- preferred beautifier result - nested CASE
             WHEN b.col1 > 0 THEN
                CASE
                   WHEN b.col1 < 10 THEN
                    'range1'
                   ELSE
                    'out of range'
                END
            ELSE 'not valid'
         END c,
     FROM tab1 a,
          tab2 b,
          tab3 c
    WHERE a.col1 = b.col1
      AND b.col1 = c.col1;

   -------------------------------------------------------------------------------

END;
-------------------------------------------------------------------------------

 

[Marco Kalter]

I have added your suggestions to the list of enhancement requests.

 

[Theo van Vroenhoven]

Hello,

I have some suggestions too.
See the test package below:

create or replace package beautifier_test is
  procedure one_param(p_param1 in varchar2);
  /* Should be:
    procedure one_param
    ( p_param1 in varchar2
    );
  */

  procedure two_params
  (
    p_param1 in varchar2
   ,p_param2 in varchar2
  );
  /* Should be:
    procedure two_params
    ( p_param1 in varchar2
    , p_param2 in varchar2
    );
  */
end beautifier_test;
/
create or replace package body beautifier_test is
  /* Should be:
  cursor c_cur
  ( v_param1  in varchar2                -- Left align parameters
  , v_param10 in varchar2
  ) is
    select tab1.column1
    ,      tab2.column1                  -- Align commas
    ,      case when tab1.column10 = 1
                and  tab2.column2  = 2   -- Align AND and =
                then 3                   -- then on new line; align with when
                else 4                   -- else on new line; align with when
           end                           -- end aligned with case
    from   table_1 tab1                  -- align table with columns
    ,      tab_2   tab2                  -- left align commas, tables and aliases
    where  tab1 in (select 1             -- select on same line
                    from   dual          -- align from with select
                    where  1 = 2
                    and    (  a1  = 3      -- add spaces to align a1 with b12 and to align =
                            or b12 = 4     -- or on new line
                            or b2  = 5     -- align =
                           )               -- align with opening parenthesis
                   )                       -- align with opening parenthesis
  ;
  */
  cursor c_cur(v_param1 in varchar2, v_param10 in varchar2) is
    select tab1.column1
          ,tab2.column1
          ,case
             when tab1.column10 = 1
                  and tab2.column2 = 2 then
              3
             else
              4
           end
    from   table_1 tab1
          ,tab_2   tab2
    where  tab1 in (select 1
                    from   dual
                    where  1 = 2
                    and    (a1 = 3 or b12 = 4 or b2 = 5));

  /* Should be:
  procedure no_param
  is                    -- IS on new line
  begin
    null;
  end;
  */
  procedure no_param is
  begin
    null;
  end;

  /* Should be:
  procedure one_param
  ( p_param1 in varchar2  -- Left align parameter
  ) is                    -- Closing parenthesis on new line
  begin
    null;
  end;
  */
  procedure one_param(p_param1 in varchar2) is
  begin
    null;
  end;

  /* Should be:
  procedure two_params
  ( p_param1  in varchar2
  , p_param10 in varchar2  -- Left align comma AND parameter
  ) is
  begin
    null;
  end;
  */
  procedure two_params
  (
    p_param1  in varchar2
   ,p_param10 in varchar2
  ) is
  begin
    null;
  end;

  procedure comments is
  begin
    /* The closing tag of this comment is at the end of
    the last comment-line.
    This will unindent the comment-lines.             */
    /* The closing tag of this comment is on a new line.
       This will not unindent this line of the comment
    */
    null;
  end;

  /* Should be:
  function fun_no_param
  return varchar2           -- return clause on new line
  is                        -- is on new line
  begin
    return 'x';
  end;
  */
  function fun_no_param return varchar2 is
  begin
    return 'x';
  end;

  /* Should be:
  function fun_one_param
  ( p_param1 in varchar2
  ) return varchar2
  is
  begin
    return 'x';
  end;
  */
  function fun_one_param(p_param1 in varchar2) return varchar2 is
  begin
    return 'x';
  end;

  /* Should be:
  function fun_one_param
  ( p_param1  in varchar2
  , p_param10 in varchar2
  ) return varchar2
  is
  begin
    return 'x';
  end;
  */
  function fun_two_param
  (
    p_param1  in varchar2
   ,p_param10 in varchar2
  ) return varchar2 is
  begin
    return 'x';
  end;

end beautifier_test;
/

It would be nice if these options could be included in a future version.

Thanks in advance!

Theo

 

[Makker]

What I also would like is that

l_string := 'Aap' || 'Noot' || 'Mies';

would become

l_string := 'Aap'
         || 'Noot'
         || 'Mies';

So 'concat'-operator aligned with eachother and the 'become' operator. That way the make-up of a string can be reviewed quicker.

Regards,
Martien

 

[Marco van der Linden]

Funny. I always like to right allign my concatenations like:

l_string := 'Aap' ||
              'Noot'||
              'Mies';

 

[Marco Kalter]

And I like to write:

l_string := 'AapNootMies';