SQL*Plus can be an effective ad-hoc[1] 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:
COLUMN owner FORMAT A12
COLUMN object_type FORMAT A10
COLUMN object_name FORMAT A30
SELECT owner, object_type, object_name
FROM dba_objects
ORDER BY owner, object_type, object_name;
COLUMN owner FORMAT A12: Tells SQL*Plus to make the owner column 12 characters wide.
COLUMN object_type FORMAT A10: Tells SQL*Plus to make the object_type column 10 characters wide.
COLUMN object_name FORMAT A30: Tells SQL*Plus to make the object_name column 30 characters wide.
SELECT owner, object_type, object_name: Identifies the three columns to print on the report.
FROM dba_objects: The report data comes from the DBA_OBJECTS view, which returns a list of each object defined in the database.
ORDER BY owner, object_type, object_name: Causes the report to be sorted by the specified columns.
SQL Reporting Capability
In Oracle Database 10g, the SQL reporting capability was further enhanced by the introduction of partitioned outer join[2]. 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.
[1]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.
[2]partitioned outer join:In Oracle SQL, a partitioned outer join is a special type of join that divides data into partitions (groups) based on a specified column, and then performs an outer join within each partition. This is particularly useful for filling gaps in sparse data, such as ensuring a report shows sales for every day of the week, even if no sales occurred on certain days.