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';
IF i_dstype = 'P' THEN
-- 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
JOIN dfl_proc_fields pf ON ( spf.procfldid = pf.procfldid )
spf.dsid = i_dsid
AND REGEXP_LIKE ( nvl(spf.validation, pf.validation)
|| '"' )
l_validation := json(rec.validation);
IF ( l_validation.exist('dependencies')
AND l_validation.get('dependencies').to_char(false) LIKE
|| '"%' )
OR ( l_validation.exist('exceptions')
AND l_validation.get('exceptions').to_char(false) LIKE
OR ( l_validation.exist('dependencies_db')
AND l_validation.get('dependencies_db').to_char(false) LIKE
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: 184.108.40.206 with the formatter reset to the default