4 Replies Latest reply on Jan 12, 2020 10:06 AM by Franco Soldera

    SQL Dev. 19.4 - Advanced format: columns/args alignment wrong when comma before with no space

    Franco Soldera

      The alignment of select fileds and procedure's args breaks when setting line breaks before commas and no space after commas:

      -- 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
      

       

      -- Expected

      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
      

       

      It works fine if I set space after commas:

      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
      
        • 1. Re: SQL Dev. 19.4 - Advanced format: columns/args alignment wrong when comma before with no space
          Vadim Tropashko-Oracle

          Custom format, modify (or replace) the following rule:

           

          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) ','

          ) -> {

              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));

          }

          1 person found this helpful
          • 2. Re: SQL Dev. 19.4 - Advanced format: columns/args alignment wrong when comma before with no space
            Franco Soldera

            Thank you so much Vadim Tropashko-Oracle, it was spot on!

             

            I found a similar exception when creating views:

            CREATE OR REPLACE FORCE VIEW "MY_SCHEMA"."MY_TABLE"(
               "FIELD_1"
               ,"FIELD_2"
               ,"FIELD_3"
            

             

            But the select in the statement is formatted correctly (after your change):

            ...
            AS
               SELECT "FIELD_1"
                     ,"FIELD_2"
                     ,"FIELD_3"
            ...
            

             

            Perhaps you can do (again) some magic?

            • 3. Re: SQL Dev. 19.4 - Advanced format: columns/args alignment wrong when comma before with no space
              Mikhail Velikikh

              I added another OR

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

              to the less2Spaces block:

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

               

              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) ','
              ) -> {
                  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));
              }
              

               

              The create view statement got formatted as follows:

              CREATE OR REPLACE FORCE VIEW "MY_SCHEMA"."MY_TABLE" (
                 "FIELD_1"
                ,"FIELD_2"
                ,"FIELD_3"
              ) AS
                 SELECT "FIELD_1"
                       ,"FIELD_2"
                       ,"FIELD_3"
                   FROM "T1"
                       ,(
                    SELECT "FIELD_1"
                          ,"FIELD_2"
                          ,"FIELD_3"
                      FROM "T2"
                 );
              

              Does it fit the bill, Franco Soldera ?