10 Replies Latest reply on Apr 19, 2020 10:27 PM by jgebal

    SQL Developer 19.2.1 - Custom format: line break on hints, columns/argument alignment

    Dilly1990

      We are migrating from Toad to Sql developer and below are few requirement to format the code on the Sql developer.

      All the code snippets show below are from PL/Sql.

       

      1) Line break after the hint on the Select

      SELECT /*+ use_hash(rtd st incl str) */ rtd.str_no AS str_no

      ,TRUNC (rtd.tx_dte_tme) AS tx_dte

      ,NVL (st.trml_typ_cd,’UNK’) AS trml_typ_cd

       

      Expected:

      SELECT /*+ use_hash(rtd st incl str) */

      rtd.str_no AS str_no

      ,TRUNC ( rtd.tx_dte_tme) AS tx_dte

      ,NVL (st.trml_typ_cd, ‘UNK’) AS trml_typ_cd

       

      2) Extra line break before Cursor declaration only when variable/constant defined above.

      c_defaultBsStrNo CONSTANT STORE.str_no%TYPE := 0;

      CURSOR l_rtlTndrDtlCur IS

      SELECT * from dual

       

      Expected:

      c_defaultBsStrNo CONSTANT STORE.str_no%TYPE := 0;

       

      CURSOR l_rtlTndrDtlCur IS

      SELECT * from dual

       

      3) Remove line beak on Procedure / Function / Cursor parameter declaration. Line break are need only when the declaration exceeds MAX CHAR LINE WIDTH

      PROCEDURE insTndrOnly (

      p_runDte IN DATE

      ,p_txDte IN DATE

      );

       

      Expected :

      PROCEDURE insTndrOnly (p_runDte IN DATE, p_txDte IN DATE);

       

      4) When invoking the procedure, line breaksBeforeComma on each parameter and also parenthesis need to be aligned

      Expected:

      5) if_stmt – Line break before on the and_expr.

      IF (l_prevStrNo = l_tndrCrctnChgRec.str_no) AND (l_prevTrmlNo = l_tndrCrctnChgRec.trml_no) AND (l_prevTxNo = l_tndrCrctnChgRec.tx_no) AND (l_prevTxDte = l_tndrCrctnChgRec.tx_dte) AND (l_prevTrmlTypCd = l_tndrCrctnChgRec.trml_typ_cd) AND (l_prevRunDTe = l_tndrCrctnChgRec.run_dte)

      THEN l_prevTndrActnCd = 0;

         ELSE l_prevTndrActnCd = 1;

         END IF;

       

      Expected:

       

      Below are the changes made to the Advanced format:

      I would really appreciate, if you could help me out.

        • 1. Re: SQL Developer 19.2.1 - Custom format: line break on hints, columns/argument alignment
          thatJeffSmith-Oracle

          Step 1, go get version 19.4, b/c the formatter has several improvements in the areas of proper alignment already, so that's a better baseline

          • 2. Re: SQL Developer 19.2.1 - Custom format: line break on hints, columns/argument alignment
            Vadim Tropashko-Oracle

            1. Comments (and hints) are filtered away by parser, and are consequently ignored by formatter. To be able to process them with Custom Format, the  input text must be made accessible as java object in Nashorn. I made this amendment to 20.1 as per your earlier request to be able to update the formatting timestamp. Then, a hint  can be recognized in the input and, consequently, new line can be appended. However you would likely want to indent the first column with the others -- this doesn't look easy at the moment.

             

            2. You have disabled double breaks after statements in the Advanced Options, but you need to enable them back to make the rule brkX2 working. If you don't need double line breaks under the certain conditions, then you just remove those disjuncts. Assuming you don't want any of them, here is the amended rule:

             

            brkX2:

              --brkX2_1 | brkX2_2

              [node) basic_decl_item_list & [node+1) full_cursor_body

            ->;

             

            The formatted output:

             

            DECLARE

               c_defaultbsstrno  CONSTANT store.str_no%TYPE := 0;

               c_defaultbsstrno  CONSTANT store.str_no%TYPE := 0;

             

               CURSOR l_rtltndrdtlcur IS

               SELECT *

                 FROM dual;

             

               CURSOR l_rtltndrdtlcur IS

               SELECT *

                 FROM dual;

            BEGIN

               NULL;

            END;

             

            3.

            simpleIndentConditions: 

            ...

            | [node) privilege_audit_clause

            --| [node) parm_list_opt &  [node^) fml_part 

            --| [node) prm_spec &       [node^) fml_part 

            --| [node) query_partition_clause --& [node^) analytic_clause

            ...

             

            _paddedIdsInScope1: (

            -- types

              :alignTypeDecl & [id) identifier & [id+1) datatype & [scope) relational_properties

            --| :alignTypeDecl & ( :breaksAfterComma | :breaksBeforeComma ) & [id) decl_id & ([id+1) prm_spec_unconstrained_type | [id+1) mode /*30067569:*/| [id+1) 'OUT' | [id+1) 'IN' ) & [scope) fml_part

            | :alignTypeDecl & ( :breaksAfterComma | :breaksBeforeComma ) & [id) decl_id & ([id+1) constrained_type | [id+1) object_d_rhs) & [scope) adt_definition

            ...

             

            _extraBrkBefore:

            ...

            | :breaksBeforeComma &  [node+1) field                 & [node) ','

            --| :breaksBeforeComma &  [node+1) prm_spec              & [node) ','

            | :breaksBeforeComma &  [node+1) XML_attributes_clause        & [node) ','

            ...

             

            Output:

             

            CREATE OR REPLACE PACKAGE BODY emp_mgmt AS

               tot_emps   NUMBER;

               tot_depts  NUMBER;

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

                  new_empno NUMBER;

               BEGIN

            ...

             

            4.

            format advanced.png

            5.

            simpleIndentConditions: 

            ...

            | [node) alter_method_spec

            --| [node) and_expr & ![node) rel

            | .breaksProcArgs & [node) arg & [node^) paren_expr_list & [node+1) arg_list

            ...

             

            _extraBrkBefore:

            [node) 'AND' & [node+1) arith_expr |

              [node) physical_properties

            ...

             

            Output:

               BEGIN

                  IF (l_prevstrno = l_tndrcrctnchgrec.str_no)

                  AND (l_prevtrmlno = l_tndrcrctnchgrec.trml_no)

                  AND (l_prevtxno = l_tndrcrctnchgrec.tx_no)

                  AND (l_prevtxdte = l_tndrcrctnchgrec.tx_dte)

                  AND (l_prevtrmltypcd = l_tndrcrctnchgrec.trml_typ_cd)

                  AND (l_prevrundte = l_tndrcrctnchgrec.run_dte)

                  THEN ilevel := 'Low Income';

             

            P.S. Tested everything in 19.4, not 19.2.

            • 3. Re: SQL Developer 19.2.1 - Custom format: line break on hints, columns/argument alignment
              Dilly1990

              Thanks Vadim for your code snippets.

              After migrating to 19.4, I was able to break lines and align code as needed.

              However on few instance I'm unable to align the code.

               

              Can you please help me on the following requirement,

               

              SQL and SQL within the PL/SQL:

              • On the CASE_EXPRESSION both CASE and END need to be aligned on the same column.

                       SEARCHED_CASE_EXPRESSION AND ELSE_CLAUSE  need to be indent within the case statement.

              • Indent ON_USING_CONDITION next to JOIN_CLAUSE.

               

              SELECT rtd.col1

                    ,rtd.col2

                    ,CASE

                 WHEN rtd.col1 = 'Y' THEN (rtd.col1 / 100) * - 1

                 ELSE (rtd.col1 / 100) * 1

              END AS prod_prc

                    ,t99.col1

                    ,TRUNC (SYSDATE) AS run_dte

              FROM table1 rtd

              JOIN table2  t99

              ON rtd.str_no = t99.str_no

                 AND CASE

                 WHEN rtd.col1 = 'N' THEN rtd.col1 * - 1

                 ELSE rtd.col1 / 100 * 1

              END = rtd.extd_prc

                 AND CASE

                 WHEN rtd.can_key_fg = 'N' THEN 'A'

                 ELSE 'V'

              END = t99.void_fg

              JOIN table2  t98

              ON rtd.str_no = t98.str_no

              WHERE t99.run_dte BETWEEN p_runDte AND p_runDte + 1;

               

              Expected:

              • 4. Re: SQL Developer 19.2.1 - Custom format: line break on hints, columns/argument alignment
                Vadim Tropashko-Oracle

                1. Advanced Format -> Line Breaks -> IF/CASE/WHILE  = Terse (line breaks only after actions)

                 

                2. Custom Format:

                 

                simpleIndentConditions:

                ...

                   | [node) case_expression & ![node) select_term & ![node^) "aliased_expr" & ![node^) comparison_condition

                ...

                  | [node) on_using_condition

                ...

                 

                pairwiseAlignments1: predecessor = node-1 & (

                    [predecessor) group_by_list[5,12)            & [node) ','

                  | [predecessor) order_by_clause[31,38)          & [node) ','

                  | [predecessor) select_list                    & [node) ','

                  | [predecessor) cartesian_product              & [node) ','

                  | [predecessor) merge_insert_clause[26,33)      & [node) ','

                  | [predecessor) column                          & [node) merge_insert_clause[26,33)

                  | [predecessor) "expr_list_def"[16,34)          & [node) ','

                  | [predecessor) "expr_list"                    & [node) ','          -- leading commas

                  | [predecessor) column                          & [node) "expr_list_def"[16,34)

                  | [predecessor) merge_update_clause[36,56)      & [node) ','

                  | !:alignRight & [predecessor) condition        & [node) AND_OR

                  | [predecessor) expr                  & [node) compound_expression[25,44)

                  | [predecessor) '>'                    & [node) expr  & [node^) arg

                  | [predecessor) arith_expr            & [node)  binary_add_op

                | [predecessor) 'CASE'      & [node)  searched_case_expression

                );

                pairwiseAlignments2: predecessor=node-1-1  & (

                    [predecessor) select_list    & [node) select_term & [node-1) ','

                  | [predecessor) cartesian_product      & [node) table_reference  & [node-1) ','

                  | [predecessor) condition      & [node) condition  & [node-1) AND_OR

                  | [predecessor) adt_field_list & [node) adt_field  & [node-1) ','

                  | [predecessor) adt_field      & [node) adt_field  & [node-1) ','

                  | [predecessor) "expr_list"    & [node) expr        & [node-1) ','

                  | [predecessor) expr          & [node) expr      

                  | !:breakOnSubqueries  & [predecessor) '('    & [node) ')'  & [node-1) query_block & ![node^) par_subquery

                  | [predecessor) 'CASE'      & [node)  'END'

                  | [predecessor-1) 'CASE'      & [node)  'END'

                  | [predecessor) 'CASE'      & [node)  else_clause

                );

                 

                 

                additional2Spaces:

                   [node) searched_case_expression

                | [node) else_clause

                -> { padNode(2) }

                 

                Output:

                SELECT rtd.col1

                      ,rtd.col2

                      ,CASE

                          WHEN rtd.col1 = 'Y' THEN (rtd.col1 / 100) * - 1

                          ELSE (rtd.col1 / 100) * 1

                        END AS prod_prc

                      ,t99.col1

                      ,trunc (sysdate) AS run_dte

                  FROM table1 rtd

                  JOIN table2 t99

                   ON rtd.str_no = t99.str_no

                      AND CASE

                            WHEN rtd.col1 = 'N' THEN rtd.col1 * - 1

                            ELSE rtd.col1 / 100 * 1

                          END = rtd.extd_prc

                      AND CASE

                            WHEN rtd.can_key_fg = 'N' THEN 'A'

                            ELSE 'V'

                          END = t99.void_fg

                  JOIN table2 t98

                   ON rtd.str_no = t98.str_no

                WHERE t99.run_dte BETWEEN p_rundte AND p_rundte + 1;

                • 5. Re: SQL Developer 19.2.1 - Custom format: line break on hints, columns/argument alignment
                  Dilly1990

                  Case and End are aligned, however when there are multiple WHEN's, first WHEN alignment goes slightly off.

                  Can you please help me on this.

                   

                  SELECT rtd.col1

                        ,rtd.col2

                        ,CASE

                                WHEN rtd.col1 = 'N'      THEN (rtd.col1 / 100) * - 1

                          WHEN rtd.col1 = 'Y'      THEN (rtd.col1 / 100) * - 1

                          WHEN rtd.col1 != 'Y'     THEN (rtd.col1 / 100) * - 1

                            ELSE (rtd.col1 / 100) * 1

                          END AS prod_prc

                        ,t99.col1

                        ,TRUNC (SYSDATE) AS run_dte

                  FROM table1 rtd;

                   

                  Expected:

                  SELECT rtd.col1

                        ,rtd.col2

                        ,CASE

                            WHEN rtd.col1 = 'N'      THEN (rtd.col1 / 100) * - 1

                            WHEN rtd.col1 = 'Y'      THEN (rtd.col1 / 100) * - 1

                            WHEN rtd.col1 != 'Y'     THEN (rtd.col1 / 100) * - 1

                            ELSE (rtd.col1 / 100) * 1

                          END AS prod_prc

                        ,t99.col1

                        ,TRUNC (SYSDATE) AS run_dte

                  FROM table1 rtd;

                  • 6. Re: SQL Developer 19.2.1 - Custom format: line break on hints, columns/argument alignment
                    Dilly1990

                    -- https://community.oracle.com/thread/4202758

                    Another question about the pairwiseAlignments7_3, it aligns the merge_update_clause only when Line breaks on comma ->  After.

                     

                    Can you please let me know what changes are needed on the 7_3 to align merge_update_clause when Line breaks on comma ->  Before.

                     

                    MERGE INTO employees e

                    USING hr_records h ON (e.id = h.emp_id)

                    WHEN MATCHED THEN

                    UPDATE

                    SET e.address = h.address e.col1 = b.col1

                    ,e.col2 = b.col2

                    ,e.col3 = b.col3

                    • 7. Re: SQL Developer 19.2.1 - Custom format: line break on hints, columns/argument alignment
                      Vadim Tropashko-Oracle

                      Thank you for the new test cases. Correction:

                       

                      additional2Spaces:

                        [node) searched_case_expression#

                      | [node) else_clause

                      -> { padNode(2) }

                       

                      Output:

                       

                      SELECT rtd.col1

                            ,rtd.col2

                            ,CASE

                                WHEN rtd.col1 = 'N'      THEN (rtd.col1 / 100) * - 1

                                WHEN rtd.col1 = 'Y'      THEN (rtd.col1 / 100) * - 1

                                WHEN rtd.col1 != 'Y'     THEN (rtd.col1 / 100) * - 1

                                ELSE (rtd.col1 / 100) * 1

                              END AS prod_prc

                            ,t99.col1

                            ,trunc (sysdate) AS run_dte

                        FROM table1 rtd;

                       

                      Fixing merge statement format:

                       

                      pairwiseAlignments9_1: -- https://community.oracle.com/thread/4310878

                        [predecessor) column

                      & [node) ',' & [node+1) column

                      & predecessor=ancestor-3

                      & ancestor < node

                      ;

                       

                      pairwiseAlignments:

                      ...

                        | pairwiseAlignments9_1

                      ...

                      ->

                      ;

                       

                      less2Spaces:   :breaksBeforeComma & (

                          [node+1) arg & [node) ','

                        | [node+1) prm_spec & [node) ','

                        | [node+1) select_term & [node) ','

                        | [node+1) group_by_col & [node) ','              

                        | [node+1) "ord_by_1desc" & [node) ','            

                        | [node+1) table_reference & [node) ','

                        | [node+1) par_expr_list[19,31) & [node) ','  -- in insert clause

                        | [node-1) "expr_list" & [node) ','           -- in insert clause

                        | [node^) query_partition_clause[14,21) & [node) ','

                        | [node+1) alias_in_out_constraints & [node) ',' 

                        | [node+1) column & [node+2) '=' & [node) ',' 

                      ) -> {

                          var node = tuple.get("node");      

                          var nodeIndent = struct.getNewline(node.from);

                          var offset = 2;

                          if( !struct.getBoolBind("spaceAfterCommas") )

                              offset = 1;

                          if( nodeIndent != null )

                              struct.putNewline(node.from, nodeIndent.substr(0,nodeIndent.length-offset));

                      }

                       

                       

                      Output:

                       

                      MERGE INTO employees e

                      USING hr_records h ON (e.id = h.emp_id)

                      WHEN MATCHED THEN UPDATE

                         SET e.address = h.address

                            ,e.col1 = b.col1

                            ,e.col2 = b.col2

                            ,e.col3 = b.col3

                       

                      To remove indentation of the SET clause:

                       

                      rightAlignments: :alignRight & (

                      ...

                        | [node) 'SET'   &  ![node^) merge_update_clause

                      ...

                      ) -> 

                      ;

                      • 8. Re: SQL Developer 19.2.1 - Custom format: line break on hints, columns/argument alignment
                        Dilly1990

                        HI Vadim,

                         

                        I'm trying to format Analytic functions in both SQL and PL/SQL, looks like each function (SUM, RANK, COUNT) has different grammar rules.

                        is there a way to format the analytic function in similar way.

                         

                        Input Sql:

                        SELECT ORDER_CD

                              ,SUM (ORDERS)

                           OVER (

                              PARTITION BY COL1,COL2,COL3

                              ORDER BY DATE1

                           )

                        COL1

                              ,RANK (UNITS_ORDERED)

                        OVER (

                           PARTITION BY COL1,COL2,COL3

                           ORDER BY DATE1

                        ) COL2

                              ,COUNT (UNITS_ORDERED)

                                OVER (

                                   PARTITION BY COL1,COL2,COL3

                                   ORDER BY DATE1

                                )

                        COL3

                              ,UNITS_ORDERED

                        FROM DUAL

                         

                        Expected:

                         

                        Thanks,

                        Dilly

                        • 9. Re: SQL Developer 19.2.1 - Custom format: line break on hints, columns/argument alignment
                          Vadim Tropashko-Oracle

                          _extraBrkBefore:

                          ...

                          | [node) order_by_clause

                          | [node) 'OVER'

                          ...

                           

                          pairwiseAlignments00: predecessor = node^^ & (

                              [predecessor) function            & [node)  'OVER'

                          );

                          pairwiseAlignments0: predecessor = node^ & (

                              [predecessor) case_expression      & [node)  'END'

                            | [predecessor) else_clause          & [node)  expr

                            | [predecessor) over_clause          & [node)  ')'

                            | [predecessor) function            & [node)  over_clause

                            | [predecessor) function            & [node)  'OVER'

                          );

                           

                          pairwiseAlignments1: predecessor = node-1 & (

                          ...

                            | [predecessor) query_partition_clause        & [node)  order_by_clause

                          );

                           

                          pairwiseAlignments30: predecessor=node-1-1-1  & (

                              [predecessor) 'OVER'          & [node) ')'

                          );

                           

                          pairwiseAlignments:

                              pairwiseAlignments00

                            | pairwiseAlignments0

                            | pairwiseAlignments1

                            | pairwiseAlignments2

                            | pairwiseAlignments30

                            | pairwiseAlignments3

                          ...

                           

                          Tested with

                          breaksAfterSelect=false

                          breaksComma=Before

                          commasPerLine=1

                           

                          SELECT order_cd

                              , SUM(orders)

                                  OVER(PARTITION BY col1

                                                  , col2

                                                  , col3

                                        ORDER BY date1

                                  ) col1

                              , RANK(units_ordered)

                                  OVER(PARTITION BY col1

                                                  , col2

                                                  , col3

                                        ORDER BY date1

                                  ) col2

                              , COUNT(units_ordered)

                                  OVER(PARTITION BY col1

                                                  , col2

                                                  , col3

                                        ORDER BY date1

                                  ) col3

                              , units_ordered

                          FROM dual

                          • 10. Re: SQL Developer 19.2.1 - Custom format: line break on hints, columns/argument alignment
                            jgebal

                            Vadim Tropashko-Oracle wrote:

                             

                            1. Comments (and hints) are filtered away by parser, and are consequently ignored by formatter. To be able to process them with Custom Format, the input text must be made accessible as java object in Nashorn. I made this amendment to 20.1 as per your earlier request to be able to update the formatting timestamp. Then, a hint can be recognized in the input and, consequently, new line can be appended. However you would likely want to indent the first column with the others -- this doesn't look easy at the moment.

                             

                             

                            Will the parser enhancements allow to distinguish between:

                            - single-line comments -- comment

                            - multi-line comments /* comment newline comment */

                            - single-line hint --+ hint

                            - multi-line hint /*+  hint newline hint */

                             

                            The reason why I'm asking is that there is a significant behavioral impact if we don't do (remove) a newline after a single-line comment/hint

                             

                            SELECT --+ INDEX(A)
                                 *
                              FROM SOME_TABLE A
                             WHERE ID = :X;
                            

                             

                            SELECT --+ INDEX(A) * 
                              FROM SOME_TABLE A
                             WHERE ID = :X;
                            

                             

                            The example 2 in above is not a valid SQL while below, both are valid.

                             

                            SELECT /*+ INDEX(A) */
                              *
                            FROM SOME_TABLE A
                            WHERE ID = :X;
                            

                             

                            SELECT /*+ INDEX(A) */ *
                            FROM SOME_TABLE A
                            WHERE ID = :X;
                            

                             

                            Taking this into consideration, some formatting could actually make code invalid if using single-line comments/hints