SQL*Plus Reporting and Scripting: Conclusion

SQL*Plus is more than a prompt where you enter a SELECT statement. It is a stable command-line environment for querying Oracle Database, formatting results, running repeatable processes, collecting output, and building administrative tools from ordinary text files. This module began with a simple ad-hoc report and ended with SQL that generates other SQL statements. Between those two points, you learned how SQL*Plus commands cooperate with SQL and PL/SQL to turn raw database results into useful reports and controlled scripts.

These skills remain valuable in Oracle AI Database 26ai because SQL*Plus is widely available, predictable, and suitable for both interactive and batch work. A graphical interface may be convenient for discovery, but a saved SQL*Plus script records the exact commands, settings, query, and output path used for a task. That record makes the work easier to repeat, review, troubleshoot, and move between Oracle environments. SQLcl adds modern conveniences, but the SQL, PL/SQL, and much of the SQL*Plus command knowledge developed here transfers directly to it.

The reporting workflow starts with the SQL statement. The query must select the correct rows, expressions, and sort order before display formatting can improve it. The early lessons used data dictionary views such as DBA_OBJECTS to demonstrate this separation. SQL determines what the database returns; SQL*Plus determines how those returned values appear on the screen, in a spool file, or in a printed report. Keeping that distinction clear prevents a common mistake: trying to repair incorrect data selection with display commands.

The COLUMN command is the center of SQL*Plus report formatting. It can replace technical identifiers with readable headings, control display widths, align headings, supply text for null values, and choose whether long character values wrap or truncate. Because column attributes remain active for the SQL*Plus session, a report script should define the formats it needs and clear or replace settings that might have been inherited from an earlier command, login.sql, or glogin.sql. Commands such as COLUMN without arguments, COLUMN column_name, COLUMN column_name CLEAR, and CLEAR COLUMNS help you inspect or reset that session state.

Text, numeric, date, timestamp, and Boolean values require different formatting decisions. Text columns use formats such as A20, together with WRAPPED, WORD_WRAPPED, or TRUNCATED. Numeric formats can add currency symbols, group separators, decimal places, leading zeros, signs, or other presentation characters, but the format must reserve enough room for the largest expected value. If it does not, SQL*Plus displays number signs instead of silently changing the number. Date presentation is normally controlled with TO_CHAR in the query when a report requires a precise, portable mask. The same approach applies to the timestamp family when fractional seconds or time-zone information matters. Oracle AI Database 26ai also extends COLUMN formatting for native SQL BOOLEAN values, allowing labels such as YES and NO when those terms communicate the result more clearly than TRUE and FALSE.

Once individual columns are readable, SQL*Plus can organize the report as a whole. The BREAK command suppresses repeated values in sorted output and can insert blank lines or begin a new report page when a grouping value changes. The query's ORDER BY clause and the columns named by BREAK must describe the same logical grouping. Otherwise, matching values may appear in several disconnected places and the visual result can imply a grouping the query did not actually produce. CLEAR BREAKS removes earlier break definitions before another report is run.

TTITLE and BTITLE add top and bottom titles. They can position literal text and values such as the current page number, split a title across lines, and align components to the left, center, or right. Their placement depends on session settings, especially LINESIZE and PAGESIZE. A title that appears centered in one session may shift in another if its line width is different. A footer can also appear too early or too often when the page size does not match the intended output medium. For that reason, a production report should set its own page geometry instead of relying on whatever values happen to be active when the script begins.

SPOOL converts transient terminal output into a file that can be reviewed, archived, printed, or processed by another program. A typical script opens the spool file before the query and closes it with SPOOL OFF afterward. The CREATE, REPLACE, and APPEND options determine how SQL*Plus handles an existing file. SET TRIMSPOOL ON removes trailing spaces, while SET TERMOUT OFF can suppress screen output when a script is run from a file. When physical pagination matters, SET NEWPAGE 0 emits a formfeed character rather than merely inserting a blank line. SQL*Plus can also generate HTML output, although structured data formats available in modern tools such as SQLcl may be more appropriate when another application, rather than a person, will consume the result.

Saving commands in a .sql file changes a successful interactive experiment into a repeatable procedure. The @ command runs a script, START provides the longer equivalent, and @@ is especially useful when one script calls another script relative to its own location. That relative behavior lets you organize a larger process into a main script and smaller supporting files without hardcoding a different absolute path for every workstation or server. SQL*Plus can also use a configured search path and a different default script suffix, but explicit, conventional .sql names are often the clearest choice for shared administrative code.

