Oracle SQL Formatter

Oracle codebases carry decades of accumulated syntax. A single package can contain a modern MERGE written last year, a hierarchical CONNECT BY query from the nineties, and a join expressed with the (+) operator that predates ANSI join syntax in Oracle entirely. All of it still runs, and all of it has to be readable.

With the PL/SQL dialect selected, this formatter parses the Oracle-specific constructs a generic SQL parser rejects outright: DECLARE … BEGIN … END; blocks, the bare / that terminates them in SQL*Plus, START WITH and CONNECT BY PRIOR, the (+) outer join marker, and DUAL.

Oracle-specific syntax this handles

• DECLARE … BEGIN … END;. Anonymous blocks are recognised, with DECLARE, BEGIN and END; as their own lines and the SQL statements inside them formatted normally.
• The / terminator. Kept on its own line. It is not part of the PL/SQL language — it is the SQL*Plus instruction meaning "execute the buffer", which is why a block needs both a closing END; and a following slash while an ordinary SELECT needs only a semicolon.
• CONNECT BY hierarchies. START WITH, CONNECT BY PRIOR and ORDER SIBLINGS BY each get a top-level line, and LEVEL is recognised as the pseudocolumn it is. Hierarchical queries are exactly the case where clause-per-line layout makes the recursion direction obvious.
• The (+) outer join operator. Preserved on the side of the predicate where you wrote it, which is the side that gets the nulls padded — a detail worth being able to see at a glance, since it is the reverse of how LEFT JOIN reads.
• MERGE. The USING subquery, the parenthesised ON predicate that Oracle requires, and the WHEN MATCHED / WHEN NOT MATCHED branches are each broken out.
• DUAL. The one-row, one-column system table used whenever you need to select an expression with no real source, as in SELECT SYSDATE FROM DUAL. Formatted like any other table reference.

Legacy joins and why they are worth spotting

Oracle supported (+) long before it supported ANSI LEFT JOIN, and enormous amounts of production code still uses it. The operator marks the deficient side — the table that gets null-extended — so WHERE e.department_id = d.department_id(+) is a left outer join keeping all employees, even though the marker sits on the departments side.

The form has real limitations that ANSI syntax does not: it cannot express a full outer join, it does not combine well with OR or IN in the same predicate, and Oracle documentation has recommended the ANSI syntax for new code since 9i. The formatter preserves whichever you wrote — putting the join conditions on their own lines is usually the fastest way to decide whether a rewrite is warranted.

How far it goes into PL/SQL

The engine is a SQL pretty-printer with PL/SQL awareness, not a full PL/SQL formatter. SQL statements inside a block are formatted properly, and block keywords such as BEGIN, END, IF, LOOP and COMMIT are placed on their own lines. Deeply nested procedural code — nested loops, exception handlers, cursor definitions inside a large package body — comes out laid out but not always indented the way a dedicated PL/SQL beautifier would do it.

It also does not validate. It has no data dictionary, does not connect to an instance, and cannot tell you whether a package compiles. It does check for unbalanced parentheses and odd quote counts, and reports the statement count so you can confirm the whole script arrived.