In addition to plain SQL statements, the SQL Formatter understands
full PL/SQL syntax: anonymous blocks, stored procedures and
functions, packages, triggers, object types, and all of the usual
flow-control statements (IF, LOOP,
CASE, exception handlers). Any SELECT,
INSERT, UPDATE, DELETE, or
MERGE statement found inside PL/SQL code is formatted
using the exact same rules described in the
SELECT and
INSERT / UPDATE / DELETE / MERGE
pages — PL/SQL formatting simply wraps around it.
Select the code you want to format and choose Tools > Text/Format > Format SQL, use the Editor context menu command Format SQL, or press Ctrl+K, E (Shift+Ctrl+K, E to adjust settings first). You can format an entire script, a single procedure, or just a fragment of code — see Formatting a fragment below.
Every PL/SQL block — anonymous, nested, or the body of a
named procedure/function/package/trigger — follows the same
skeleton. DECLARE, BEGIN,
EXCEPTION, and END each start on their
own line at the block’s own indentation level; everything
inside a section is indented one level deeper.
Example:
declare
v_count number;
begin
select count(*) into v_count from employees;
dbms_output.put_line(v_count);
exception
when no_data_found then
v_count := 0;
end;
/
Consecutive simple variable and constant declarations are column-aligned as a group, so the types (and default values, if any) line up neatly:
declare v_id number; v_name varchar2(100) := 'x'; c_max_rows constant number := 100; v_emp_id emp.employee_id%type; v_row emp%rowtype; begin
A blank line, a comment, or a declaration that is not a simple
scalar (a cursor, a TYPE, or a nested subprogram)
breaks the alignment group — alignment starts fresh
afterwards.
CURSOR declarations, TYPE declarations,
and nested subprograms are each followed by a blank line to set
them apart from the rest of the section:
declare
cursor c_emp (p_dept_id number) is
select employee_id, last_name
from emp
where department_id = p_dept_id;
type t_emp_rec is record (
id number,
name varchar2(50)
);
v_rec t_emp_rec;
begin
The query inside a CURSOR … IS declaration is
formatted using the ordinary SELECT rules, indented
one level under the CURSOR keyword — exactly as
if that same query had been formatted on its own.
A procedure or function declared locally inside a
DECLARE section is formatted the same way as a
top-level one (see CREATE PROCEDURE / FUNCTION
below): IS/AS always starts its own line,
and the body follows the usual block layout. A blank line is added
after its closing END;.
declare
procedure deposit(account in out number, amount in number) is
begin
account := account + amount;
end;
begin
A forward declaration (just the signature, no
body) is emitted as a single normalized line ending in
;, also followed by a blank line.
Each branch keyword (IF, ELSIF,
ELSE) starts on its own line at the statement’s
indentation level; the statements inside each branch are indented
one level deeper. END IF; lines up with the original
IF.
if v_salary > 10000 then v_bonus := v_salary * 0.1; elsif v_salary > 5000 then v_bonus := v_salary * 0.05; else v_bonus := 0; end if;
All loop forms — plain LOOP,
WHILE … LOOP, numeric FOR … LOOP,
and cursor FOR … LOOP — are formatted the
same way: the loop header stays on one line, the body is indented
one level, and END LOOP; lines up with the header.
for i in 1..10 loop dbms_output.put_line(i); end loop; for i in reverse 1..10 loop dbms_output.put_line(i); end loop;
A cursor FOR loop with an inline subquery formats the
subquery using the ordinary SELECT rules, indented
one level under the opening (:
for r in ( select employee_id, last_name from emp where department_id = 10 ) loop dbms_output.put_line(r.last_name); end loop;
PL/SQL has two different uses of CASE, and the
formatter tells them apart automatically:
CASE … WHEN …
THEN <statements> … END CASE;
) appears where a
statement is expected, and each WHEN/THEN
branch is formatted like an IF branch.
RETURN, or another expression) is formatted the
same way it would be inside a SQL statement.
case v_grade
when 'A' then
dbms_output.put_line('Excellent');
when 'B' then
dbms_output.put_line('Good ');
else
dbms_output.put_line('Needs improvement');
end case;
Each WHEN <exception> THEN clause in an
EXCEPTION section is formatted like an
IF branch: the WHEN line stays on one
line, and the handler’s statements are indented one level
deeper.
exception
when no_data_found then
v_count := 0;
when others then
raise;
end;
The header of a CREATE [OR REPLACE] PROCEDURE,
FUNCTION, PACKAGE [BODY],
TRIGGER, or object TYPE [BODY] is kept on
one line when it is short enough; otherwise the parameter list is
wrapped one parameter per line (see
Parameter lists). IS/AS
stays on the same line as the header by default.
create or replace procedure deposit(account in out number, amount in number) is begin account := account + amount; end; /
A package spec/body and a package’s declaration list follow
the same rules as an ordinary DECLARE section (see
Declarations), and each subprogram body in
a package body is separated from its neighbours by a blank line.
Parameter lists appear in two places, and both are governed by the
same two settings — Param list max length and
Align param list to paren: in the header of a
procedure, function, or type member, and in the argument list of a
call. They are laid out differently once they wrap. A
header always puts one parameter per line; a call packs as many
arguments onto a line as will fit, and drops to one argument per
line only when the limit is too narrow for even the first argument
to fit after the opening (.
When a procedure/function header (or a call to one) is too long
to fit on one line, its parameter list is wrapped one parameter
per line, with the parameter names, modes (IN /
OUT / IN OUT), and types column-aligned:
create or replace procedure proc_example ( p_id in number, p_name in varchar2 default 'unknown', p_out out number, p_inout in out varchar2 ) is
When Align parameter list with opening parenthesis
is enabled, the first parameter instead shares the line with the
opening (, and every following parameter (as well as
the closing )) lines up under that same column:
create or replace procedure proc_example (p_id in number,
p_name in varchar2 default 'unknown',
p_out out number,
p_inout in out varchar2) is
The argument list of a call is packed rather than exploded: the
formatter keeps adding arguments to the current line while they fit
within Param list max length, then continues on a new
line. A call that already fits inside the limit is left on one line.
The five examples below all start from the same statement — only the two settings change:
BEGIN update_employee_record( p_employee_id=>v_id,p_department_name=>v_dept, p_employment_status=>'ACTIVE' ); END; /
1. Max length = 0, align to paren off. Wrapping is disabled, so the call stays on one line however wide it gets. Spacing and indentation are still normalised:
BEGIN update_employee_record(p_employee_id => v_id, p_department_name => v_dept, p_employment_status => 'ACTIVE'); END; /
2. Max length = 80, align to paren off. The call no
longer fits, so it is packed: what fits stays on the first line, the
rest continues one indent step in, and the closing )
gets a line of its own:
BEGIN
update_employee_record(p_employee_id => v_id, p_department_name => v_dept,
p_employment_status => 'ACTIVE'
);
END;
/
3. Max length = 80, align to paren on. The same
packing, but continuation lines align under the column after
( instead of one indent step, and the closing
) is appended to the last argument:
BEGIN
update_employee_record(p_employee_id => v_id, p_department_name => v_dept,
p_employment_status => 'ACTIVE');
END;
/
4. Max length = 40, align to paren off. The limit is
now too narrow for even the first argument to fit after
(, so the list opens with a break and every argument
gets its own line:
BEGIN
update_employee_record(
p_employee_id => v_id,
p_department_name => v_dept,
p_employment_status => 'ACTIVE'
);
END;
/
5. Max length = 40, align to paren on. One argument
per line as in example 4, but the alignment style keeps the first
argument on the ( line and lines the rest up beneath
it:
BEGIN
update_employee_record(p_employee_id => v_id,
p_department_name => v_dept,
p_employment_status => 'ACTIVE');
END;
/
The break after ( in example 4 applies only to a real
list — two or more arguments. A call with a single argument
has nothing to lay out one per line, so its argument stays on the
( line and is wrapped instead by the rules for
long expressions:
BEGIN
DBMS_OUTPUT.PUT_LINE('Employee ' || v_name || ' moved to department ' || v_dept || ' effective '
|| v_dt
);
END;
/
A long string built with || is packed as many pieces
per line as fit within the configured width, wrapping onto a new
line only when the next piece would not fit:
begin
v_msg := 'Employee ' || v_first_name || ' ' || v_last_name
|| ' earns ' || v_salary || ' per month';
end;
Long arithmetic (+ / -) and boolean
(AND / OR) expressions wrap the same
way — each continuation line lines up under the start of the
expression.
$IF … $THEN … $ELSIF … $ELSE … $END
conditional-compilation blocks are formatted the same way as an
ordinary IF statement.
An end-of-line -- comment that annotates a line of
PL/SQL code stays on that line, one space after the code:
DECLARE
v_amt NUMBER(10, 2); -- currency, 2dp
BEGIN
v_amt := 0; -- initialize
IF v_amt > 0 THEN -- positive only
NULL;
END IF; -- guard
END;
/
This is controlled by Keep end-of-line comments on their code line. When that option is off, every end-of-line comment is emitted on a line of its own below the code it annotates, which was the behaviour of earlier releases.
End-of-line comment max column limits the rule:
when keeping a comment on its code line would push that line past
the configured column, the comment is demoted onto a line of its
own instead. A value of 0 disables the width check.
Both options are described in the
formatting settings
reference.
Alignment. End-of-line comments are aligned to a common column inside SQL statements only — including SQL statements nested in a PL/SQL block — as described in the SQL Formatter overview:
BEGIN
SELECT
employee_id, -- pk
first_name || last_name AS full_name -- derived
INTO
v_id, v_nm
FROM hr.employees
WHERE rownum = 1;
END;
/
Comments attached to PL/SQL constructs — declarations,
assignments, END IF; and the like — are separated
from the code by a single space and are not aligned.
Multi-line /* … */ comments are re-indented to
the current level while preserving their internal layout.
You do not need to select a whole procedure or block to format it
— you can select just part of a script, for example a single
IF … END IF; statement in the middle of a
larger block, and format only that selection.
A few things to keep in mind when formatting a fragment:
WHEN cond THEN … copied out of
an EXCEPTION section), it is formatted at the
indentation level it already had — no extra indent is
invented for the missing enclosing block.
CURSOR … IS,
TYPE, SUBTYPE, and
PRAGMA are recognized only when the surrounding
DECLARE block is part of the selection. A
partial declaration selected on its own is left as-is rather
than reformatted incorrectly.
PROCEDURE/FUNCTION
declaration, on the other hand, is recognized and formatted
correctly even when selected on its own, since it carries
enough of its own structure (header, optional body) to be
understood without the enclosing block.