SQL* Plus CLI  «Prev  Next»

Lesson 2Reporting with SQL*Plus
Objective Generate ad-hoc reports using SQL*Plus.

Reporting with SQL*Plus

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:
  1. Tells SQL*Plus to make the owner column 12 characters wide.
  2. Tells SQL*Plus to make the object_type column 10 characters wide.
  3. Tells SQL*Plus to make the object_name column 30 characters wide.
  4. Identifies the three columns to print on the report.
  5. The report data comes from the DBA_OBJECTS view, which returns a list of each object defined in the database.
  6. Causes the report to be sorted by the specified columns.
Generating Oracle Report
The output from executing the commands shown here will look like this:

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.