Oracle SQL - TTITLE and BTITLE

Introduction

You may add top and bottom titles to a report that is in a script.

The TTITLE (top title) and BTITLE (bottom title) commands have this syntax:

TTITLE option text OFF/ON 

where option refers to the placement of the title:

COLUMN n (start in some column, n) 
SKIP m (skip m blank lines) 
TAB x (tab x positions) 
LEFT/CENTER/RIGHT (default is LEFT) 

The same holds for BTITLE.

The titles, line sizes, and page sizes for bottom titles need to be coordinated to make the report look attractive.

In addition, page numbers may be added with the extension:

option text format 999 sql.pno 

Here is an example:

Demo

SQL>
SQL>-- from w w  w  .j  av  a2  s  .  c  om
SQL> drop table emp;

Table dropped.

SQL> create table emp(
  2  empno        Number(3)  NOT NULL,
  3  ename        VARCHAR2(10 BYTE),
  4  hireDate     DATE,
  5  orig_salary  Number(8,2),
  6  curr_salary  Number(8,2),
  7  region       VARCHAR2(1 BYTE)
  8  );
SQL>
SQL> insert into emp values(1,'Alison',DATE '1996-03-21', 45000, NULL,  'E');
SQL> insert into emp values(2,'James',DATE  '1978-12-12', 23000, 32000, 'W');
SQL> insert into emp values(3,'Celia',DATE  '1982-10-24', NULL,  58000, 'E');
SQL> insert into emp values(4,'Robert',DATE '1984-01-15', 31000, NULL,  'W');
SQL> insert into emp values(5,'Linda',DATE  '1987-07-30', NULL,  53000, 'E');
SQL> insert into emp values(6,'David',DATE  '1990-12-31', 78000, NULL,  'W');
SQL> insert into emp values(7,'Jode',DATE  '1996-09-17', 21000, 29000, 'E');
SQL>
SQL>
SQL> SET echo off
SQL> REM February 13, 2018
SQL> REM modified Feb. 14, 2018
SQL> REM Script for employee's current salary report
SQL> COLUMN curr_salary FORMAT $9,999,999
SQL> COLUMN ename FORMAT a10
SQL> TTITLE LEFT 'Current Salary Report ##########################'
SQL> SKIP 1
SP2-0042: unknown command "SKIP 1" - rest of line ignored.
SQL> BTITLE LEFT 'End of report **********************'   ' Page #'
SQL> format 99 sql.pno
SP2-0734: unknown command beginning "format 99 ..." - rest of line ignored.
SQL> SET linesize 50
SQL> SET pagesize 25
SQL> COLUMN region FORMAT a7
SQL> BREAK ON region skip1 ON report
SQL> REM 2 breaks - one on region, one on report
SQL> COMPUTE sum max min of curr_salary ON region
SQL> COMPUTE sum of curr_salary ON report
SQL> REM a compute for each BREAK
SQL> SET feedback off
SQL> SET verify off
SQL> SELECT empno, ename, curr_salary, region
  2  FROM emp
  3  ORDER BY region
  4  /

Current Salary Report ##########################
     EMPNO ENAME      CURR_SALARY REGION
---------- ---------- ----------- -------
         1 Alison                 E
         5 Linda          $53,000
         3 Celia          $58,000
         7 Jode           $29,000
                      ----------- *******
                          $29,000 minimum
                          $58,000 maximum
                         $140,000 sum

         6 David                  W
         2 James          $32,000
         4 Robert
                      ----------- *******
                          $32,000 minimum
                          $32,000 maximum
                          $32,000 sum

                      -----------
sum                      $172,000

End of report ********************** Page #
SQL> REM clean up parameters set before the SELECT
SQL> CLEAR BREAKS
breaks cleared
SQL> CLEAR COMPUTES
computes cleared
SQL> CLEAR COLUMNS
columns cleared
SQL> BTITLE OFF
SQL> TTITLE OFF
SQL> SET verify on
SQL> SET feedback on
SQL> SET echo on
SQL>