SQL*Plus can be an effective ad-hoc reporting tool. Generating a report using SQL*Plus can be as simple as typing in a SQL statement and executing it, although you usually will want to include some formatting commands as well.
ad-hoc: Done on an irregular or spontaneous basis. An ad-hoc report, for example, is one that is designed on the spot and run only once, or maybe a very few times.
Generating a simple report
The following mouseover shows one set of commands that you could use to generate a report listing all objects in the database:
Tells SQL*Plus to make the owner column 12 characters wide.
Tells SQL*Plus to make the object_type column 10 characters wide.
Tells SQL*Plus to make the object_name column 30 characters wide.
Identifies the three columns to print on the report.
The report data comes from the DBA_OBJECTS view, which returns a list of each object defined in the database.
Causes the report to be sorted by the specified columns.
OWNER OBJECT_TYP OBJECT_NAME
--------- ---------- ---------------
DBSNMP SYNONYM DBA_DATA_FILES
DBSNMP SYNONYM DBA_FREE_SPACE
DBSNMP SYNONYM DBA_SEGMENTS
DBSNMP SYNONYM DBA_TABLESPACES
OUTLN INDEX OL$HNT_NUM
OUTLN INDEX OL$NAME
OUTLN INDEX OL$SIGNATURE
OUTLN TABLE OL$
OUTLN TABLE OL$HINTS
PUBLIC SYNONYM ALL_ALL_TABLES
The COLUMN command is used in this example to limit the display width of the three columns so that their combined width is less than 80 characters. You will learn more about the COLUMN command in the next lesson.