@PhilippSalvisberg no longer work for Trivadis - Part of Accenture and no one at Trivadis/Accenture will continue this project. Therefore, this GitHub repository was archived on 30 August 2024.
However, @PhilippSalvisberg decided to create a fork and continue this project under PhilippSalvisberg/plsql-formatter-settings.
This repository provides formatter settings for the coding style rules of the Trivadis PL/SQL & SQL Coding Guidelines.
Settings are primarily provided for
- Oracle SQLcl, Version 23.4.0
- Oracle SQL Developer, Version 23.1.1.345 (requires
dbtools-common.jarfrom SQLcl 23.4.0 usingParseNode.classfrom SQL Developer 23.1.1.345)
These settings have been defined and tested with the product versions mentioned above. They might not work in other versions.
Please note, that these settings cannot be used in Oracle SQL Developer for VSCode 23.4.0. And it does not work in the embedded version of SQLcl 23.3.1.0, which cannot run JavaScript.
JDK 11 is required for SQLDev and SQLcl. The standalone tvdformat.jar requires JDK 17 or newer.
See releases for settings supporting older versions.
SQL Developer is slowly reaching the end of its life cycle. The days when SQL Developer and SQLcl were released almost simultaneously every quarter are long gone. We can continue to expect regular SQLcl releases, but with the availability of SQL Developer as a Visual Studio Code Extension, SQL Developer will only be updated sporadically.
A new SQLcl version typically comes with enhancements and bug fixes in the area of PL/SQL and SQL grammar. And this also requires an adaptation of the formatting rules due to symbol name changes etc. And as a result, these formatting rules can actually only be used in SQLcl.
However, we know that the grammars and the formatter are provided in a JAR called dbtools-common.jar. And this JAR file also exists in the SQL Developer distribution. This means that in order to be able to use the current formatting rules in SQL Developer, we have to copy the dbtools-common.jar file from SQLcl to SQL Developer. Unfortunatelly the classes are not 100% compatible with SQL Developer. As a a result, we have to keep some original classes, which complicates the patching process a bit.
Here's the full procedure to use dbtools-common.jar from SQLcl 23.4.0 in SQL Developer 23.1.1.345:
-
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.
-
Rename SQL Developer’s
dbtools-common.jarFind the
dbtools-common.jarin your SQL Developer installation. In our case it's in/Applications/SQLDeveloper.app/Contents/Resources/sqldeveloper/sqldeveloper/lib. Rename this file todbtools-common.original.jar. -
Copy SQLcl’s
dbtools-common.jarFind the
dbtools-common.jarin your SQLcl installation. In our case it's in/usr/local/bin/sqlcl/lib. Copy the file to the SQL Developer’s directory (wheredbtools-common.original.jaris located). -
Patch SQLcl's
dbtools-common.jarOpen a terminal window in the
libfolder wheredbtools-common.original.jaranddbtools-commmon.jarare located and run the following commands to copy the classoracle.dbtools.parser.ParserNode.classtodbtools-common.jar:jar -xvf dbtools-common.original.jar oracle/dbtools/parser/ParseNode.class jar -u0vMf dbtools-common.jar oracle/dbtools rm -rf oracle
This step is necessary to ensure that the "Code Outline" continues to work in SQL Developer.
-
Clear SQL Developer's cache
Delete the following directory:
- On Windows:
%APPDATA%\SQL Developer\system23.1.1.345.2114\system_cache - On other platforms:
$HOME/.sqldeveloper/system23.1.1.345.2114/system_cache
This step is necessary to ensure no other version of
dbtools-common.jaris used from the cache. All window preferences will be reset to factory settings. However, all other preferences including your connections are preserved. - On Windows:
-
Start SQL Developer
Open an editor and test if the formatter and code outline is working.
Please note that these settings do not comply with rule 5. Line breaks are placed after a comma and not before. All other rules are followed. However, you can easily change this in the preferences.
Clone this repository or download the ZIP file and extract it.
See sqlcl/README.md.
- Start SQL Developer
- Open
Preferences - Select
Code Editor->Format->Advanced Format - Press
Import...
- Select
trivadis_advanced_format.xml - Press
Open - Select
Code Editor->Format->Advanced Format->Custom Format - Press
Import...
- Select
trivadis_custom_format.arbori - Press
Open - Press
OKto save the settings
See hook/README.md.
The Arbori program implements a lightweight formatter which accepts different coding styles. The primary scope of the formatter is the following SQL statements:
create functioncreate packagecreate package bodycreate procedurecreate triggercreate typecreate type bodycreate view(including thesubquerypart ofcreate materialized view)deleteinsertmergeselectupdate
Other statements like create table or create tablespace are basically left as is. There are some exceptions like adding line breaks after reaching the maximum line length and changing the case of keywords and identifiers. But that's it.
If you need a heavyweight formatter to enforce the conformity of your code, you will need to use the default Arbori program provided by the SQL Developer team.
In any case, you can use our SQLcl JavaScript format.js, the standalone formatter or the Git pre-commit hook to format your code.
The formatter is enabled by default. You can disable the formatter with a single-line or multi-line comment containing @formatter:off. From that point on, whitespace are kept as is. To re-enable the formatter use @formatter:on.
| Supported Marker Comment | Action | Origin |
|---|---|---|
@formatter:off |
Disables formatter | Eclipse IDE |
@formatter:on |
Enables formatter | Eclipse IDE |
noformat start |
Disables formatter | PL/SQL Developer |
noformat end |
Enables formatter | PL/SQL Developer |
The marker comments are case sensitive.
Here's an example showing the code after calling the formatter:
-- @formatter:off
select decode(dummy, 'X', 1
, 'Y', 2
, 'Z', 3
, 0) /* @formatter:on */
from dual;SQL Developer uses its own parse tree query language called Arbori for its advanced formatter configuration. Here is some additional information that might be useful if you plan to tweak the behavior of the formatter yourself.
- SQL Developer 23.1 User Guide, Code Editor: Format
- Formatting Code With SQL Developer
- Formatter Callback Functions
- Arbori Starter Manual
- Semantic Analysis with Arbori
- Arbori Semantic Actions
- Custom Formatting in SQLDev 4.2
- Formula for Formatting
- Custom Syntax Coloring
- Arbori 20.2
- Java Script Conditions
Thank you, Vadim Tropashko for providing this valuable information.
To get the most out of the dynamic JavaScript actions from an Arbori program, you should know the following global variables and their corresponding Java class.
| Variable | Type | JAR File |
|---|---|---|
struct |
oracle.dbtools.app.Format | dbtools-common.jar |
target |
oracle.dbtools.parser.Parsed | dbtools-common.jar |
tuple |
HashMap<String, oracle.dbtools.parser.ParseNode> | dbtools-common.jar |
logger |
oracle.dbtools.util.Logger | dbtools-common.jar |
Formatter settings are also provided for the following products:
| Product | Version | File |
|---|---|---|
| Allround Automations PL/SQL Developer | 14.0.6 | trivadis_beautifier.br |
| JetBrains DataGrip | 2021.1 | trivadis.xml |
| Quest Toad for Oracle | 14.1 | trivadis_fmtplus.opt |
Please refer to the vendor's documentation for instructions on how to import these settings.
The Trivadis PL/SQL & SQL Formatter Settings are licensed under the Apache License, Version 2.0. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0.