Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
SD 4.2 formatter - too many line breaks

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.
--unformatted
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
,'Column1'
,'Col2'
FROM dual;
--SD 4.2 actual format
-- break before comma. everything else unchecked.
SELECT nvl(
'this'
,'that'
) col_name
, 'Column1'
, 'Col2'
FROM dual;
Answers
-
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:
complexIndentConditions:
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:
SELECT
nvl('this','that','3',4) col_name,
nvl(
'this',
'that',
'3',
4,
5
) col_name2
FROM
dual;
-
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?
-
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?
-
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.