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:

Before

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.

Almost

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.

After

Ta-da!

When does 17.4 come out?

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

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
    (
    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’;

    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
      Author
    1. thatjeffsmith Post
      Author
  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
      Author
      1. 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.(DefaultConnectionIdentifier.java:29)
        at oracle.dbtools.db.DefaultConnectionIdentifier.createIdentifier(DefaultConnectionIdentifier.java:21)
        at oracle.dbtools.raptor.metadata.AbstractDisplayModel.getQuery(AbstractDisplayModel.java:393)
        at oracle.dbtools.raptor.metadata.AbstractDisplayModel.getQuery(AbstractDisplayModel.java:377)
        at oracle.dbtools.raptor.controls.display.html.PLSQLHtmlGenerator.generateHtml(PLSQLHtmlGenerator.java:38)
        at oracle.dbtools.raptor.controls.display.DisplayHtml.generateStyleSpecificHtml(DisplayHtml.java:391)
        at oracle.dbtools.raptor.controls.display.DisplayHtml.generateModelHtml(DisplayHtml.java:325)
        at oracle.dbtools.raptor.controls.display.DisplayHtml.generateHtml(DisplayHtml.java:261)
        at oracle.dbtools.raptor.controls.display.DisplayHtml.generate(DisplayHtml.java:202)
        at oracle.dbtools.raptor.report.headless.ReportsProcessor$GenerateReportTask.doWork(ReportsProcessor.java:101)
        at oracle.dbtools.raptor.report.headless.ReportsProcessor$GenerateReportTask.doWork(ReportsProcessor.java:39)
        at oracle.dbtools.raptor.backgroundTask.RaptorTask.call(RaptorTask.java:199)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask.run(RaptorTaskManager.java:702)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)

      2. thatjeffsmith Post
        Author

Leave a Reply

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