Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

Coming Soon: A More Forgiving Formatter

1 Min Read

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.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

11 Comments

  1. Don Zouras Reply

    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. John Thomas Reply

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

    Regards, JT

  3. John Thomas Reply

    Jeff, for clarification, plain SQL reports do now work. The failing one is a PL/SQL report.

    • thatjeffsmith Post Author

      Great. So that would be a separate bug. I’ll re-open the existing with the ‘new’ test case. Thanks for the heads-up.

  4. TR Reply

    Jeff,

    It’s here!

    What other enhancements can we find?

    BTW Happy New Year and thanks for the support

    • thatjeffsmith Post Author

      It’s mostly just bug fixes. The insight support for plsql packages is better, the formatter got more tweaks.

      A few forum requests were implemented.

  5. John Thomas Reply

    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

    • John Thomas

      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)

    • thatjeffsmith Post Author

      hmmm, the developer marked that bug as fixed…
      27115078 – SDCLI REPORTS GENERATION FILES WITH NULL CONNECTION NOT ALLOWED

Write A Comment