inconsistent formatting behavior

Toad for Oracle - Beta
1

All,

I’m currently on Toad 12.11.0.82.

Within an IF statement, I’m doing two calls to a single routine (parms are different).
Strange enough, the code gets formatted quite differently:

if lower(p_debug) = ‘debug’
then
utl_krg.msg_log.log_debug_msg(p_schema => g_my_schema
, p_package => g_my_package
, p_routine => l_my_routine
, p_msg_id => g_my_msg_prefix || ‘0022D’
, p_message => 'Queue ’
|| coalesce(g_mqget_queue, ‘<*Null>’)
|| ’ opened ’
|| case
when l_errmsg = ‘OK’ then ‘successfully’
else ’ with error: ’ || l_errmsg
end
);
utl_krg.msg_log.log_debug_msg(
p_schema =>
g_my_schema
, p_package =>
g_my_package
, p_routine =>
l_my_routine
, p_msg_id =>
g_my_msg_prefix || ‘0023D’
, p_message =>
‘g_mqget_status=’
|| coalesce(g_mqget_status, ‘<*Null>’)
|| ‘, g_mqget_retry_time=’
|| coalesce(trim(to_char(g_mqget_retry_time, ‘yyyy-mm-dd hh24:mi:ss’)), ‘<*Null>’)
);
end if;

Q1: Why are they being formatted differently?

Q2: I don’t mind the reduced indentation of the p_message parameter, but I really hate all the other parameters being spread out over two lines where there is no need to do so. Would it be possible to make this configurable?

Kind regards,
Abe Kornelis

2

Hello Abe,

I could reproduce this result by setting at least the following:

  • Margin to above 98,
  • CASE expressions to “allow half expanded” only (no allow flat),
  • Parameters List Arrangement: Stacked + Parens option #2 switching to #5 on overflow.

A1: Here’s the reason. When a statement/clause is formatted and when there’s not enough room then formatter will attempt to fold the outermost construct first (one which can be folded of course). In our case it may do that for both function calls. They are both tried using arrangement #2:

myfunc (aaaaaa,
bbbbbb,
…
)

The parameter block of the 2nd call is much wider than that of the 1st call, because the CASE statement in the 1st call has the option “Allow flat” turned off, which makes it fold regardless of available room. Hence the parameter block in the 1st call is not too wide, and arrangement #2 will fit.

However, the 2nd call has the long COALESCE call which causes a right margin overflow, which will cause the outermost construct which can be folded, to fold. The latter means that for the 2nd call the list arrangement #2 will change to #5:

myfunc(
aaaaaa,
bbbbbb,
…
)

The parameter block is still too wide of course, but there is are no more alternatives for list arrangement. What will happen is that each of the parameters will now be folded. This causes the named parameters to get folded on two lines (-> Q2), and the || concatenation to get stacked. More inner constructs will be folded until no more margin overflow occurs (or until no more folding is possible).

I guess I just wrote a chapter of some formatter manual. :slight_smile: Sorry if it sounds a bit complex, but you asked why, and that’s just the way it works.

A2: Fully agree. I just described the “why” a few lines above. Remember we discussed a better way to handle name parameters a while ago? This is still in the works (QP-2344, https://www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86/t/32152), I already have good interim results, but I need a bit more time to get fully through.

Hope this helps,

Thanks,
Andre

3

Please refer to http://www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86/p/34361/67664#67664 for further discussion.

4

Fixed in component QP5 version 5.320.

5

Correction: Fixed in component QP5 version 5.319.

6

Andre,

I just noticed 12.12 is being shipped with QP5 version 5.318.
Too bad.

We’ll wait for 12.13. When’s the first beta scheduled?

Kind regards,
Abe