Oracle PL/SQL Tutorial/SQL PLUS Session Environment/Ttitle BTitle
Remarks in Scripts with Ttitle and Btitle
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.
In addition, page numbers may be added with the extension:
option text format 999 sql.pno
<source lang="sql">
SQL> SQL> SQL> set pagesize 22 SQL> set linesize 80 SQL> SQL> ttitle left "SQL*Plus report" - > right "Page: " format 99 SQL.PNO - > skip center "OVERVIEW" - > skip center "employees department 30" - > skip 2 SQL> SQL> btitle col 20 "Confidential" tab 8 - > "Created by: " SQL.USER SQL> SQL> SQL> create table employees(
2 empno NUMBER(4) 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , msal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) ) ;
Table created. SQL> SQL> SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10); 1 row created. SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10); 1 row created. SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20); 1 row created. SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20); 1 row created. SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30); 1 row created. SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30); 1 row created. SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40); 1 row created. SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40); 1 row created. SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20); 1 row created. SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30); 1 row created. SQL> SQL> SQL> SQL> select empno, ename, bdate
2 , msal as salary 3 , comm as commission 4 from employees 5 where deptno = 30;
SQL*Plus report Page: 1
OVERVIEW employees department 30 month EMPNO last_name BDATE salary COMMISSION
-------------------- ---------- --------- ----------
5 Joe 23-08-1969 $1900.00 1400 6 Black 24-07-1970 $2000.00 null 10 Pink 28-03-1974 $2400.00 0
Confidential Created by: sqle
3 rows selected. SQL> SQL> btitle off SQL> btitle btitle OFF and is the following 66 characters: col 20 "Confidential" tab 8 "Created by: " SQL.USER SQL> ttitle off SQL> SQL> drop table employees; Table dropped. SQL></source>
TTitle ON and OFF
<source lang="sql">
SQL> SQL> SET PAGESIZE 66 SQL> COLUMN name FORMAT A30 SQL> COLUMN type FORMAT A15 SQL> COLUMN source_size FORMAT 999999 SQL> COLUMN parsed_size FORMAT 999999 SQL> COLUMN code_size FORMAT 999999 SQL> TTITLE "Size of PL/SQL Objects > &&1 KBytes" Enter value for 1: SQL> SELECT name, type, source_size, parsed_size, code_size
2 FROM user_object_size 3 WHERE code_size > &&1 * 1000 4 ORDER BY code_size DESC 5 /
old 3: WHERE code_size > &&1 * 1000 new 3: WHERE code_size > * 1000
WHERE code_size > * 1000 *
ERROR at line 3: ORA-00936: missing expression
SQL> SQL> TTITLE OFF SQL> SQL> SQL></source>
Use TTITLE and BTITLE
<source lang="sql">
SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description) 2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060
725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description) 2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860
221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description) 2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900
315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description) 2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990
421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description) 2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980
808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description) 2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960
104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description) 2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980
212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description) 2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020
415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> SQL> SET echo off SQL> COLUMN salary FORMAT $9,999,999 SQL> COLUMN first_name FORMAT a10 SQL> TTITLE LEFT "Salary Report ##########################" SQL> SKIP 1 SQL> BTITLE LEFT "End of report **********************" " Page #" SQL> format 99 sql.pno SQL> SET linesize 50 SQL> SET pagesize 25 SQL> COLUMN city FORMAT a7 SQL> BREAK ON city skip1 ON report SQL> REM 2 breaks - one on city, one on report SQL> COMPUTE sum max min of salary ON city SQL> COMPUTE sum of salary ON report SQL> REM a compute for each BREAK SQL> SET feedback off SQL> SET verify off SQL> SELECT * FROM employee
2 ORDER BY city 3 /
Salary Report ########################## ID FIRST_NAME LAST_NAME START_DAT END_DATE
---------- ---------- --------- ---------
SALARY CITY DESCRIPTION
------- ---------------
07 David Larry 31-DEC-90 12-FEB-98
$7,898 New Yor Manager k
06 Linda Green 30-JUL-87 04-JAN-96
$4,323 Tester
*******
$4,323 minimum
$7,898 maximum
$12,221 sum
End of report ********************** Page # Salary Report ########################## ID FIRST_NAME LAST_NAME START_DAT END_DATE
---------- ---------- --------- ---------
SALARY CITY DESCRIPTION
------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06
$1,235 Toronto Programmer
*******
$1,235 minimum
$1,235 maximum
$1,235 sum
End of report ********************** Page # Salary Report ########################## ID FIRST_NAME LAST_NAME START_DAT END_DATE
---------- ---------- --------- ---------
SALARY CITY DESCRIPTION
------- ---------------
05 Robert Black 15-JAN-84 08-AUG-98
$2,335 Vancouv Tester er
08 James Cat 17-SEP-96 15-APR-02
$1,233 Tester
03 James Smith 12-DEC-78 15-MAR-90
$6,545 Tester
02 Alison Mathews 21-MAR-76 21-FEB-86
$6,662 Tester
04 Celia Rice 24-OCT-82 21-APR-99
$2,345 Manager
*******
End of report ********************** Page # Salary Report ########################## ID FIRST_NAME LAST_NAME START_DAT END_DATE
---------- ---------- --------- ---------
SALARY CITY DESCRIPTION
------- ---------------
$1,233 minimum
$6,662 maximum
$19,119 sum
sum
$32,574
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> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL></source>