Formatting. Another Comments

Toad for Oracle - Beta
1

Before formatting:

select *
from dual -- Comment1
where x = x -- comment 2
and x = x -- comment 3 
and x = x; -- comment 4

After formatting:

select *
  from dual                                                                                                  -- Comment1
 where X = X                                                                                                -- comment 2
             and X = X                                                                                      -- comment 3
                       and X = X;           -- comment 4
2

You could uncheck option Formatter -> Oracle Formatter Options -> Comments -> Align the right margin, but that would still give you the following:

SELECT *
  FROM DUAL -- Comment1
 WHERE x = x -- comment 2
			  AND x = x -- comment 3
 					  AND x = x;
		   -- comment 4

The thing is that formatter want to obtain ...

SELECT *
  FROM DUAL
 WHERE x = x AND x = x AND x = x;

... but the end-of-line comments are in the way. So the next best thing it can do is to output them and continue the actual code on the next line.
One could think of moving the comments forwards or backwards but I guess that might open a can of worms.

Andre

3

I pasted your text and then formatted it. I did not get what you did, but what I was expecting

image

4

My options:
FmtPlus.opt.txt (1.2 КБ)

Before formatting:

select X, X
  from dual
 -- cmt1
 where X = X and X = X -- cmt2
    --cmt3
    or X = X 
    -- cmt4
    and X = X;

After formatting:

select X, X
  from dual
 -- cmt1
 where X = X and X = X                                                                                           -- cmt2
    --cmt3
    or X = X -- cmt4
             and X = X;

OK, but 2 last lines.

5

Ihor, if you remove all comment lines then formatter will output this way:

select X, X
  from dual
 where X = X and X = X
    or X = X and X = X;

If formatter want to output on a single line, say

or X = X and X = X

then any single-line comment "breaking" that line will cause the part of the line following that comment to be output on a next line (of course!) :

    or X = X                --cmt
             and X = X;

A comment line between two entire output lines won't affect things:

select X, X
  --cmt
  from dual
 --cmt
 where X = X and X = X
    --cmt
    or X = X and X = X;

All the above is by design. Comment lines are not part of the actual formatting, they are rather retrofitted. And single-line comments require a new line ending so that's an annoying constraint.

Block comments /*...*/ would be a workaround. What I DO see however is that block comment lines also seem to break output lines in the middle:

 where X = X /*cmt*/
             and X = X

This is not by design, there is no reason for this to happen, so that looks like a flaw.
I'll try to find a fix, but it's not simple as many combinations of comments and code tokens are possible.

6

Thank You, @avergison

7

I added a bit of code to align embedded single block-style comments /*....*/ in between the code tokens. It will be on by default, but there is an option to turn it off. (ref QP-3916)

image

Here are a few example (snippets), each time with option OFF and ON:

-- OFF

      g_datatypes_by_name (
         'Variant') /* Family placeholder */
                    :=
         '{0F2EC0D8-4A1B-41E7-956D-7BB186E6EA16}';

-- ON
      g_datatypes_by_name (
         'Variant') /* Family placeholder */ :=
         '{0F2EC0D8-4A1B-41E7-956D-7BB186E6EA16}';

-- OFF

   TYPE type_attributes_aat
      IS TABLE OF all_type_attrs%ROWTYPE
      INDEX BY PLS_INTEGER /* Order of attributes */
                          ;
-- ON

   TYPE type_attributes_aat
      IS TABLE OF all_type_attrs%ROWTYPE
      INDEX BY PLS_INTEGER /* Order of attributes */;

-- OFF

CREATE VIEW v
(
   aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
)
AS
   SELECT x                           /*Igor: to avoid a syntax error , y, z*/
            FROM t;

-- ON

CREATE VIEW v
(
   aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
)
AS
   SELECT x /*Igor: to avoid a syntax error , y, z*/ FROM t;

-- OFF

BEGIN
    p4 (a,         /*embedded*/
          b);
END;
/

-- ON

BEGIN
    p4 (a, /*embedded*/ b);
END;
/

-- OFF

