Forum Stats

  • 3,722,879 Users
  • 2,244,429 Discussions
  • 7,850,126 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Formatting PL/SQL in SQLDev 20.2.0 - Unwanted Indentation after Comment - Solution?

Philipp Salvisberg
Philipp Salvisberg Member Posts: 115 Bronze Badge
edited July 2020 in SQL Developer

When I format the following Code in SQL Developer 20.2

BEGIN    -- a comment    NULL; END; / 

I get this the first time:

BEGIN    -- a comment       NULL; END; / 

and this after formatting it the second time:

BEGIN    -- a comment          NULL; END; / 

Every time an additional (unwanted) indentation is added before NULL;. This worked fine with SQL Developer 19.4.0.

Same behaviour with multi-line comments. Same behaviour on macOS and Windows.

Any Idea how I can avoid this additional indentation with SQL Developer 20.2.0?

I hoped we get access to comment nodes in Arbori, based on this thread SQL Developer 19.2.1 - Custom format: line break on hints, columns/argument alignment . But it does not seem so, so I really have no idea how to change formatting when comments are involved.

Thanks

Philipp

P.S.

I use this settings:

sqldev-adv-formatter-settings.png

Philipp SalvisbergÜmit ŞAMLIUser_8AO7BMiloBandit-Oracle

Best Answer

  • Vadim Tropashko-Oracle
    Vadim Tropashko-Oracle Posts: 1,220 Employee
    edited June 2020 Accepted Answer

    This is a bug.

    Workaround: append the following rule to Custom Format Arbori program:

    fixComments: runOnce -> {

        var LexerToken = Java.type('oracle.dbtools.parser.LexerToken'); 

        var tokens  =  LexerToken.parse(target.input,true);  // parse with WS symbols

        var Token = Java.type('oracle.dbtools.parser.Token');

        var Substitutions = Java.type('oracle.dbtools.parser.Substitutions');

        var substitutions = new Substitutions(target.input);

        var commentEnd = 0;

        for( i = 0; i < tokens.length; i++ ) {

            if( commentEnd == 0 && (tokens[i].type == Token.COMMENT || tokens[i].type == Token.LINE_COMMENT) ) {

                commentEnd = tokens[i].end;

                continue;

            }

            if( tokens[i].content == " " && commentEnd != 0 ) {

                substitutions.put(tokens[i].begin,tokens[i].end,"");

                continue;

            }

            if( tokens[i].type != Token.WS )

                commentEnd = 0;   

        }

        target.input = substitutions.transformInput();

    }

    Witness it working:

    fix comments.png

    This hack is based upon an observations that if I nuke the indentation of the line following a comment, then the format works properly. 

    The Substitutions class is helpful when writing a translator, like in this example; therefore, it warrants little more explanation. It is basically a sugarcoated map of position intervals which content would be substituted with the mapped strings. The text substitution entries can be added in arbitrary order, and the Substitutions class takes care of applying them properly. For example, java program

    public static void main( String[] args ) {

        Substitutions repl = new Substitutions("0123456789012345");

        repl.put(1,2,"[1,2)");

        repl.put(11,12,"[11,12)");

        System.out.println("**** repl.toString():");

        System.out.println(repl.toString());

        System.out.println("**** repl.transformInput():");

        System.out.println(repl.transformInput());

    }

    outputs:

    **** repl.toString():

    0

    ---

    1

    --->>>

    [1,2)

    ---

    234567890

    ---

    1

    --->>>

    [11,12)

    ---

    2345

    **** repl.transformInput():

    0[1,2)234567890[11,12)2345

    Philipp SalvisbergUser_8AO7B

