4 Replies Latest reply on Jan 28, 2017 6:07 AM by KiwiAndrew

    [4.2EA2] Formatter Questions

    KiwiAndrew

      Been playing with 4.2.0.16.356 Ctrl-F7

       

      1. Where has "Right-Align Master Keywords" gone? i.e. can I get

       

      SELECT something
        FROM somewhere
       WHERE this = that
         AND that < theother
      

      rather than

      SELECT something
      FROM somewhere
      WHERE this = that
      AND that < theother
      

       

      2. How do I force a line break BEFORE SELECT/FROM/WHERE e.g. I want sub-Selects on a new line:

      Select this
      from (
             Select that
              From   there
           )
      

       

      3. Some of the options in the Advanced Formatting panel are not used to format the sample until I select them (e.g. Line breaks/On xxx) although they do work in the code window.

       

      4. White space/Around parenthesis is not used for "Insert Into Values (this,that)" - It appears to always force spaces around these parentheses i.e. "values ( this, that )"? It either apply to all parentheses, or have specific items for each variation.  (Note - "Around parenthesis" should really be "Around parentheses" since it is the plural to match After commas etc)

       

      5. All the elements of CASE expressions should line up e.g.

      SELECT CASE 1
             WHEN 1 THEN 99
             ELSE 100
             END
      FROM dual
      

       

      6. I'd like to NOT break before THEN in CASE statements - see above

       

      7. If I have No breaks on Commas (which I do ) I don't want white space added e.g. rather than

       

      FUNCTION hire ( last_name       VARCHAR2, job_id          VARCHAR2, manager_id      NUMBER, salary          NUMBER, department_id   NUMBER ) RETURN NUMBER IS
      

       

      I'd prefer

      FUNCTION hire (last_name VARCHAR2, job_id VARCHAR2, manager_id NUMBER, salary NUMBER, department_id NUMBER) RETURN NUMBER IS
      

       

      Any comments? 

        • 1. Re: [4.2EA2] Formatter Questions
          Vadim Tropashko-Oracle

          In case #2 I've got

           

          SELECT this

          FROM (

                  SELECT that

                  FROM there

              )

           

          With custom format

           

          | [node) table_reference /*--added this condition->*/ & ![node) query_table_expression

           

          it won't indent query_table_expression (that is subquery together with enclosing parenthesis)

           

          SELECT this

          FROM (

              SELECT that

              FROM there

          )

           

          In case #5 and #6 it formatted as

           

          SELECT

              CASE 1

              WHEN 1 THEN 99

              ELSE 100

              END

          FROM

              dual

           

          with no breaks after case,when, else, and no indentation on expressions after WHEN in custom settings:

           

          --| [node) expr  &  [node-1) 'WHEN'

           

          I agree that #7 is a bug. Workaround: comment this line on custom formatting page:

           

          --| [id) decl_id & ([id+1) prm_spec_unconstrained_type | [id+1) 'IN' | [id+1) 'OUT' ) & [scope) fml_part

          • 2. Re: [4.2EA2] Formatter Questions
            KiwiAndrew

            Thanks Vadim -

             

            If I set Line breaks/On subqueries, I also get your #2, which is closer to what I like - without it I get the whole statement on one line (I didn't expect one option to "fix" the whole statement)...

             

            For #5,6, I have to set After SELECT/FROM/WHERE to reproduce your result? But I really only want to break on the Select - please can these three be separated?  or can I do it via the Custom Formatting?

             

            Is there an option to reset just the formatting preferences to their defaults?

            • 3. Re: [4.2EA2] Formatter Questions
              Vadim Tropashko-Oracle

              I must confess that the set of advanced options has been inherited from the former formatter, and I don't think that was a good idea. Consider "break after comma". Do users want to have the same rule for list of arguments in procedure, and list of tables in a query? Your enhancement request to split the option for SELECT/FROM/WHERE indicates that perhaps it is desirable to have more refined options. A satisfactory answer to that request is custom format which describes formal logical conditions to carry formatting actions. Unfortunately, right now it only has indentation condition, and no separate rule for line-breaks.

               

              On more practical note, if we have a split option for the CASE/WHEN/THEN, why not to do the same for SELECT/FROM/WHERE as you have suggested?

               

              Currently, the only way to reset formatting options is deleting formatterOptions.xml (which is located where product-preferences.xml are), then restarting sqldeveloper. Likewise, custom formatting rules are persisted in format.arbori . Perhaps, having a reset button would be handy...

              • 4. Re: [4.2EA2] Formatter Questions
                KiwiAndrew

                So without wanting to start a formatting war, what I really want is this:

                 

                SELECT  this, that

                  FROM  thing1 t1

                        INNER JOIN thing2 t2

                                ON t1.this = t2.whatever

                               AND SYSDATE > SYSDATE - 1

                WHERE  this > 10

                   AND  that <= 27;

                 

                Although I could probably live with

                 

                SELECT  this, that

                FROM    thing1 t1

                        INNER JOIN thing2 t2

                            ON  t1.this = t2.whatever

                            AND SYSDATE > SYSDATE - 1

                WHERE   this > 10

                AND     that <= 27;

                 

                Both of these mean that some number of TABs (note I have TAB set to be 4 spaces) takes me to the correct position to start typing, for example when adding a new condition.

                 

                How about a hook in Custom Formatting that allows you to pass a subtree off for formatting by a piece of code written in Java/Python/Perl/something .Net-ish - then you can tell me to write my own formatter for the bits I care about