1 2 Previous Next 28 Replies Latest reply on Jun 26, 2020 7:40 AM by David Sun 999

    SQL Developer 18.4 'custom format' for NVL

    user637346

      Hello all,

       

      I would like to format using custom format so that my queries do not break into several lines. Currently the formatter is formatting my NVL as below:

       

      SELECT 1

         FROM dual

      WHERE nvl(

               NULL,

               0,

               1

      ) = 0

       

      I would like to format as below:

       

      SELECT 1

         FROM dual

      WHERE NVL(NULL, 0, 1) = 0

        • 1. Re: SQL Developer 18.4 'custom format' for NVL
          Vadim Tropashko-Oracle

          Advanced Format-> Line Breaks -> On procedure arguments = false

           

          Or you want something more sophisticated, like Line Breaks -> On procedure arguments = true, with exception of NVL?

          • 2. Re: SQL Developer 18.4 'custom format' for NVL
            user637346

            Hi Vadim,

             

            I think I am looking for more sophisticated.

             

            I want Line Breaks -> on procedure arguments (TRUE)

             

            I do not want Line Breaks on database functions like NVL, TRUNC etc. in this scenario Line Breaks = FALSE

            • 3. Re: SQL Developer 18.4 'custom format' for NVL
              Vadim Tropashko-Oracle

              As you might have already edited custom format program, I'm posting the the amendment in increments, rather than the entire format.arbori:

               

              1. Additional condition to insert before the simpleIndentConditions:

              /* auxiliary */

              sysFunctionCalls: ([procedureCall) function_call | [procedureCall) function | [procedureCall) procedure_call)

                            & (?name='NVL' |  ?name='UPPER' | ?name='LOWER' | ?name='INSTR' | ?name='SUBSTR')

                            & name^ = procedureCall

              ;

               

              2. Amend simpleIndentConditions like this:

               

              simpleIndentConditions_1: (         -- renamed

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

              ...

              | [node) xmlelement[58,78) & [node^) XML_function )

              & node^^ = procedureCall          -- added attribute, not new condition!

              ;

               

              simpleIndentConditions: simpleIndentConditions_1 - sysFunctionCalls;   -- added

               

              3.  After  commasInProc:

               

              -- commasInSys: commasInProc & sysFunctionCalls;  -- not needed

              commasElsewhere: commasInProc - sysFunctionCalls;      

               

              extraBrkBefore:

                ( _extraBrkBefore - commasInProc)

              | :breaksBeforeComma & :breaksProcArgs & commasElsewhere

              ->;

               

              extraBrkAfter:

                ( _extraBrkAfter - commasInProc)

              | :breaksAfterComma & :breaksProcArgs & commasElsewhere

              ->;

               

              The format test:

               

              SELECT

                  not_nvl(

                      NULL,

                      0

                  )

              FROM

                  dual

              WHERE

                  nvl(NULL, 0, 1) = 0;

              • 4. Re: SQL Developer 18.4 'custom format' for NVL
                user637346

                I'm pretty new to "format.arbori" and never actually edited it. I have tried changes that you have recommended. Now when I try to do format test, I see my format stopped working completely. May be I made a mistake?

                 

                I have attached the file in .txt format here

                • 5. Re: SQL Developer 18.4 'custom format' for NVL
                  user637346

                  attaching the latest updated file. disregard the previous file

                  • 6. Re: SQL Developer 18.4 'custom format' for NVL
                    Vadim Tropashko-Oracle

                    I imported your file and it does work:

                     

                    arbori.png

                     

                    Just for the reference, enclosed please find my 18.4 format.arbori

                    • 7. Re: SQL Developer 18.4 'custom format' for NVL
                      user637346

                      Yep, I now see nvl is placed in single line. could you also please suggest changes for the following:

                       

                      1. TO_CHAR, TO_NUMBER, TRUNC still braking lines

                      2. can we ensure system functions(NVL, TRUNC etc) are always in capital letters. I could keep on adding the functions if I know where to add in the format.arbori

                      • 8. Re: SQL Developer 18.4 'custom format' for NVL
                        Vadim Tropashko-Oracle

                        Enclosed please find the amended format program to import into 18.4

                         

                        custom.png

                        • 9. Re: SQL Developer 18.4 'custom format' for NVL
                          _Dylan_

                          This is very helpful. I like to have breaks on arguments for procedure calls in pl/sql, but don't necessarily want functions to behave that way in sql statements.

                          • 10. Re: SQL Developer 18.4 'custom format' for NVL
                            user637346

                            Hi Vadim,

                             

                            Could you please provide this:

                             

                            1. I do not want any line break for parameters unless it is procedure or function header. It has to break only when it is beyond the maximum set line size. In this case, I want it to break line for each parameter. This should also be the case for Concatenation operator ||

                             

                            2. The open and close parenthesis should be like below

                             

                            PROCEDURE proc_name (param1       OUT     NUMBER,

                                                                        param2        IN        VARCHAR2)

                             

                            Thanks!

                            • 11. Re: SQL Developer 18.4 'custom format' for NVL
                              Vadim Tropashko-Oracle

                              Tested in soon to be released 19.2. Removing indentation of function parameters:

                               

                              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

                              ...

                               

                              Output:

                               

                              CREATE PACKAGE BODY test_format AS

                               

                                  PROCEDURE proc_name ( param1   OUT   NUMBER,

                                                        param2   NUMBER,

                                                        param3   IN    VARCHAR2 ) AS

                                  BEGIN

                                      p1(a);

                                      p2(a, b);

                                      p2(1, proc(1, 2));

                                      p3(a, b, c);

                                      p4(a, b, c, proc(1, 2, 3));

                                      p5(a, b, c, d, e);

                                      p7(a, b, c, d, e,

                                         f, g);

                                      p10(1, 2, 3, 4, 5,

                                          6, 7, 8, 9, 0);

                               

                                      p100(1, 2, 3, 4, 5,

                                           6, 7, 8, 9, 0,

                                           1, 2, 3, 4, 5,

                                           6, 7, 8, 9, 0,

                                           1, 2, 3, 4, 5,

                                           6, 7, 8, 9, 0,

                                           1, 2, 3, 4, 5,

                                           6, 7, 8, 9, 0,

                                           1, 2, 3, 4, 5,

                                           6, 7, 8, 9, 0,

                                           1, 2, 3, 4, 5,

                                           6, 7, 8, 9, 0,

                                           1, 2, 3, 4, 5,

                                           6, 7, 8, 9, 0,

                                           1, 2, 3, 4, 5,

                                           6, 7, 8, 9, 0,

                                           1, 2, 3, 4, 5,

                                           6, 7, 8, 9, 0);

                               

                                  END;

                               

                              END;

                               

                              It breaks list of arguments after each 5th argument, which you can modify here:

                               

                              breakAfterEvery5thArg: commasInProc & !:breaksProcArgs

                              ->{

                                  argNum++;  

                                  var proc = tuple.get("procedureCall");   

                                  var name = target.src[proc.from].content;

                                  var dot = target.src[proc.from+1].content;

                                  if( dot == '.' )

                                      name = name + '.' + target.src[proc.from+2].content;

                                  if( proc != priorProc ) {

                                      argNum = 1;

                                      priorProc = proc;

                                  } else {

                                      if( argNum%5 == 0 ) {

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

                                          var procIndent = struct.getNewline(proc.from);

                                          if( procIndent == null )

                                              procIndent = "\n";

                                          var pad = new java.lang.StringBuilder();

                                          var i;

                                          for( i = 0; i <= name.length(); i++ ) 

                                             pad.append(" ");

                                          struct.putNewline(node.to, procIndent+pad.toString());

                                      }       

                                  }

                              }

                              • 12. Re: SQL Developer 18.4 'custom format' for NVL
                                Pavel Gl

                                Hallo Vadim,

                                 

                                how i can add empty space to the lines after LOOP statement?

                                currently i have

                                  FOR i IN 1..5 LOOP

                                  dummy(i);

                                  END LOOP;

                                 

                                and i want

                                  FOR i IN 1..5 LOOP

                                    dummy(i);

                                  END LOOP;

                                 

                                Tank you!

                                 

                                P.S. I use custom format file

                                https://drive.google.com/file/d/1nNC4VGrF-VRKJRnrLPQWESqxh8_-op4_/view?usp=sharing

                                 

                                Regards,

                                Pavel

                                • 13. Re: SQL Developer 18.4 'custom format' for NVL
                                  Vadim Tropashko-Oracle

                                  simpleIndentConditions:

                                  ...

                                  | [node) standard_actions

                                  | [node) stmt & [node-1) 'LOOP'

                                  | [node) stmt & [node-1) 'BEGIN'

                                  | [node) stmt & [node^) seq_of_stmts

                                  ...

                                  1 2 Previous Next