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 Company' -
       RIGHT 'Page: ' FORMAT 99 SQL.PNO SKIP 2
BTITLE CENTER 'Company Confidential' 
SELECT empno,ename,sal,sal*1.10 newsal,comm
FROM scott.emp
/

Now the following report will be produced:

The Totally Bogus Company                       Page:  1
           
Employee  Employee      Current        New
  Number  Name           Salary     Salary  Commission
--------  ----------  ---------  ---------  ----------
    7369  SMITH         $800.00    $880.00
    7499  ALLEN        $1600.00   $1760.00     $300.00
    7521  WARD         $1250.00   $1375.00     $500.00
    7566  JONES        $2975.00   $3272.50
    7654  MARTIN       $1250.00   $1375.00    $1400.00
    7698  BLAKE        $2850.00   $3135.00
    7782  CLARK        $2450.00   $2695.00
    7788  SCOTT        $3000.00   $3300.00
    7839  KING         $5000.00   $5500.00
    7844  TURNER       $1500.00   $1650.00        $.00
    7876  ADAMS        $1100.00   $1210.00
    7900  JAMES         $950.00   $1045.00
    7902  FORD         $3000.00   $3300.00
    7934  MILLER       $1300.00   $1430.00

                 Company Confidential