Answers

  • Vadim Tropashko-Oracle
    Vadim Tropashko-Oracle Posts: 1,220 Employee
    edited June 2020 Accepted Answer

    This is a bug.

    Workaround: append the following rule to Custom Format Arbori program:

    fixComments: runOnce -> {

        var LexerToken = Java.type('oracle.dbtools.parser.LexerToken'); 

        var tokens  =  LexerToken.parse(target.input,true);  // parse with WS symbols

        var Token = Java.type('oracle.dbtools.parser.Token');

        var Substitutions = Java.type('oracle.dbtools.parser.Substitutions');

        var substitutions = new Substitutions(target.input);

        var commentEnd = 0;

        for( i = 0; i < tokens.length; i++ ) {

            if( commentEnd == 0 && (tokens[i].type == Token.COMMENT || tokens[i].type == Token.LINE_COMMENT) ) {

                commentEnd = tokens[i].end;

                continue;

            }

            if( tokens[i].content == " " && commentEnd != 0 ) {

                substitutions.put(tokens[i].begin,tokens[i].end,"");

                continue;

            }

            if( tokens[i].type != Token.WS )

                commentEnd = 0;   

        }

        target.input = substitutions.transformInput();

    }

    Witness it working:

    fix comments.png

    This hack is based upon an observations that if I nuke the indentation of the line following a comment, then the format works properly. 

    The Substitutions class is helpful when writing a translator, like in this example; therefore, it warrants little more explanation. It is basically a sugarcoated map of position intervals which content would be substituted with the mapped strings. The text substitution entries can be added in arbitrary order, and the Substitutions class takes care of applying them properly. For example, java program

    public static void main( String[] args ) {

        Substitutions repl = new Substitutions("0123456789012345");

        repl.put(1,2,"[1,2)");

        repl.put(11,12,"[11,12)");

        System.out.println("**** repl.toString():");

        System.out.println(repl.toString());

        System.out.println("**** repl.transformInput():");

        System.out.println(repl.transformInput());

    }

    outputs:

    **** repl.toString():

    0

    ---

    1

    --->>>

    [1,2)

    ---

    234567890

    ---

    1

    --->>>

    [11,12)

    ---

    2345

    **** repl.transformInput():

    0[1,2)234567890[11,12)2345

    Philipp SalvisbergUser_8AO7B
  • Philipp Salvisberg
    Philipp Salvisberg Member Posts: 115 Bronze Badge
    edited June 2020

    Vadim,

    Thank you very much. This works.

    And the explanation of the Substitutions class is very helpful. I have to look at the code a bit closer... That's new in 20.2 that one can change the target.input with an effect on the subsequent processing. Cool.

    Thanks again

    Philipp

  • Ümit ŞAMLI
    Ümit ŞAMLI Member Posts: 12 Red Ribbon
    edited June 2020

    Hi,

    Works fine for single comment. But for 2 and more comments the same problem persists.

    /**/ comments is correct

    -- comment is not correct

     

    BEGIN      --a comment      --a comment       NULL;END;

    first

    BEGIN      --a comment  --a comment           NULL;END;

    second

    BEGIN      --a comment  --a comment               NULL;END;
  • Vadim Tropashko-Oracle
    Vadim Tropashko-Oracle Posts: 1,220 Employee
    edited June 2020

    fixComments: runOnce -> {

        var LexerToken = Java.type('oracle.dbtools.parser.LexerToken');

        var tokens  =  LexerToken.parse(target.input,/* parse with WS symbols =*/ true); 

        var Token = Java.type('oracle.dbtools.parser.Token');

        var Substitutions = Java.type('oracle.dbtools.parser.Substitutions');

        var substitutions = new Substitutions(target.input);

        var commentEnd = 0;

        var firstSpace = 0;

        for( i = 0; i < tokens.length; i++ ) {

            if( tokens[i].type == Token.COMMENT || tokens[i].type == Token.LINE_COMMENT ) {

                commentEnd = tokens[i].end;

                var firstSpace = 0;

                continue;

            }

            if( tokens[i].content == " " && commentEnd != 0 && firstSpace == 0) {

                firstSpace = tokens[i].begin;

                commentEnd = 0;

                continue;

            }

            if( tokens[i].type != Token.WS ) {

                if( tokens[i].type != Token.COMMENT && tokens[i].type != Token.LINE_COMMENT && firstSpace != 0 )

                    substitutions.put(firstSpace,tokens[i].begin,"");

                firstSpace = 0;          

                commentEnd = 0;  

            }

        }

        target.input = substitutions.transformInput();

    }

    Philipp SalvisbergÜmit ŞAMLIMiloBandit-Oracle
  • Christian Rackerseder
    Christian Rackerseder Member Posts: 24 Blue Ribbon
    edited July 2020

    Hi Vadim,

    thank you for this quick solution.

    Please can you make sure that your updated answer is marked as the "correct answer".

    Thank you Christian

  • Philipp Salvisberg
    Philipp Salvisberg Member Posts: 115 Bronze Badge
    edited July 2020

    Hello, Christian,

    Actually, it is the responsibility of the person asking a question to mark it as correct. In this case, it's me. Vadim answered my question based on my example. He stated that it is a bug and showed me how to work around it. It was not my expectation that the workaround handles all possible cases. I was happy to have a starting point. And since it is a bug, I expect it to be fixed in one of the coming SQLDev releases. Hence I marked his answer as “correct”. All good.

    However, you have decided to "hijack" this thread at the time I've already marked the question as “correct” and showed that the workaround does not handle all cases. IMO this is worth a dedicated question (with a link to this one). Nonetheless, the initial question is answered fully (bug, workaround, good explanation of the workaround) and it would be wrong to mark the answer containing just the new code as correct. Since it is incomplete and addresses a similar but different question (or at least a different test example).

    I suggest that next time you create a dedicated question. Then you can decide which answer is the correct one ;-)

    Regards,

    Philipp

  • Christian Rackerseder
    Christian Rackerseder Member Posts: 24 Blue Ribbon
    edited July 2020

    well I thought that a forum would be there to help other people that hit the same problem.

    the second solution seemed to me to be "better"/"enhanced" as it covers a broader range of problems.

    Please take my apologies for misusing your thread.

Sign In or Register to comment.