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.
Generating Simple Report of Objects in Database
The following SQL shows one set of commands that you could use to generate a report listing all objects in the database:
SQL Reporting Capability
In Oracle Database 10g, the SQL reporting capability was further enhanced by the introduction of partitioned outer join. Partitioned outer join is an extension to ANSI outer join syntax that allows users to selectively densify certain dimensions while keeping others sparse. This allows reporting tools to selectively densify dimensions, for example, the ones that appear in their cross-tabular reports while keeping others sparse.
To enhance performance, analytic functions can be parallelized: multiple processes can simultaneously execute all of these statements. These capabilities make calculations easier and more efficient, thereby enhancing database performance, scalability, and simplicity.
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.
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.