Writing and Running SQL*Plus Scripts

By now, you have typed a fair number of COLUMN commands and SELECT statements in this module: report formatting, text and numeric columns, date handling. Retyping all of that every single time you want to run a report is tedious, and it is also a real source of errors: a mistyped format model or a forgotten COLUMN command produces a report that looks subtly wrong in a way that is easy to miss. SQL*Plus solves this the same way most command-line tools do: it can read commands from a text file, so you build the report once and simply run the file whenever you need it again.

To generate the DBA_OBJECTS report from Lesson 2, you could place the following commands into a text file named db_objects_by_type.sql, using any text editor at all, vi, Notepad, VS Code, whatever you already have:

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;

Once you have a file like this saved, you run it with one of three related commands, and the difference between them matters more than it might first appear. All three list and run the commands in a specified script the same way:

• @ looks for the named script starting in your current working directory, then falls back to your configured SQLPATH if it is not found there.
• @@ looks specifically in the same directory as whatever script is currently running it, regardless of which directory you originally launched from.
• START is the more explicit, spelled-out equivalent of @ for most everyday purposes.

To run the script above from the directory where you saved it:

SQL> @db_objects_by_type

or, equivalently:

SQL> START db_objects_by_type

The distinction between @ and @@ earns its keep the moment one script calls another. Suppose db_objects_by_type.sql itself calls a second script, say a shared header.sql that sets up common COLUMN formatting used across several reports, and both files live together in the same folder. If db_objects_by_type.sql uses @@header.sql to call it, that call resolves correctly no matter which directory you were sitting in when you originally ran the outer script. If it had used plain @header.sql instead, that call would look in your current directory first, which may or may not be where header.sql actually lives, depending on where you started from. This is a small distinction, but it is exactly the kind of thing that produces a confusing "script not found" error the first time you try running a set of nested scripts from somewhere other than their own folder.

By default, running a script quietly executes each line without displaying the commands themselves as they run, only their output. If you want to see exactly which command SQL*Plus is executing at each step, useful for troubleshooting a script that is not behaving as expected, turn on echoing first:

SQL> SET ECHO ON
SQL> @db_objects_by_type

SET ECHO affects @ and @@ identically, so this works the same way regardless of which of the two you use to run the script.

A script file is not a separate language from what you have already been typing interactively in this module. Every COLUMN command, every SELECT statement, every formatting technique from the last five lessons works exactly the same way inside a saved .sql file as it does typed directly at the SQL prompt. The only real difference is that a script captures your work permanently: once db_objects_by_type.sql exists, you never have to remember the exact FORMAT widths or column order again. You just run it.

This is worth internalizing early, because it changes how you should approach building any report you expect to run more than once. Rather than typing commands interactively and hoping you remember them correctly next time, build the report in a text editor from the start, save it as a named .sql file, and run it with @ or START. If something needs adjusting later, you edit the file once, and every future run of that script picks up the change automatically.

One brief note on SQL*Plus itself, since this module covers a lot of ground: the command-line interface you have been using throughout is the standard SQL*Plus interface, installed with every Oracle Database installation, and it is the same tool covered in Lesson 1, including the SQL*Plus Instant Client option for machines that need SQL*Plus without a full local Oracle Database installation. See the Oracle Call Interface Developer's Guide for further detail on Instant Client specifically, and see Lesson 1 for the fuller overview of SQL*Plus's overall capabilities, batch processing, HTML output, and database administration, rather than repeating that material here.