There is much which can be done to format output in SQL*Plus both environmentally (globally) and within individual queries. This page presents a brief summary of those capabilities. In general there are some very sophisticated tools available for reporting and SQL*Plus is more often than not used for administration and testing as opposed to commercial reporting.

Environmental Settings

The SHOW and SET Commands

The SHOW command shows the value of a SQL*Plus environment variable and SET allows changing of those variables. The SHOW ALL command displays all the current or default environmental settings. Let's hightlight and examine a few of these settings.

show all

appinfo is OFF and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
btitle OFF and is the first few characters of the next SELECT statement
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK is ON
define "&" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT ON
echo OFF
editfile "afiedt.buf"
embedded OFF
escape OFF
FEEDBACK ON for 6 or more rows
flagger OFF
flush ON
heading ON
headsep "|" (hex 7c)
instance "local"
linesize 132
lno 14
loboffset 1
logsource ""
long 80
longchunksize 80
markup HTML OFF HEAD "<style type='text/css'> body {font:10pt Arial,Helvetica,sa
newpage 1
null ""
numformat ""
numwidth 10
pagesize 14
PAUSE is OFF
pno 0
recsep WRAP
recsepchar " " (hex 20)
release 902000100
repfooter OFF and is NULL
repheader OFF and is NULL
serveroutput OFF
shiftinout INVISIBLE
showmode OFF
spool OFF
sqlblanklines OFF
sqlcase MIXED
sqlcode 0
sqlcontinue "> "
sqlnumber ON
sqlpluscompatibility 8.1.7
sqlprefix "#" (hex 23)
sqlprompt "SQL> "
sqlterminator ";" (hex 3b)
suffix "sql"
tab ON
termout ON
time OFF
timing OFF
trimout ON
trimspool OFF
ttitle OFF and is the first few characters of the next SELECT statement
underline "-" (hex 2d)
USER is "SYS"
verify ON
wrap : lines will be truncated

Individual Settings

Individual settings can be viewed using SHOW <variable>; as shown following.

SQL> show linesize;
linesize 132

Some Interesting Settings in Detail

Setting	Description
AUTOCOMMIT [ ON \| OFF ]	Each DML forces a commit. This option is best left set to off.
AUTOTRACE [ ON \| OFF \| TRACEONLY ] [ EXPLAIN STATISTICS ]	Displays tracing and query plan details for DML statements.
COLSEP ""	The default column separator is a space character.
editfile ""	Allows editing of a script file for the current session. The default file name is afiedt.buf.
ECHO [ ON \| OFF ]	Echoes commands to the screen.
ESCAPE [ ON \| OFF \| char ]	Sets the escape character (default \).
FEEDBACK [ ON \| OFF ]	Displays rows returned.
HEADING [ ON \| OFF ]	Displays column headings.
LINESIZE	Line width.
NUMFORMAT $999,999.90	Set the default numerical format.
NUMWIDTH n	Default width for numbers is 10 characters.
NULL 'string'	Replace NULL values with a string.
PAGESIZE n	Set to 0 to suppress headers, breaks and titles.
SERVEROUTPUT [ ON \| OFF ]	Used with DBMS_OUTPUT to display output in PL/SQL code.
SPOOL [ ON \| OFF ]	Switch on (open) or off (close) the output log file.
SQLPROMPT 'string'	Set as '' to remove prompt.
TERMOUT [ ON \| OFF ]	Suppresses screen output.
TIMING [ ON \| OFF ]	Switches timing of each command on and off.
TRIMOUT [ ON \| OFF ]	Removes trailing white space characters from the end of a line of output.
VERIFY [ ON \| OFF ]	Variable substitution display.
WRAP [ ON \| OFF ]	Line word wrapping.

Some Simple Scripting

set wrap off linesize 132 feedback off heading off pagesize 0 sqlprompt '';
spool c:\tmp\script.log;
select subject_id||','||name from subject;
spool off;
set wrap on linesize 80 feedback on heading on pagesize 40 sqlprompt 'SQL> ';
exit;

If I run this command from a shell.

sqlplus -SILENT books/books@test @c:\tmp\script.sql

This is what the output file looks like.

1,Non-Fiction
2,Self Help
3,Esoteric
4,Metaphysics
5,Computers
6,Fiction
7,Science Fiction
8,Fantasy
9,Drama
10,Whodunnit
11,Suspense
12,Literature
13,Poetry
14,Victorian
15,Shakespearian
16,Modern American
17,19th Century American
100,A new subject
101,Another new subject
21,Subject name