A script should establish the environment on which its output depends. Settings in glogin.sql and login.sql can provide useful site and user defaults, but a report intended to produce consistent output should still set its critical values explicitly. This includes page and line size, feedback, headings, wrapping, substitution behavior, verification messages, and spooling rules. SET ECHO ON is useful while debugging because it shows commands as SQL*Plus reads them from the script; it is usually turned off for a clean final report. In the same way, SET FEEDBACK OFF removes row counts and statement confirmations, while SET VERIFY OFF suppresses the old and new forms of lines containing substituted text. These controls do not change the query's result. They remove SQL*Plus dialogue that would otherwise become part of the report.

Substitution variables make one script reusable for different owners, dates, table names, or reporting thresholds. A single ampersand requests a value when SQL*Plus needs it, while a double ampersand defines a value that can be reused during the session. DEFINE assigns a value directly, and UNDEFINE removes it. The period used after a variable name is a delimiter, not part of the value, which becomes important when variable text must be joined immediately to a filename extension or another string.

PROMPT explains what the script is doing or what the user should enter. ACCEPT provides more control over input by supporting a prompt, a datatype, a format, a default, and optional input hiding. Together, these commands provide a simple command-line interface for a report. Simpler prompts are generally easier to maintain, and the script should validate important assumptions through SQL or PL/SQL rather than treating a prompt as proof that the supplied value is safe and correct.

A substitution variable is textual replacement performed before Oracle parses the statement. It is not the same as a bind variable, and it should not be mistaken for one. Bind variables represent values supplied to a parsed statement and are normally preferable in application code for performance and security. Substitution remains useful when the text itself must vary, such as an object name or spool filename, but generated or privileged commands should be constrained, displayed, and reviewed before execution. COLUMN ... NEW_VALUE can capture a query result into a substitution variable, which is particularly useful for timestamps, environment labels, and unique output filenames.

SQL*Plus keeps the most recently entered SQL statement or PL/SQL block in the SQL buffer. The line-editing commands covered in this module let you repair that buffer without typing the entire statement again. LIST displays one or more lines and makes a listed line current. CHANGE replaces or removes text on the current line, DEL deletes a line, INPUT adds one or more new lines, and APPEND adds text to the end of the current line. A forward slash on a line by itself executes the statement currently stored in the buffer. For larger changes, EDIT opens the buffer or a named script in the configured operating-system editor.

These commands are not a substitute for source control or a full editor, but they remain useful for quick corrections on a console, jump server, or remote environment. The larger lesson is that SQL*Plus maintains state. The SQL buffer, column formats, break definitions, variables, titles, and SET values all persist according to their own rules. Productive SQL*Plus work therefore requires awareness of both database state and client state. When output is surprising, inspect the query and the active SQL*Plus environment before assuming that the database returned the wrong data.

The following compact pattern combines the central reporting techniques from the module. It prompts for an owner, formats and groups object information, adds a title, captures clean output, and resets the report-specific definitions when finished:

SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET TRIMSPOOL ON
SET LINESIZE 100
SET PAGESIZE 50

ACCEPT owner_name CHAR PROMPT 'Schema owner: '

COLUMN owner       HEADING 'Owner'       FORMAT A20
COLUMN object_type HEADING 'Object Type' FORMAT A20
COLUMN object_name HEADING 'Object Name' FORMAT A35

BREAK ON owner SKIP 1
TTITLE CENTER 'Database Objects by Owner' SKIP 2

SPOOL schema_objects.txt REPLACE
SELECT owner, object_type, object_name
FROM   all_objects
WHERE  owner = UPPER('&owner_name')
ORDER BY owner, object_type, object_name;
SPOOL OFF

TTITLE OFF
CLEAR BREAKS
CLEAR COLUMNS
UNDEFINE owner_name

No individual command in this example is complicated. Its value comes from composition. The query supplies correct and predictably ordered data; COLUMN makes each datatype readable; BREAK reflects the sort order; TTITLE supplies report context; SPOOL preserves the result; and the closing commands prevent the report's state from leaking into later work. A real administrative version could add error handling, a dynamic filename, authorization checks, and a standard header showing the database, container, timestamp, and connected user.

