And…? Formatting in Version 4.2

13 Comments

1. 

   balakrishna 6 years ago Reply

   Hi,

   My code is like this

   CURSOR Cur_Tax IS SELECT Nvl(Pct_Un,0) Pct_Un,
   Nvl(Pct_Rc,0) Pct_Rc
   FROM T_Taxval
   WHERE Nptf = P_Nptf AND
   Noval = P_Noval AND
   A = B;

   and i want like below
   CURSOR Cur_Tax IS
   SELECT Nvl(Pct_Un,0) Pct_Un,
   Nvl(Pct_Rc,0) Pct_Rc
   FROM T_Taxval
   WHERE Nptf = P_Nptf
   AND Noval = P_Noval
   AND A = B;
   Regards,
   Sumathi.V

2. 

   Shira Ishikawa 8 years ago Reply

   I have similar problem, but with after commas. Example below:

   nvl(
   oeh.cust_po_number,
   ‘-‘
   )

   Formatter on 4.2 didn’t ignore commas inside the NVL, while in 4.1.5 it did great even when i ops after comma line break. Another problem is the indent, subquery not indented correctly, resulting with Select indented but the From and Where not indented. And another again is the UNION/UNION ALL, the formatter doesn’t have any option to add line break here.

   Any solution for this? Or is there any way to import format setting from 4.1.5?

   • 

     thatjeffsmith Post Author 8 years ago

     How do you mean the sub query isn’t indented properly?

     SELECT 1
     FROM dual
     WHERE 1 IN (
             SELECT 1
             FROM dual
         );

     Please always provide a test case if you want me to look at your formatter issues.

   • 

     Vadim Tropashko 8 years ago

     @Shira

     I don’t see any problem with UNION. For excessive line breaks
     https://community.oracle.com/thread/4040444
     add the following line to the custom format
     & [closestAncestorDescendent.ancestor+10 < closestAncestorDescendent.ancestor)
     This makes function calls with small number of arguments, such as NVL, inline.

   • 

     sankar reddy 8 years ago

     hi,
     plz try below code. it may works for u r problem.
     SET PAGESIZE 200
     SET LINESIZE 140

3. 

   Joe Shoes 8 years ago Reply

   Question. If you change the test code to “SELECT 1,2” and reformat, how come the FROM now moves to the first line? When I reformat, I get this:

   SELECT 1,2 FROM dual
   WHERE 1 = 2
   AND 2 = 3
   AND 3 = 4

   • 

     thatjeffsmith Post Author 8 years ago

     I get this

     SELECT 1
        ,   2
     FROM dual
     WHERE 1 = 2 AND
             2 = 3 AND
             3 = 4

   • 

     Vadim Tropashko 8 years ago

     With no break on comma option it looks like a bug

   • 

     Joe 8 years ago

     Sorry Vadim, I should have added that small IMPORTANT fact in my question. I have no break on comma option set in my preferences. As soon as I add at least one comma(,) on the first line, the FROM clause will always move up to the SELECT line.

4. 

   Vadim Tropashko 8 years ago Reply

   Comment out
   –| [node) expr & [ancestor) aggregate_function
   in the ancestorDescendant rule. Alternatively, you can exclude only listagg like this:
   | [node) expr & [ancestor) aggregate_function & ![ancestor) listagg

   • 

     Vadim Tropashko 8 years ago

     To expand my comment a little, somewhat more detailed discussion of excessive line breaks is here:
     https://community.oracle.com/thread/4040444
     The suggested amendment, that is the condition
     & [closestAncestorDescendent.ancestor+10 < closestAncestorDescendent.ancestor)
     is incorporated into official code. As it has been discussed in the thread, if function argument is long, they would be broken into separate lines; if the list is short, then they would be inline. Unfortunately, listagg syntax is tricky, and it escapes this condition. For this case please use my earlier suggestion.

5. 

   TR Rudkin 8 years ago Reply

   I have a similar preference for not breaking after open paren “(” or before a close paren “)” . An open paren should never end a line and an close paren should never start a line. I may be one only one that likes code this way be a formatter should be able to generate any layout that is syntactically correct.

   Example
   
SELECT set#
, LISTAGG(
val
,','
) WITHIN GROUP( ORDER BY val )
FROM sets
GROUP BY
set#


   
SELECT set#
, LISTAGG( val, ',' ) WITHIN GROUP( ORDER BY val )
FROM sets
GROUP BY set#


   Or maybe I have not figured out how the make the auto-detect learn my formatting.

   • 

     thatjeffsmith Post Author 8 years ago

     You’ll have to use the custom formatting to make your own rules. I’ll try to take a look tomorrow…at the beach.