This tip comes from , a Senior IA from Vernon Hills, IL. We have improved the SQL statement in some points, so WHERE clauses will be correctly executed.

In SQL*Plus, it is often difficult to read the results of a SELECT *FROM 'table' statement since the output could wrap across multiple lines. For example, the normal output of SELECT * FROM 'dba_users' would be :

USERNAME USER_ID PASSWORD
------------------------------ ---------- ---------
ACCOUNT_STATUS LOCK_DATE EXPIRY_DAT
-------------------------------- ---------- ----------
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED
------------------------------ --------------------------- -------
PROFILE
------------------------------
EXTERNAL_NAME
------------------------------------------------------------------
SYS 0 5638228DAF52805F
OPEN
SYSTEM TEMP 28-AUG-00

The results are not clearly readable.

Implementing this SQL script, the output would be :

ACCOUNT_STATUS = ----------------OPEN [4]
CREATED = -----------------------08/28/2000 14:56:08 [9]
DEFAULT_TABLESPACE = ------------SYSTEM [6]
EXPIRY_DATE = -------------------[0]
EXTERNAL_NAME = -----------------[0]
LOCK_DATE = ---------------------[0]
PASSWORD = ----------------------5638228DAF52805F [16]
PROFILE = -----------------------DEFAULT [7]
TEMPORARY_TABLESPACE = ----------TEMP [4]
USERNAME = ----------------------SYS [3]
USER_ID = -----------------------0 [1]

This formats the results to 1 row per screen with the length of data in square braces [ ]. It also allows optional WHERE clause and ORDER BY .

Click for the improved script.