Oracle is the dialect where formatting can genuinely change behaviour, because of one feature: optimizer hints are written as comments. A formatter that normalises or drops comments will silently remove /*+ INDEX(...) */ and change the execution plan of a query that still looks correct. Preserving hints exactly is the first requirement for anything that touches Oracle SQL.
Beyond hints, Oracle carries decades of accumulated syntax — the (+) outer join notation that predates ANSI joins, CONNECT BY for hierarchies, DUAL, and the q-quote mechanism for strings containing apostrophes. The query below, loaded into the editor above, uses several of them at once.
Before and after
This is the query loaded in the editor above. On the left is what you paste; on the right is what the formatter returns with the default options for this dialect.
select /*+ index(e emp_dept_ix) */ e.employee_id, e.last_name, nvl(e.commission_pct, 0) as comm, level as depth, decode(d.location_id, 1700, 'HQ', 'Branch') as site from employees e, departments d where e.department_id = d.department_id(+) and e.hire_date >= add_months(sysdate, -12) start with e.manager_id is null connect by prior e.employee_id = e.manager_id order siblings by e.last_name fetch first 25 rows only;SELECT
/*+ index(e emp_dept_ix) */ e.employee_id,
e.last_name,
NVL(e.commission_pct, 0) AS comm,
LEVEL AS depth,
DECODE(d.location_id, 1700, 'HQ', 'Branch') AS site
FROM
employees e,
departments d
WHERE
e.department_id = d.department_id (+)
AND e.hire_date >= ADD_MONTHS(sysdate, -12)
START WITH e.manager_id IS NULL
CONNECT BY PRIOR e.employee_id = e.manager_id
ORDER SIBLINGS BY
e.last_name
FETCH FIRST
25 rows ONLY;What is specific to Oracle (PL/SQL)
Optimizer hints are comments that matter
A hint is written /*+ ... */ and must appear immediately after the SELECT, INSERT, UPDATE, DELETE or MERGE keyword. Put it anywhere else and Oracle ignores it without raising an error, which is why a formatter that moves comments around is dangerous here.
Hints are preserved in position and their contents are never re-cased or reflowed. In the output above, /*+ index(e emp_dept_ix) */ stays directly after SELECT and keeps the lower-case spelling it was written with.
The legacy (+) outer join
Before ANSI join syntax was supported, Oracle marked the optional side of an outer join with (+) on the join predicate. It is still very common in older code. d.department_id(+) means the row from departments may be missing — the equivalent of a LEFT JOIN from employees.
The notation is preserved. Note that a space is inserted before the marker, producing d.department_id (+), which Oracle parses identically.
Hierarchical queries: CONNECT BY, LEVEL, ORDER SIBLINGS BY
Oracle walks tree structures with START WITH to choose the roots and CONNECT BY PRIOR to describe the parent-child link, exposing the depth through the LEVEL pseudo-column. ORDER SIBLINGS BY then sorts within each level without breaking the hierarchy.
These are top-level clauses, so they are placed at the same indentation as WHERE and GROUP BY rather than being folded into the WHERE clause they follow.
q-quoted string literals
Doubling every apostrophe inside a string is error-prone, so Oracle offers alternative quoting: q'[...]' — or any other delimiter pair — makes the contents literal. The delimiters and the contents are preserved exactly:
DECLARE v VARCHAR2(50) := q'[it's fine]';DUAL, NVL and DECODE
DUAL is Oracle's one-row table, used whenever an expression needs a FROM clause. NVL is the two-argument null substitution and DECODE is the positional conditional that predates CASE. All three are treated as ordinary identifiers and functions.
For new code, COALESCE and CASE are the portable equivalents of NVL and DECODE, and behave slightly differently around type conversion and short-circuiting.
Known limitations
No formatter handles every corner of a dialect. These are the cases where this one produces output you may want to correct by hand.
- PL/SQL blocks are formatted much less well than queries. A DECLARE / BEGIN / END block is broken onto separate lines with blank lines between the sections rather than being indented as a nested structure. The SQL statements inside a block format normally; the block scaffolding around them does not.
- In FETCH FIRST 25 rows ONLY the word rows is left lower case, because it is parsed as part of the row-limiting clause rather than as a standalone keyword.
- The trailing / that SQL*Plus uses to execute a block is a client directive, not PL/SQL. Keep it out of what you paste.
Conventions worth adopting
Prefer ANSI joins in new code
The (+) notation cannot express a full outer join, does not combine with ANSI join syntax in the same query, and makes the join condition hard to separate from the filter condition. LEFT JOIN is clearer and is what Oracle has recommended for years. Formatting old (+) code is useful for reading it; converting it is a separate job.
ROWNUM versus FETCH FIRST
Row limiting with ROWNUM requires an inline view when combined with ORDER BY, because ROWNUM is assigned before sorting — the classic source of "top N" queries that return the wrong N rows. Oracle 12c introduced FETCH FIRST n ROWS ONLY, which sorts first and is what the example above uses.
Oracle (PL/SQL) formatting FAQ
Are optimizer hints preserved?
Yes. Hints are kept in position immediately after the leading keyword and their contents are not modified. This is the behaviour shown in the example above.
Can it format a package body or a large PL/SQL block?
The SQL statements inside will format, but the block scaffolding is handled poorly — this is listed under known limitations. For procedural code, an IDE with a PL/SQL-aware formatter will do better.
Should I keep the trailing slash?
No. The / is a SQL*Plus instruction to execute the preceding block, not part of PL/SQL. Paste the statement without it.
Is my SQL uploaded anywhere?
No. Formatting runs entirely in your browser, which matters here because Oracle queries often embed schema names and business logic.
Other SQL dialects
Not sure which one you need, or working with more than one? The general SQL formatter lets you switch dialects without leaving the page.