Forum Stats

  • 3,838,675 Users
  • 2,262,392 Discussions


SD 4.2 formatter - too many line breaks

sarlaac Member Posts: 12
edited Apr 27, 2017 2:59PM in SQL Developer

I just downloaded the 4.2 version and didn't import my settings from previous. I'm trying to setup the formatter to produce similar results to what I got in 4.1.5 but it seems to be doing a worse job of formatting the query. I have provided a example query below with a desired and actual results. In the actual results I have almost all line breaks off except before comma, but the formatted result breaks NVL or DECODE. Additionally the formatter indents 4 spaces after the comma. I would like it not to, and can't find a setting to achieve this.


SELECT nvl('this','that') col_name, 'Column1', 'Col2' FROM dual;

--desired format

--NVL, DECODES on one line

--break before comma, no space after.

--align columns and KEYWORDS

SELECT nvl('this', 'that') col_name



  FROM dual;

--SD 4.2 actual format

-- break before comma. everything else unchecked.




    ) col_name

   ,   'Column1'

   ,   'Col2'

FROM dual;


  • Vadim Tropashko-Oracle
    Vadim Tropashko-Oracle Posts: 1,260 Employee
    edited Apr 26, 2017 7:25PM

    Custom format:

    /*| [node) expr    &  [ancestor) "(x,y,z)"                         & ![node^) comparison_condition

                                                                      & ![node^) compound_expression

                                                                      & ![node^) interval_expression*/

    You might actually want somewhat more sophisticated formatting rule, e.g. "Indent arguments for function when there are more than 4 of them"

    In that case please disregard my earlier suggestion but modify the following:


        closestAncestorDescendent.ancestor = ancestor4FirstAndLastDescendantsWithGap.ancestor

      & closestAncestorDescendent.node = closestAncestorDescendent.node   -- vacuous condition ...

      & [closestAncestorDescendent.ancestor+10 < closestAncestorDescendent.ancestor)

    Here the magic number 10 includes commas and parenthesis contributing to the length of the ancestor parse tree node. The output:


        nvl('this','that','3',4) col_name,







        ) col_name2



  • sarlaac
    sarlaac Member Posts: 12
    edited Apr 27, 2017 10:49AM

    While I appreciate there is a solution to adjust the formatting. I don't see this as a good user friendly solution. Previous versions of SD and other formatting tools just handle this and format SQL much closer to what I'm looking for without a lot of tinkering. I'm happy to click a few checkboxes to tailor the output, but having to understand and edit this syntax is too much to ask. Are the format settings still under development, and can I expect some easier to use settings, or is the intent to shift to using these advanced options going forward?

  • Vadim Tropashko-Oracle
    Vadim Tropashko-Oracle Posts: 1,260 Employee
    edited Apr 27, 2017 1:53PM

    We already have some options on Advanced format panel which influence the rules on Custom panel. For example, flipping Breaks->Case->After WHEN results in

    /*+breakAfterWhen | [node) condition  &  [node-1) 'WHEN'*/

    being rewritten into

    /*+breakAfterWhen*/ | [node) condition  &  [node-1) 'WHEN'

    Therefore, new options can be introduced relatively easily. The problem is naming them intuitively. Also, to implement your request, would you be OK with one long line

    function_call(argument1, argument2, argument3, argument4, argument5, argument6+argument7, argument8, argument9)

    or is it better to indent arguments if some threshold is exceeded?

  • sarlaac
    sarlaac Member Posts: 12
    edited Apr 27, 2017 2:59PM

    I think if you put an option on the advanced panel it would be better to take an argument on how many to break after. This way users can choose 0 or blank to just break on everything, or X so NVL or NVL2 doesn't break while longer calls do break out. I would usually want NVL and NVL2 and other smaller functions to not break, but I would want a function with more arguments to have breaks, especially when it gets so long I have to scroll over on the line to read it all.

This discussion has been closed.