Coming Soon: A More Forgiving Formatter

thatjeffsmith SQL Developer 11 Comments

Tell Others About This Story:

For 17.4, the formatter will attempt to format invalid SQL for you.

If the parser runs into a problem recognizing your code, it will format it as much as it can. But that’s not all.

It will ALSO go to the end of your statement, and traverse the SQL in the other direction and continue formatting as far as it can.

Let’s look at an example:


the parser squiggle after the first pipe indicates it doesn’t understand what’s going on at that point.

But hey, let’s format it anyway.

Cause hey, we’re trying to fix the code, but it’s so hard to read. We could really use the formatter’s help.


Ok, a bit more obvious where the problem is now, and it did format the SQL a good bit.

Adds the missing pipe |, formats again.



When does 17.4 come out?

Soon’ish. But the name should give you a pretty good hint.

Related Posts

Tell Others About This Story:

Comments 11

  1. I’m glad to hear that the formatter will someday be able to better handle SQL that contains errors. Perhaps that would have helped me to more quickly identify an issue that I encountered recently. The formatter in SQL Developer v17.2 does not seem to recognize ANSI join syntax.

    For example, I would like this unformatted SQL…

    with escalation_order AS
    select unit_id
    from order__table
    where name = ‘exception_ind’
    and upper(value) = ‘YES’
    select eo.unit_id
    from escalation_order eo
    join on item_table it on eo.unit_id = it.action_id AND it.type = ‘XX’;

    …to look something like this after formatting.

    WITH escalation_order AS
    name = ‘EXCEPTION_IND’
    AND UPPER( value ) = ‘YES’
    ) SELECT
    escalation_order eo
    JOIN ON item_table it
    ON eo.unit_id = it.action_id
    AND it.type = ‘XX’;

    Unfortunately, the formatter wouldn’t make any changes to the query. I tried selecting parts of the query and applying the formatter and it worked until I reached the “JOIN ON” part. It seems that the formatter does not like ANSI join syntax. This is disappointing since Oracle obviously supports it and we use it frequently.
    Additionally, I would like standard Oracle function names (ex. UPPER) to be considered as keywords for the purpose of capitalization.

  2. Jeff, for clarification, the error above occurred in a report that is PL/SQL-based. SQL-only reports are working.

    Regards, JT

    1. thatjeffsmith Post
    1. thatjeffsmith Post
  3. Jeff, hit by “Bug 27115078 java.lang.IllegalArgumentException: null connection not allowed” just as experimenting with command line reports…

    Can’t wait for 17.4!

    Regards, JT

    1. thatjeffsmith Post
    2. Not quite there yet…

      C:\sqldeveloper17.4\sqldeveloper\bin>sdcli64 reports generate -report “Redo heat map” -db DBNAME -file c:\temp\heatmap
      Command failed:
      java.lang.IllegalArgumentException: null connection not allowed
      at oracle.dbtools.db.DefaultConnectionIdentifier.(
      at oracle.dbtools.db.DefaultConnectionIdentifier.createIdentifier(
      at oracle.dbtools.raptor.metadata.AbstractDisplayModel.getQuery(
      at oracle.dbtools.raptor.metadata.AbstractDisplayModel.getQuery(
      at oracle.dbtools.raptor.controls.display.html.PLSQLHtmlGenerator.generateHtml(
      at oracle.dbtools.raptor.controls.display.DisplayHtml.generateStyleSpecificHtml(
      at oracle.dbtools.raptor.controls.display.DisplayHtml.generateModelHtml(
      at oracle.dbtools.raptor.controls.display.DisplayHtml.generateHtml(
      at oracle.dbtools.raptor.controls.display.DisplayHtml.generate(
      at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$
      at java.util.concurrent.Executors$
      at java.util.concurrent.ThreadPoolExecutor.runWorker(
      at java.util.concurrent.ThreadPoolExecutor$

    3. thatjeffsmith Post

Leave a Reply

Your email address will not be published. Required fields are marked *