Formatting Oracle with SQLinForm
Oracle SQL and PL/SQL have a long history and a rich, expressive syntax. Formatting Oracle code correctly requires understanding constructs that are unique to this database. Here is what makes it complex.
Challenges Formatting Oracle SQL
Anonymous blocks and nested blocks
Oracle PL/SQL anonymous blocks can be deeply nested. Each nested block has its own DECLARE, BEGIN, and EXCEPTION sections. Tracking the correct indentation level across multiple levels of nesting — especially when exception handlers contain their own blocks — is one of the more complex formatting tasks in SQLinForm.
The slash terminator and statement detection
While the slash is elegant, it creates a challenge: the formatter must distinguish between a division operator and a block terminator. A slash on its own line at the leftmost column is the terminator; a slash anywhere else is arithmetic. Getting this right requires positional awareness during parsing.
EXECUTE IMMEDIATE and dynamic SQL
Dynamic SQL inside EXECUTE IMMEDIATE strings is a known hard problem for any formatter. The SQL string is just a string literal to the parser, so it cannot be reformatted. SQLinForm handles this transparently — the outer code is formatted, the dynamic SQL inside is preserved — but detecting where the dynamic SQL ends, especially with concatenation, requires careful handling.
CONNECT BY and hierarchical clause ordering
The hierarchical query clauses (START WITH, CONNECT BY, NOCYCLE, PRIOR) interact with WHERE and ORDER BY in ways that differ from standard query formatting. Determining the correct line break positions and indentation for each clause requires specific Oracle-aware logic.
Oracle-specific DDL variants
Oracle DDL has many unique extensions: ORGANIZATION INDEX, TABLESPACE clauses, PARTITION BY, STORAGE parameters, DROP TABLE PURGE, and more. Each adds complexity to the DDL formatter, since these multi-keyword clauses need to be recognized and handled as units rather than individual keywords.
Package specifications and bodies
Oracle PACKAGEs consist of a specification (the public interface) and a body (the implementation), each declared separately. A package body can contain dozens of procedures and functions with nested blocks. Maintaining consistent indentation across a large package body, where each procedure begins at the same level, requires careful state management throughout the formatting pass.
About SQLinForm: SQLinForm is a SQL formatter supporting 25+ database dialects with 200+ formatting options — available for VS Code, SSMS, JetBrains, Notepad++, DBeaver, and as an online formatter. Your SQL never leaves your machine.