Patching SQL Developer 20.2 with SQLcl’s Formatter

blank

Introduction

SQLcl 20.3.0 was released on October 29, 2020. It’s the first time I remember that we have a SQLcl version without the corresponding SQL Developer version. This is a pity because this SQLcl version also contains formatter fixes. And formatting code is something I do more often in the full-blown IDE than in the stripped down command line interface. In this blog post I show you how you can patch your SQL Developer installation and make the formatter fixes available there as well.

Disclaimer

What I do here is an experiment and most probably not really legal. It’s nothing I suggest you should do in your production environment. If you feel uneasy, don’t do it. What you do might destroy your SQL Developer installation.

Prerequisites

For this experiment you need

  • SQLcl 20.3.0
  • SQL Developer 20.2.0
  • JDK8 or JDK11

These software components are all installed on your computer. I do not explain in this blog post how to do it.

Solution Approach

SQLcl and SQL Developer provide a dbtools-common.jar file. This file contains the formatter (beside other things). We copy this file from the SQLcl installation to the SQL Developer installation.

That would be all, if SQL Developer would load the Guava library before opening a connection. But it does not because it was not required for version 20.2 of dbtools-common.jar. Therefore we put the VersionTracker.class and the SetServerOutput.class (which do not use Guava) from the original SQL Developer installation into the dbtools-common.jar file.

Another option would be to embed the classes from the guava.jar.

Step by Step Instruction

Step 1 – Quit SQL Developer

We are going to patch SQL Developer. This is not possible on Windows if SQL Developer is running. On other OS this might have strange effects. Therefore quit SQL Developer.

Step 2 – Rename SQLDev’s dbtools-common.jar

Find the dbtools-common.jar in your SQL Developer installation. In my case the file is in this directory: /Applications/SQLDeveloper20.2.0.app/Contents/Resources/sqldeveloper/sqldeveloper/lib. Rename this file to dbtools-common.original.jar.





Step 3 – Copy SQLcl’s dbtools-common.jar

Find the dbtools-common.jar in your SQLcl installation. In my case the file is in this directory: /usr/local/bin/sqlcl/lib. Copy the file to the SQLDev’s directory (where the dbtools-common.original.jar is located).





Step 4 – Patch dbtools-common.jar

Open a terminal window and change to the directory of the dbtools-common.original.jar file. Run there the following commands to create a patched version of dbtools-common.jar:





Here some explanation:

  • First we change to the directory where the files dbtools-common.original.jar (version 20.2.0) and dbtools-common.jar (version 20.3.0)  are stored. In your case the directory name may differ.
  • On the second and third line we extract the VersionTracker.class and the SetServerOutput.class from the dbtools-common.original.jar file. The classes are stored in a newly created directory oracle.
  • On the forth line we copy the previously extracted classes into the dbtools-common.jar file.
  • And on the last line we remove the oracle directory and its subdirectories.

Formatter Improvements

An example is the best to show the difference between version 20.2 and 20.3. I use the default formatter settings with just one change. “No breaks” for “Line Breaks On Boolean connectors”.

blank

And here are the results after formatting the code once.

There are two significant improvements of the formatter. Both are related to comment and whitespace handling and therefore are independent of an Arbori program.

  • The line of code after a comment is no longer indented additionally.
  • The line break after a single-line comment is no longer lost. As a result, the formatter will no longer comment out code.

While the first bug leads to badly formatted code, the second bug is really nasty. It breaks your code. This happens when you use single line comments on consecutive lines. In most cases this will lead to compile errors. However, I showed that the resulting code may be syntactically correct and a wrong formatting result could go unnoticed.

Updated on 2020-11-03, fixed typo in jar file names (db-common... to dbtools-common...).

Updated on 2020-11-05, added SetServerOutput.class to the patched jar file. Otherwise no SQL can be processed in a worksheet. Mentioned an alternative option to embed the classes from the guava.jar. Renamed chapter “Overview” to “Solution Approach”.

 

 

 

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.