SQL Developer

Howdy,

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;

BEGIN

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 (

  SELECT

    nvl(spf.validation, pf.validation) validation

  FROM

    dfl\_set\_proc\_fields spf

    JOIN dfl\_proc\_fields pf ON ( spf.procfldid = pf.procfldid )

  WHERE

    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

          || '"%')

  THEN

    RETURN 'D';

  END IF;

END LOOP;

RETURN l\_result;

END;

/

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: 19.2.0.206 with the formatter reset to the default

Thanks,

Brian..