2 Replies Latest reply on Aug 12, 2019 5:59 PM by Brian Shaver

    SQL Developer formatter breaks my code

    Brian Shaver



      I'm really optimistic for the future of Oracle development tools. However, as a long time user now of SQL Developer, I am continuing to struggle with its formatter feature. We're so close to a truly functional code formatter with SQLcl supporting now the Advanced Format rules, it opens up vast possibilities to integrating SQLcl as a formatter with almost anything: git hook, editors like Visual Studio Code or even Vim. But I drift from my point, the formatter does not seem to work. I often have minor complaints with its output, things not aligned as expected, or code that shifts each time if you run the formatter multiple times. Now I've got an example where SQL Developer formatter is taking code which WOULD compile and introducing syntax errors.


      Place the following into a file and run it with any settings in the Advanced formatter that you'd like. I'm just using the out of the box settings (Reset) thinking that perhaps my attempts to configure them had introduced some the issue.


      CREATE OR REPLACE FUNCTION get_cross_form (

          i_field_code    IN   VARCHAR2

          , i_dsid          IN   NUMBER

          , i_dstype        IN   VARCHAR2

          , i_procedureid   IN   NUMBER

        ) RETURN CHAR IS

          l_result       CHAR(1) := 'N';

          l_cnt          PLS_INTEGER;

          l_validation   json;


          IF i_dstype = 'P' THEN

            RETURN l_result;

          END IF;


          -- we have a cross form variable

          l_result := 'Y';


          -- check to see if there is a cross form dependency

          FOR rec IN (


              nvl(spf.validation, pf.validation) validation


              dfl_set_proc_fields spf

              JOIN dfl_proc_fields pf ON ( spf.procfldid = pf.procfldid )


              spf.dsid = i_dsid

              AND REGEXP_LIKE ( nvl(spf.validation, pf.validation)

              , '"(dependencies|dependencies_db|exceptions)".*"'

                || i_field_code

                || '"' )

          ) LOOP

            l_validation := json(rec.validation);

            IF ( l_validation.exist('dependencies')

                 AND l_validation.get('dependencies').to_char(false) LIKE


                   || i_field_code

                   || '"%' )

                 OR ( l_validation.exist('exceptions')

                   AND l_validation.get('exceptions').to_char(false) LIKE


                     || i_field_code

                     || '"%')

                  OR ( l_validation.exist('dependencies_db')

                    AND l_validation.get('dependencies_db').to_char(false) LIKE


                    || i_field_code

                    || '"%')


              RETURN 'D';

            END IF;

          END LOOP;



          RETURN l_result;




      Its formatted pretty much as I would like it to be, or at least I can live with the current formatting. Attempt to format this with any settings I can think of and it produces output which can't be compiled splitting the concatenations || onto multiple lines.


      Using the out-of the box formatter:



      Am I missing something here, or does this seem like a bug?


      If anyone has formatter settings that can handle code like this, and would be willing to share, it would be greatly appreciated.


      I am running SQL Developer: with the formatter reset to the default