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
    Franco Soldera Dec 30, 2019 1:56 PM

      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
      • 283 Views
      • Tags:
        • 1. Re: SQL Dev. 19.4 - Advanced format: columns/args alignment wrong when comma before with no space
          Vadim Tropashko-Oracle
          Vadim Tropashko-Oracle Dec 30, 2019 6:40 PM (in response to Franco Soldera)

          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
            Franco Soldera Jan 3, 2020 12:42 PM (in response to Vadim Tropashko-Oracle)

            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
              Mikhail Velikikh Jan 3, 2020 1:53 PM (in response to Franco Soldera)

              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 ?