RelationalDBDesign RelationalDBDesign


SQL* Plus CLI  «Prev 

Generating Oracle report - one example

Representative example

  1. COLUMN owner FORMAT A12: Tells SQL*Plus to make the owner column 12 characters wide.
  2. COLUMN object_type FORMAT A10: Tells SQL*Plus to make the object_type column 10 characters wide.
  3. COLUMN object_name FORMAT A30: Tells SQL*Plus to make the object_name column 30 characters wide.
  4. SELECT owner, object_type, object_name: Identifies the three columns to print on the report.
  5. FROM dba_objects: The report data comes from the DBA_OBJECTS view, which returns a list of each object defined in the database.
  6. 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. 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.