Oracle Database Administration: The Essential Refe

Producing Reports

SQL*Plus also contains a powerful set of commands for formatting output and producing reports. While not as powerful or flexible as a full commercial report writing system, reports generated by SQL*Plus are attractive and easy to program, and may satisfy a variety of reporting needs.

Consider the following SQL statement, which lists all employees in the scott.emp table and prints a projected new salary based on a 10% increase:

SELECT empno,ename,sal,sal*1.10,comm
FROM scott.emp
/

When this statement is executed, the following output is produced:

    EMPNO ENAME           SAL  SAL*1.10      COMM
--------- --------- --------- --------- ---------
     7369 SMITH           800       880
     7499 ALLEN          1600      1760       300
     7521 WARD           1250      1375       500
     7566 JONES          2975    3272.5
     7654 MARTIN         1250      1375      1400
     7698 BLAKE          2850      3135
     7782 CLARK          2450      2695
     7788 SCOTT          3000      3300
     7839 KING           5000      5500
     7844 TURNER         1500      1650         0
     7876 ADAMS          1100      1210
     7900 JAMES           950      1045
     7902 FORD           3000      3300
     7934 MILLER         1300      1430

14 rows selected

While this output is readable and contains the requested information, it is probably not a report you would want to present to senior management. Try adding a few SQL*Plus formatting commands:

set space 2 set feedback off set linesize 54 set pagesize 30 COLUMN empno heading "Employee|Number" format 9999 COLUMN ename heading "Employee|Name" format a10 COLUMN sal heading "Current|Salary" format $9999.99 COLUMN newsal heading "New|Salary" format $9999.99 COLUMN comm heading "Commission" format $9999.99 TTITLE LEFT 'The Totally Bogus ...