DECLARE
   a   VARCHAR2 (7) := 'too long';
   b   VARCHAR2 (7) := 'too long';

   c                                                                  /*2804*/
       CHAR (7) := 'too long';
   d                                                                  /*2804*/
       CHARACTER (7) := 'too long';
   e   VARCHAR2 (8) := 'too long';
   f   VARCHAR2 (7) := 'too' || ' long';
   g   VARCHAR2 (8) := 'too' || CHR (30) || CHR (31) || 'long';
BEGIN

-- ON

DECLARE
   a            VARCHAR2 (7) := 'too long';
   b            VARCHAR2 (7) := 'too long';

   c /*2804*/   CHAR (7) := 'too long';
   d /*2804*/   CHARACTER (7) := 'too long';
   e            VARCHAR2 (8) := 'too long';
   f            VARCHAR2 (7) := 'too' || ' long';
   g            VARCHAR2 (8) := 'too' || CHR (30) || CHR (31) || 'long';
BEGIN

-- OFF

      CASE 'S'
         WHEN 'L'
         THEN
            SELECT CASE WHEN 1 > 0 THEN 'S' ELSE 'L'             /*endcase1 */
                                                     END,
                   --   ,case '1' when 'D' then 'D' else  'M' end
                   CASE WHEN 1 > 0 THEN 'S' ELSE 'L'              /*endcase2*/
                                                     END
              INTO v_sit_id
              FROM LOCATIONS_V;
      /*endcase3*/
      END CASE;

-- ON

      CASE 'S'
         WHEN 'L'
         THEN
            SELECT CASE WHEN 1 > 0 THEN 'S' ELSE 'L' /*endcase1 */ END,
                   --   ,case '1' when 'D' then 'D' else  'M' end
                   CASE WHEN 1 > 0 THEN 'S' ELSE 'L' /*endcase2*/ END
              INTO v_sit_id
              FROM LOCATIONS_V;
      /*endcase3*/
      END CASE;

-- OFF

VARRAY email_addrs
   SUBSTITUTABLE AT ALL LEVELS
   STORE AS LOB
   lnt
   (PARTITION partition1 LOB (a) STORE AS                            /* LOB */
                                          (CACHE));

-- ON

VARRAY email_addrs
   SUBSTITUTABLE AT ALL LEVELS
   STORE AS LOB
   lnt
   (PARTITION partition1 LOB (a) STORE AS /* LOB */ (CACHE));

-- OFF

   MEMBER FUNCTION open_account (
      self          IN OUT o_account,
      ssn_num       IN     VARCHAR2,
      accounttype   IN     VARCHAR2 /*test comments*/
                                   ,
      amount        IN     NUMBER,
      name      /* comments */
                    IN     VARCHAR2,
      bday          IN     DATE,
      gender        IN     CHAR,
      addr                 o_address)
      RETURN INTEGER
   AS

-- ON

   MEMBER FUNCTION open_account (
      self                  IN OUT o_account,
      ssn_num               IN     VARCHAR2,
      accounttype           IN     VARCHAR2 /*test comments*/,
      amount                IN     NUMBER,
      name /* comments */   IN     VARCHAR2,
      bday                  IN     DATE,
      gender                IN     CHAR,
      addr                         o_address)
      RETURN INTEGER
   AS

-- OFF

CREATE TABLE xml_oidindex2 OF XMLTYPE
OBJECT ID                                    /*= IDENTIFIER; is undocumented*/
          IS SYSTEM GENERATED
OIDINDEX ind2 (TABLESPACE Users STORAGE (MAXSIZE UNLIMITED));

-- ON

CREATE TABLE xml_oidindex2 OF XMLTYPE
OBJECT ID /*= IDENTIFIER; is undocumented*/ IS SYSTEM GENERATED
OIDINDEX ind2 (TABLESPACE Users STORAGE (MAXSIZE UNLIMITED));

-- OFF

SELECT AVG (x) OVER (ORDER                                                                                  /*SIBLINGS*/

                           BY z RANGE INTERVAL '30.12345' SECOND (2, 4) PRECEDING) FROM t;

-- ON

SELECT AVG (x) OVER (ORDER /*SIBLINGS*/ BY z RANGE INTERVAL '30.12345' SECOND (2, 4) PRECEDING) FROM t;

Will show up in component QP5.dll version 5.382.

