Oracle SQL and PL/SQL Formatter

Oracle literals and variables

Oracle code mixes server SQL with SQL*Plus conventions, and the PL/SQL dialect reads both:

• Alternative quoting: q'[Customer's note]', q'{…}', q'<…>' and q'#…#' hold apostrophes without doubling them. nq'…' works too. In any other dialect the first inner apostrophe ends the string, so the rest of the query falls apart.
• Ordinary strings double their quotes ('O''Brien'), and N'…' marks national-character text.
• Double quotes are case-sensitive identifiers such as "OrderTotal". Backticks are rejected.
• Bind variables like :cust_id and :new_id stay attached to their colon.
• Substitution variables from SQL*Plus and SQL Developer scripts, &region and &&status, are kept as single tokens.
• Hints: /*+ index(o orders_cust_ix) */ and line hints starting with --+ are recognised as hints rather than ordinary comments.

Hierarchical queries, MERGE and other Oracle syntax

START WITH and CONNECT BY PRIOR each get a line of their own, and ORDER SIBLINGS BY is treated as an ordering clause, so an org-chart or bill-of-materials query reads top to bottom. LEVEL, NOCYCLE and SYS_CONNECT_BY_PATH keep their place. MERGE INTO … USING (subquery) ON (…) indents the source query and puts each WHEN MATCHED THEN UPDATE SET / WHEN NOT MATCHED THEN INSERT branch on new lines.

The legacy outer-join marker is kept, with a space added before it: o.customer_id (+). FETCH FIRST 10 ROWS ONLY, RETURNING order_id INTO :new_id, ROWNUM filters and functions like DECODE and NVL all format as you would expect. Two oddities to know: EXTRACT(YEAR FROM order_date) is spread over several lines, and a column called name is upper-cased because NAME is a keyword.

PL/SQL blocks, procedures and the slash

This is a SQL formatter that tolerates PL/SQL rather than a full PL/SQL pretty-printer, and it helps to know where the line falls. In an anonymous block, every SQL statement between BEGIN and END is laid out properly, but the statements are not indented under BEGIN, DECLARE v_total NUMBER; stays on one line, and EXCEPTION WHEN no_data_found THEN … is joined into a single line. The / that ends a block in SQL*Plus is kept on its own line.

For CREATE OR REPLACE PROCEDURE, PACKAGE BODY and TRIGGER, the header is split after CREATE, because OR is read as a logical operator. CREATE OR REPLACE VIEW is not affected. For a procedure or package, joining the first two lines back together is usually the only edit needed. Trigger headers come out more broken up, with BEFORE, INSERT and ON orders on separate lines.

Choosing options for Oracle code

Many Oracle shops write upper-case keywords and lower-case names, which the defaults produce. Tabular indent styles misplace START WITH and CONNECT BY, splitting them as START WITH, so prefer Standard for hierarchical queries. When you need a one-line query for a log or a JDBC string, Minify (Ctrl/Cmd+Shift+M) removes ordinary comments but keeps /*+ */ and --+ hints, which the optimizer still needs. Everything stays in the browser, so schema names from a production instance are not sent anywhere.

Examples

select employee_id, last_name, manager_id, level as depth, sys_connect_by_path(last_name, '/') as chain from employees start with manager_id is null connect by prior employee_id = manager_id order siblings by last_name;

SELECT
  employee_id,
  last_name,
  manager_id,
  LEVEL AS depth,
  sys_connect_by_path(last_name, '/') AS chain
FROM
  employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY
  last_name;

merge into inventory t using (select sku, sum(qty) as qty from stock_moves where moved_at >= trunc(sysdate) group by sku) s on (t.sku = s.sku) when matched then update set t.qty = t.qty + s.qty, t.updated_at = systimestamp when not matched then insert (sku, qty, updated_at) values (s.sku, s.qty, systimestamp);

MERGE INTO
  inventory t USING (
    SELECT
      sku,
      sum(qty) AS qty
    FROM
      stock_moves
    WHERE
      moved_at >= trunc(sysdate)
    GROUP BY
      sku
  ) s ON (t.sku = s.sku)
WHEN MATCHED THEN
UPDATE SET
  t.qty = t.qty + s.qty,
  t.updated_at = systimestamp
WHEN NOT MATCHED THEN
INSERT
  (sku, qty, updated_at)
VALUES
  (s.sku, s.qty, systimestamp);

select o.order_id, o.order_total, q'[Customer's "priority" note]' as label from orders o where o.customer_id = :cust_id and o.region = '&region' and o.order_date >= date '2026-01-01' order by o.order_total desc fetch first 10 rows only;

SELECT
  o.order_id,
  o.order_total,
  q'[Customer's "priority" note]' AS label
FROM
  orders o
WHERE
  o.customer_id = :cust_id
  AND o.region = '&region'
  AND o.order_date >= DATE '2026-01-01'
ORDER BY
  o.order_total DESC
FETCH FIRST
  10 rows ONLY;

declare
  v_total number;
begin
  select sum(order_total) into v_total from orders where customer_id = 101;
  dbms_output.put_line('Total: ' || v_total);
exception
  when no_data_found then
    v_total := 0;
end;
/

DECLARE v_total NUMBER;

BEGIN
SELECT
  sum(order_total) INTO v_total
FROM
  orders
WHERE
  customer_id = 101;

dbms_output.put_line ('Total: ' || v_total);

EXCEPTION WHEN no_data_found THEN v_total := 0;

END;

/

Common errors and how to fix them

Related tools

• SQL formatter
• sql formatter/transactsql
• sql formatter/db2
• sql validator
• sql minifier
• JSON to SQL
• sql style guide
• sql unterminated quoted string