Making SQL*Plus output readable

By default, SQL*Plus output is hard to read — columns wrap, numbers aren't formatted, and there's too much whitespace. A few SET and COLUMN commands transform it into clean, professional output.

Recipe #1 - Essential SET commands

Put these at the top of every SQL*Plus script:

SET LINESIZE 200
SET PAGESIZE 50
SET TRIMSPOOL ON
SET TRIMOUT ON
SET FEEDBACK OFF
SET HEADING ON
SET COLSEP ' | '

• LINESIZE — How wide the output is (default 80 is way too narrow)
• PAGESIZE — Rows before header is reprinted
• TRIMSPOOL/TRIMOUT — Remove trailing spaces
• COLSEP — Separator between columns

Recipe #2 - COLUMN formatting

Format specific columns for readability:

COLUMN ename        FORMAT A20    HEADING 'Employee'
COLUMN sal          FORMAT 999,999.99 HEADING 'Salary'
COLUMN hiredate     FORMAT A12    HEADING 'Hire Date'
COLUMN deptno       FORMAT 9999   HEADING 'Dept'

SELECT ename, sal, TO_CHAR(hiredate, 'DD-MON-YYYY') AS hiredate, deptno
FROM emp
ORDER BY deptno, ename;

• A20 — Character column, 20 characters wide
• 999,999.99 — Number with thousands separator and 2 decimals
• HEADING — Custom column header

Recipe #3 - SPOOL output to a file

Save query results to a file:

SPOOL /tmp/employee_report.txt
SELECT ename, sal, deptno FROM emp ORDER BY deptno;
SPOOL OFF

For CSV output:

SET COLSEP ','
SET HEADING ON
SET FEEDBACK OFF
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIMSPOOL ON

SPOOL /tmp/employees.csv
SELECT ename || ',' || sal || ',' || deptno FROM emp;
SPOOL OFF

Recipe #4 - BREAK and COMPUTE for report-style output

Create subtotals and grouping:

BREAK ON deptno SKIP 1

COMPUTE SUM LABEL 'Total' OF sal ON deptno

SELECT deptno, ename, sal
FROM emp
ORDER BY deptno, ename;

CLEAR BREAKS
CLEAR COMPUTES

Recipe #5 - Timing your queries

SET TIMING ON

SELECT COUNT(*) FROM large_table;

SET TIMING OFF

This shows elapsed time after each statement — invaluable for performance testing.