Thoughts welcome. :slight_smile:

Andre

1 Like
8

The following input ...

select *
from dual /*Comment1*/ where x = x  /*comment 4*/;

select *
from dual /*Comment1*/ where x = x /*comment 2*/ and x = x /*comment 4*/;

select *
from dual /*Comment1*/ where x = x /*comment 2*/ and x = x /*comment 3*/ and x = x /*comment 4*/;

... would yield:

-- OFF
SELECT *
  FROM DUAL                                                       /*Comment1*/
 WHERE x = x                                                     /*comment 4*/;

SELECT *
  FROM DUAL                                                       /*Comment1*/
 WHERE x = x                                                     /*comment 2*/
             AND x = x                                           /*comment 4*/;

SELECT *
  FROM DUAL                                                       /*Comment1*/
 WHERE x = x                                                     /*comment 2*/
             AND x = x                                           /*comment 3*/
                       AND x = x                                 /*comment 4*/;

-- ON
SELECT *
  FROM DUAL /*Comment1*/
 WHERE x = x                                                     /*comment 4*/;

SELECT *
  FROM DUAL /*Comment1*/
 WHERE x = x /*comment 2*/ AND x = x                             /*comment 4*/;

SELECT *
  FROM DUAL /*Comment1*/
 WHERE x = x /*comment 2*/ AND x = x /*comment 3*/ AND x = x     /*comment 4*/;

You see that the positioning of the last comment doesn't change, since that option ONLY works on embedded, single comments. Also '--' style comments are left alone.

This input:

select X, X
  from dual
 -- cmt1
 where X = X and X = X /*cmt2*/      or 
   X = X /*cmt4*/ and
   X = X;

select X, X
  from dual
 -- cmt1
 where X = X and X = X /*cmt2*/ /*cmt3*/ or
   X = X /*cmt4*/ and
   X = X;

would become:

-- OFF
SELECT X, X
  FROM DUAL
 -- cmt1
 WHERE X = X AND X = X /*cmt2*/
                       OR X = X /*cmt4*/
                                AND X = X;

SELECT X, X
  FROM DUAL
 -- cmt1
 WHERE X = X AND X = X /*cmt2*/
                       /*cmt3*/
                       OR X = X /*cmt4*/
                                AND X = X;

-- ON
SELECT X, X
  FROM DUAL
 -- cmt1
 WHERE X = X AND X = X /*cmt2*/ OR X = X /*cmt4*/ AND X = X;

SELECT X, X
  FROM DUAL
 -- cmt1
 WHERE X = X AND X = X /*cmt2*/
                       /*cmt3*/
                       OR X = X /*cmt4*/ AND X = X;

Stacking may turn embedded comments into trailing comments (last comment of the line), which then will shift to the right upon subsequent formatting whenever the option Align at the right margin is checked. So in this case formatting is not stable.

To avoid that effect only one of both checkboxes can be left in a checked state. Ideally both checkboxes should become a single radio button control though.

Let's see what's happens. :slight_smile:

Andre

9

Waiting for new version...

10

Can you already tell which version this is planned for?

11

We're on 16.1.17 but I still can't see this option.

12

For some reason it didn't make it into the said version 5.382. I mustn't have pay attention to it in the meantime, sorry. Now I put it on my whiteboard, big. By the end of the month two I'll find time to recap and finish it.
Thank you.

13

@avergison ,
Still absent in 16.2 (5.388)

image
image802×198 3.66 KB

14

It's part of the upcoming 5.390 QP5.DLL component.
The current ("old") behavior is that block comments found near the end of a line are also aligned to the right margin along with the line comments (of course assuming the checkbox is checked).
Now I separated block comments from line comments using a second checkbox.
I invite you to try that out. Unchecking it should align block comments the same way as regular tokens.

image

Andre

1 Like
15

Thank You, Andre.

Waiting for 5.390 QP5.DLL component.

16

Hello, @avergison

Now testing 5.390

Both comment options turned on.

Example:

select *
from dual -- Comment1
where x = x -- comment 2
and x = x -- comment 3
and x = x; -- comment 4

After each formatting, last comment ("-- comment 4") jumps right infinitely.