The final lesson extended these reporting techniques into automation. By selecting literal command text together with data dictionary values and spooling the result, SQL can generate a script containing hundreds or thousands of consistent statements. The example that produced an ALTER TABLE ... DISABLE CONSTRAINT command for each referential constraint demonstrates the essential pattern. PAGESIZE 0, wide lines, disabled headings and feedback, and trimmed spool output ensure that the resulting file contains executable SQL rather than report decoration.

Hand-built concatenation is appropriate for short, predictable statements. When the goal is to reproduce full object definitions, use DBMS_METADATA.GET_DDL so Oracle, rather than custom string logic, reconstructs the DDL. SQL*Plus must set LONG and LONGCHUNKSIZE high enough to prevent the returned text from being truncated. Alternative quoting syntax makes embedded quotation marks easier to manage, and NEW_VALUE can generate timestamped filenames so each run remains traceable. The resulting script can be reviewed and then run with @, or SQL*Plus can be started in silent mode as one controlled step in a larger operating-system job.

Generated SQL magnifies both accuracy and error. A precise predicate can automate work across every intended object; an imprecise predicate can automate the wrong work just as efficiently. Before running generated DDL or privilege changes, spool it to a separate file, inspect the target owners and objects, test the logic in an appropriate nonproduction environment, and use an account with only the privileges required for the task. Generation and execution are separate phases for a reason. Keeping them separate creates a review point and a durable record of what was intended.

Most commands in this module have remained useful for many Oracle releases, which is one of SQL*Plus's strengths. Oracle AI Database 26ai adds capabilities without discarding that established scripting model. SQL*Plus can work with VECTOR bind variables, SET ERRORDETAILS can display an Oracle Database Error Help link and additional diagnostic detail, and SHOW CONNECTION NETSERVICENAMES can list or resolve Oracle Net service names. Enhanced DESCRIBE output can include annotation metadata associated with schema objects. These features connect a familiar client to vector search, richer metadata, and improved troubleshooting while preserving the commands used throughout this module.

New capability does not eliminate the need for disciplined scripts. A report still needs deterministic settings, an appropriate data dictionary view, a meaningful sort order, sufficient privileges, and a deliberate output destination. A generated script still needs review. A parameter still needs to be understood as either substituted text or a bound value. Oracle versions evolve, but those operational principles remain stable.

The techniques in this module are most effective when they are used as a repeatable workflow. Begin by validating the query interactively, including its privileges, filters, joins, and sort order. Add column formats only after the returned data are correct. Next, save the commands in a script, define every setting that affects the output, and replace hardcoded run-specific values with carefully bounded input. Spool the result to a deliberate location, review it, and reset any client state that should not persist. If the output is another SQL script, treat review as a required stage before execution.

A maintained script should also explain its purpose, expected account, required privileges, parameters, output files, and any changes it can make. Include identifying information in administrative reports when it aids traceability, such as the database or pluggable database, connected user, and generation time. Store important scripts with the same care given to other source code so that changes can be compared and recovered. These habits turn SQL*Plus from a collection of remembered commands into a dependable operational tool whose results can be understood by the next person who runs it, including you months after the original task was completed.

After completing this module, you should be able to:

1. Create an ad-hoc SQL*Plus report from a correctly ordered SQL query.
2. Format text, numbers, dates, timestamps, nulls, headings, and Boolean output for the intended audience.
3. Use breaks, page geometry, top titles, and bottom titles to organize a multi-page report.
4. Spool clean output to a file and choose settings appropriate for viewing, printing, archiving, or later processing.
5. Write modular scripts and run them with @, @@, or START.
6. Accept user input through substitution variables, PROMPT, and ACCEPT, while recognizing the distinction between substitution variables and bind variables.
7. Use SQL*Plus line editing and session settings to work efficiently and diagnose unexpected output.
8. Query the data dictionary to generate reviewable SQL scripts for repetitive administrative work.

The most important result is not memorizing every option. It is learning how the pieces fit together. SQL supplies the data and database operations; SQL*Plus supplies the client environment, presentation rules, input mechanism, file handling, and orchestration. When those responsibilities are combined carefully, a command-line session becomes a portable reporting and automation system. That is why SQL*Plus remains relevant in Oracle AI Database 26ai, and why the techniques in this module continue to belong in an Oracle administrator's working toolkit.