Formatter Assignments/String Concatenation issue

Toad for Oracle
1

Having an issue with string concatenation not being applied to assignments within If blocks, code looks like

2538.Capture.png
2538.Capture.png1009×309 6.31 KB

In the formatter options under "String Concatenation" I have style set to Stacked, Stacking arrangement set to Operators left. Under "Assignments" I have alignment set to Compact and the arrangement wrap check box unchecked.

when I run the formatter over the code I would expect that the assignment within the if block for the variable "other_stuff" would behave the same way that the assignment for the variable "other_other_stuff" is handled below it. But that doesn't seem to be the case, With some testing I have found that the formatter does not seem to attempt to format the "if" statement assignment at all.

Is there an option somewhere I have missed that is driving this behaviour or is this a bug?

2

Can you please paste the SQL text (not pic) for me to try it out?

3

declare

stuff varchar(10);

other_stuff varchar(10);

other_other_stuff varchar(10);

begin

select thing into stuff from place;

if (stuff is not null) then

other_stuff := ‘string’ || to_char(stuff, ‘YYYY-MM-DD’) || ‘string’ || ‘string’ || ‘string’ || ‘string’;

end if;

other_other_stuff := ‘string’ || to_char(stuff, ‘YYYY-MM-DD’) || ‘string’ || ‘string’ || ‘string’ || ‘string’;

end;

4

It seems that only the case of the “IF” statement is formatted, the indenting and alignment are unaffected by the formatter. (note-above is the un-formatted code)

5

the formatted code looks like:

/* Formatted on 23/06/2014 09:00:46 (QP5 v5.265.14096.37972) */

DECLARE

stuff VARCHAR(10);

other_stuff VARCHAR(10);

other_other_stuff VARCHAR(10);

BEGIN

SELECT thing INTO stuff FROM place;

IF (stuff IS NOT NULL) THEN

other_stuff := ‘string’ || TO_CHAR(stuff, ‘YYYY-MM-DD’) || ‘string’ || ‘string’ || ‘string’ || ‘string’;

END IF;

other_other_stuff := ‘string’

|| TO_CHAR

(

stuff,

‘YYYY-MM-DD’

)

|| ‘string’

|| ‘string’

|| ‘string’

|| ‘string’;

END;

6

Most strange, I got a quite normal result after setting the options you suggested (see below).

Could you list me the entire set of options you were using? (go to the top tab of the options tree, and press the Copy… button to get the data onto your clipboard).

As an extra test, could you perhaps just paste the three options below in your Toad (copy the lines below and use the Paste… button on the same tab), these will override your current working options, hence exclude anything else. Afterwards you can restore your own options by pasting the set you just copied.

Thanks,
Andre

<AssignFavorWrapping>0</AssignFavorWrapping>
<ConcatStyle>1</ConcatStyle>
<ConcatOperatorPos>0</ConcatOperatorPos>
7

Here are the settings I am using:

252

5.259.0

5.265

1

1

150

<LF_ConsecLFLimit>1</LF_ConsecLFLimit>

2

0

0

0

0

0

1

0

0

0

1

0

2

0

0

0

1

1

5

2

5

5

5

5

1

5

D

D

D

5

5

5

D

99

5

5

3

3

0

8

Seems when I use just the three options you posted above the code formats as expected, so I guess that means I am overwriting or conflicting those setting somehow?

9

After some further testing the issue seems to be caused by:

1

Once that is removed the “if” block formats as expected. I believe this is a bug in how this new feature is operating.

10

You hit the nail on the head! Yes the culprit was the combination of three things:

  • a predicate between parentheses: if (stuff is not null) then…

  • the ParenthesizedQueryAsBlock option activated

  • using QP5.dll version 5.265 (which I could see in your set of options)

This bug has already been fixed in 5.266.

You can safely use a more recent dll from Toad Beta, but keep the 265 for support purposes.

Thanks,

Andre

11

Thanks Andre