Here is a slighty more complex query, executing a query within the script, the query resulting from the query generated by the script.

set termout off echo off feed off trimspool on head off pages 0;
spool C:\tmp\counts.log;
column table_name format a16;
select 'SELECT '''||table_name||','', TRIM(TO_CHAR(count(*),''999,999,990'')) from '||table_name||';' from   user_tables;
spool off;
set termout on;
@@C:\tmp\counts.log;
set termout on echo on feed on trimspool off head on pages 40;
exit;

This is the generated script.

SELECT 'AUTHOR,', TRIM(TO_CHAR(count(*),'999,999,990')) from AUTHOR;
SELECT 'CRITIC,', TRIM(TO_CHAR(count(*),'999,999,990')) from CRITIC;
SELECT 'EDITION,', TRIM(TO_CHAR(count(*),'999,999,990')) from EDITION;
SELECT 'GHOSTWRITER,', TRIM(TO_CHAR(count(*),'999,999,990')) from GHOSTWRITER;
SELECT 'PROFESSIONALREVIEWS,', TRIM(TO_CHAR(count(*),'999,999,990')) from PROFESSIONALREVIEWS;
SELECT 'PUBLICATION,', TRIM(TO_CHAR(count(*),'999,999,990')) from PUBLICATION;
SELECT 'PUBLISHER,', TRIM(TO_CHAR(count(*),'999,999,990')) from PUBLISHER;
SELECT 'REVIEW,', TRIM(TO_CHAR(count(*),'999,999,990')) from REVIEW;
SELECT 'REVIEWER,', TRIM(TO_CHAR(count(*),'999,999,990')) from REVIEWER;
SELECT 'SUBJECT,', TRIM(TO_CHAR(count(*),'999,999,990')) from SUBJECT;

This is the final result shown. There are only 17 rows in one table at present, the other tables are empty.

AUTHOR, 6
COAUTHOR, 2
CRITIC, 2
EDITION, 17
PUBLICATION, 11
PUBLISHER, 11
REVIEW, 10
REVIEWER, 0
SUBJECT, 19

Formatting Query Results

Individual queries can be formatted specifically. The most useful area which should be covered is the COLUMN command. In addition to all the options in the SET command the COLUMN command can be utilized to format individual columns within the rows of a query result.

COLUMN Command Syntax

COLUMN column [ HEADING 'heading' ]
	[ JUSTIFY { LEFT | CENTER | RIGHT } ]
	[ { FORMAT A99 } | { FORMAT $999,999.90 } ]
	…;

Let's take a quick look at the Publication table.

set linesize 40;
desc publication;

 Name              Null?    Type
 ----------------- -------- ------------
 PUBLICATION_ID    NOT NULL NUMBER
 SUBJECT_ID        NOT NULL NUMBER
 AUTHOR_ID         NOT NULL NUMBER
 TITLE                      VARCHAR2(64)

set linesize 132;

Let's get all the data from the Publication table.

SELECT * FROM publication;

PUBLICATION_ID SUBJECT_ID  AUTHOR_ID TITLE
-------------- ---------- ---------- -----------------------------------
             1          7          2 Cities in Flight
             2          7          2 A Case of Conscience
             3          7          3 Foundation
             4          7          3 Second Foundation
             5          7          3 Foundation and Empire
             6          7          3 Foundation's Edge
             7          7          3 Prelude to Foundation
             8         15          6 The Complete Works of Shakespeare
             9          7          4 Lucifer's Hammer
            10          7          4 Footfall
            11          7          4 Ringworld

11 rows selected.

Let's do some simple formatting.

COL publication_id HEADING 'Title' FORMAT 99999;
COL subject_id HEADING 'Subject' FORMAT 9999999;
COL author_id HEADING 'Author' FORMAT 999999;
COL title HEADING 'Title' FORMAT a36;
SELECT * FROM publication;

 Title  Subject  Author Title
------ -------- ------- ------------------------------------
     1        7       2 Cities in Flight
     2        7       2 A Case of Conscience
     3        7       3 Foundation
     4        7       3 Second Foundation
     5        7       3 Foundation and Empire
     6        7       3 Foundation's Edge
     7        7       3 Prelude to Foundation
     8       15       6 The Complete Works of Shakespeare
     9        7       4 Lucifer's Hammer
    10        7       4 Footfall
    11        7       4 Ringworld

11 rows selected.