Reporting with SQL*Plus in Oracle AI Database 26ai

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 will usually want to include some formatting commands as well. The word "ad-hoc" is doing real work in that objective, too: this lesson is about the reports you build on the spot, to answer a question right now, not the polished, repeatable scripts covered later in this module. Both use the same underlying commands, but ad-hoc reporting is about speed and flexibility rather than permanence.

The following SQL shows one set of commands you could use to generate a report listing 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;

Walking through each piece:

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 every object defined in the database.
6. ORDER BY owner, object_type, object_name: sorts the report by the specified columns, so results group logically by owning schema.

None of this requires you to write, save, or compile anything ahead of time. That is the entire appeal of ad-hoc reporting: you type three COLUMN commands and a query, press Enter, and you have a formatted report in front of you. If the format needs adjusting, you just retype the COLUMN command with a different width and run the query again.

SQL*Plus reporting gets a real boost from a SQL language feature introduced back in Oracle Database 10g and still fully current today: the partitioned outer join^[2]. Partitioned outer join is an extension to ANSI outer join syntax that lets you selectively densify certain dimensions of a report while keeping others sparse. This matters more than it might sound at first: a lot of real reporting problems come down to gaps in your data, not errors in your data. If a store had zero sales on a Tuesday, an ordinary query simply has no row for that Tuesday at all, and a report built from that query silently skips the day rather than showing a zero. Partitioned outer join fixes exactly that kind of gap.

A brief, illustrative example, filling in every day of a week for a report even on days with no matching sales data:

SELECT d.day_of_week, s.total_sales
FROM (SELECT day_of_week FROM all_days_of_week) d
LEFT OUTER JOIN sales s
  PARTITION BY (s.store_id)
  ON d.day_of_week = s.day_of_week
ORDER BY d.day_of_week;

The PARTITION BY clause here is what turns an ordinary outer join into a partitioned one: rather than joining the two tables as a single unit, Oracle joins the outer table against each partition of the inner table separately, then unions the results together. The practical effect is that every day of the week appears in the output, with a sales total where one exists and a null (which you can format to display as zero) where it does not. Reporting tools that build cross-tabular output, the kind with days or months across the top and categories down the side, rely on this densification constantly. Without it, a sparse underlying dataset produces a report full of missing columns instead of a clean grid.

Partitioned outer join comes with a few current restrictions worth knowing before you reach for it: you can specify the PARTITION BY clause on the left or right side of the join, but not both, and you cannot specify a FULL partitioned outer join. Neither restriction tends to matter for typical reporting use, but they are worth remembering if you ever get an unexpected syntax error while experimenting with this construct.

A related performance detail, often mentioned in the same breath as partitioned outer join because both show up in analytic reporting queries: analytic functions can be parallelized under a specific, two-part condition. If the objects being queried have the parallel attribute, and the analytic function itself includes a PARTITION BY clause, then Oracle parallelizes the function's computations as well as the query that feeds them. This is not a blanket rule that every analytic function automatically runs in parallel; both conditions need to be true. On a large DBA_OBJECTS-style report against a genuinely large database, this can matter, since analytic functions computing running totals, rankings, or window aggregates across a big partitioned dataset benefit directly from that parallel execution rather than running as a single serial pass.

Taken together, partitioned outer join and parallelized analytic functions make a certain class of report both easier to write and faster to run: easier because you are not hand-coding workarounds for missing rows, and faster because Oracle can spread the actual computation across multiple processes when the conditions call for it.

The output from executing the original three-column report looks 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

This output is not a fabricated example. DBSNMP is a real, predefined Oracle administrative account, one you should never delete, and it is precisely the account Oracle Enterprise Manager Cloud Control uses to monitor and manage a database. Seeing DBSNMP-owned synonyms in a DBA_OBJECTS query against your own COIN database is exactly what you should expect, not a coincidence tied to some older Oracle version.

The COLUMN command is what keeps this output readable. Without it, the OWNER, OBJECT_TYPE, and OBJECT_NAME columns would print at whatever width the underlying data happens to need, which for OBJECT_NAME in particular can be quite wide, and the result wraps awkwardly or scrolls off the side of a terminal. By explicitly setting each column's width, this example keeps the combined display width under 80 characters, comfortably readable in a standard terminal window regardless of what platform you are running SQL*Plus on. You will learn a great deal more about the COLUMN command, including headings, number formats, and wrapping behavior, in the next lesson.

It is worth stepping back and asking why this skill belongs early in a module on SQL*Plus. The answer is that ad-hoc reporting is often the very first thing a DBA does when investigating a problem or answering a question from a colleague. Someone asks, "how many objects does the DBSNMP schema actually own," or "which schemas have the most indexes," and the fastest path to an answer is not writing a permanent script, it is typing a quick SELECT with a couple of COLUMN commands in front of it and reading the result directly off the screen. The three-column report shown in this lesson is intentionally simple precisely because that simplicity is the whole point: you should be able to build something like it from memory, adjust it on the fly, and get a readable answer in under a minute.

As this module continues, you will build on exactly this foundation: the same COLUMN command, the same underlying SELECT statements, and the same DBA_OBJECTS-style views, extended with headers, footers, saved scripts, and substitution variables so that a quick ad-hoc query can grow into a genuinely reusable reporting tool when the situation calls for it.

[1]ad-hoc: done on an irregular or spontaneous basis. An ad-hoc report, for example, is one 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 based on a specified column, 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.