Skip to Main Content
Forums

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

Franco SolderaDec 30 2019 — edited Jan 12 2020

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
This post has been answered by Vadim Tropashko-Oracle on Dec 30 2019
Jump to Answer

Comments

Answer

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

}

Marked as Answer by Franco Soldera · Sep 27 2020
1 person found this helpful
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?

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

1 person found this helpful
Franco Soldera

Magic!!! Thanks again :-)

1 - 4

Post Details

Added on Dec 30 2019
#sql-developer
4 comments
440 views