Patching SQL Developer 20.2 with SQLcl’s Formatter

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 (besides 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.

cd /Applications/SQLDeveloper20.2.0.app/Contents/Resources/sqldeveloper/sqldeveloper/lib
mv dbtools-common.jar dbtools-common.original.jar

cd C:\app\sqldeveloper20.2.0\sqldeveloper\lib
ren dbtools-common.jar 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).

cd /usr/local/bin/sqlcl/lib
cp dbtools-common.jar /Applications/SQLDeveloper20.2.0.app/Contents/Resources/sqldeveloper/sqldeveloper/lib

cd C:\app\sqlcl\lib
copy dbtools-common.jar C:\app\sqldeveloper20.2.0\sqldeveloper\lib

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:

cd /Applications/SQLDeveloper20.2.0.app/Contents/Resources/sqldeveloper/sqldeveloper/lib
jar -xvf dbtools-common.original.jar oracle/dbtools/db/VersionTracker.class
jar -xvf dbtools-common.original.jar oracle/dbtools/raptor/newscriptrunner/commands/SetServerOutput.class
jar -u0vMf dbtools-common.jar oracle/dbtools
rm -rf oracle

cd C:\app\sqldeveloper20.2.0\sqldeveloper\lib
jar -xvf dbtools-common.original.jar oracle/dbtools/db/VersionTracker.class
jar -xvf dbtools-common.original.jar oracle/dbtools/raptor/newscriptrunner/commands/SetServerOutput.class
jar -u0vMf dbtools-common.jar oracle/dbtools
rmdir /s /q oracle

Here is 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 fourth 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”.

And here are the results after formatting the code once.

There are two significant improvements to 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”.