Creating a Report with SQLPlus

For those of you who have used SQL*Plus, you will know that it is a nightmare to get the output looking just the way you want it to. You have to battle with page sizes and column widths. (Why isn't there an option to set the column size automatically, I wonder?)

Here are a few things that I have learnt:

Silent Mode
Use the -s flag on your sqlplus command in order to inhibit output such as the SQL*Plus banner and prompt.

Spooling to a file
You need to spool in order to write the output of your sql commands to a file. Turn it off when you are done.

SQL> spool results.out
SQL> select 1 from dual;
SQL> spool off

Page Size
This refers to the number of rows on a single page. The default is 14 which means that after 14 lines, your table header will be repeated, which is ugly! In order to get around this, set your page size to the maximum of 50000. It would be nice if you could set it to unlimited.

SQL> show pagesize;
pagesize 24
SQL> set pagesize 50000

Line Size
This refers to how long your line can get before it wraps to the next line. If you are not sure how long your line can be, set the size to the maximum of 32767 and turn on trimspool in order to remove trailing blanks from your spooled file.

SQL> show linesize;
pagesize 80
SQL> set linesize 32767
SQL> set trimspool on

Column Size
You can specify the size of individual columns like this:

SQL> col employee_name format a40

Titles
Use TTITLE to display a heading before you run a query.

SQL> TTITLE LEFT 'My table heading'
SQL> select 1 from dual;

My table heading
         1
----------
         1

Use SKIP to skip lines e.g. SKIP 2 would be equivalent to pressing Return twice.

SQL> ttitle left 'My table heading' -
> SKIP 2 'Another heading' SKIP 2
SQL> select 1 from dual;

My table heading

Another heading

         1
----------
         1

Example script
The shell script below uses SQL*Plus to create a report.

#! /usr/bin/sh

#the file where sql output will go
OUT=/report/path/report.txt

#email this report?
EMAIL=Y

#oracle variables
ORACLE_HOME=/path/oracle/client
export ORACLE_HOME
SQLPLUS=$ORACLE_HOME/bin/sqlplus
export SQLPLUS
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH
TNS_ADMIN=/path/tnsnames
export TNS_ADMIN

#######################
#sqlplus - silent mode
#redirect /dev/null so that output is not shown on terminal
$SQLPLUS -s "user/pass@database" << END_SQL > /dev/null

SET ECHO OFF
SET TERMOUT OFF

SET PAGESIZE 50000

SET LINESIZE 32767
SET TRIMSPOOL ON

COL EMPLOYEE_NAME FORMAT A40

SPOOL $OUT

TTITLE LEFT 'EMPLOYEE REPORT' -
SKIP 2 LEFT 'Number of Employees:' SKIP 2

SELECT COUNT(*) AS total FROM employee
/

TTITLE LEFT 'Employee Names' SKIP 2

SELECT employee_name FROM employee
ORDER BY employee_name DESC
/

SPOOL OFF

END_SQL
#######################

#change tabs to spaces
expand $OUT > $OUT.new
mv $OUT.new $OUT

echo Finished writing report $OUT

if [ "$EMAIL" = "Y" ]
then
 to=someone@abc.com
 subject="Employee Report"
 mailx -s "$subject" $to < $OUT
 echo "Emailed report"
fi