Useful SQL*Plus Settings in Oracle AI Database 26ai

SQL*Plus has well over sixty different settings controlling how it operates. You already know LINESIZE and PAGESIZE from earlier in this module. This lesson covers several more settings worth knowing well, then closes with a look at what is genuinely new for SQL*Plus in Oracle AI Database 26ai specifically, capability that did not exist in earlier releases at all.

Feedback messages are what SQL*Plus displays after a statement that selects or modifies more than a certain number of rows, messages like:

15 rows selected.

If you would rather not see those, use SET FEEDBACK OFF. By default, SQL*Plus displays feedback for any statement affecting more than 6 rows; supply a number instead to change that threshold, as in SET FEEDBACK 50. Turning feedback ON sets the threshold back to 1, and setting it to 0 is equivalent to turning it off entirely. Worth knowing: SET FEEDBACK OFF also suppresses the statement confirmation messages, things like Table created and PL/SQL procedure successfully completed, that normally print after a successful SQL or PL/SQL statement. Two lesser-known options round out the full syntax, {6 | n | ON | OFF | ONLY} [SQL_ID]: ONLY and a SQL_ID parameter both exist for more specialized reporting scenarios beyond the everyday row-count case covered here.

Normally, running a script does not display the commands SQL*Plus is reading from the file, only their output. For debugging, SET ECHO ON tells SQL*Plus to display each command as it runs, exactly as covered when this module first introduced scripting.

When you use substitution variables, SQL*Plus shows before-and-after images of each line containing one, messages like:

old   6: WHERE owner = '&user_name.'
new   6: WHERE owner = 'SYSTEM'

If you would rather not see those, turn them off with SET VERIFY OFF, exactly as covered in the substitution variables lesson earlier in this module.

When spooling a report to a file, SQL*Plus pads each line with spaces to match LINESIZE exactly by default. If you do not want those trailing spaces cluttering your output file, SET TRIMSPOOL ON removes them.

RECSEP controls whether a separator line prints between records in a report. By default, a separator prints after any record containing one or more wrapped column values, using SET RECSEP WRAPPED. Turn that behavior off entirely with SET RECSEP OFF, or force a separator after every single record regardless of wrapping with SET RECSEP EACH.

A related, separate setting handles what the separator actually looks like rather than when it appears: SET RECSEPCHAR controls the specific character used to build the separator line itself. RECSEP decides whether and when a separator prints; RECSEPCHAR decides what it is made of. The two are easy to conflate since they sound similar, but they answer different questions.

SET DEFINE controls the substitution variable feature itself, the mechanism covered in depth two lessons ago. The default is SET DEFINE "&", meaning the ampersand marks a substitution variable. You can turn the entire feature off with SET DEFINE OFF, useful if a script legitimately contains ampersand characters that were never meant to trigger a substitution prompt.

SET NEWPAGE controls how SQL*Plus marks a page break. By default, SET NEWPAGE 1 prints one blank line between pages, fine on screen but useless for a printer, since a single blank line gives a printer nothing to recognize as a page-advance signal. Setting NEWPAGE to 0 instead makes SQL*Plus begin each page with an actual formfeed character, which most printers do recognize and act on, exactly as covered back in the spooling and printing lesson.

The table below pairs common reporting problems with the setting that actually solves each one:

That last scenario is exactly the printer page-advance problem covered above: it needs a genuine formfeed character, which is precisely what SET NEWPAGE 0 provides. SET VERIFY has nothing to do with page breaks or printers at all; it only controls the substitution-variable before-and-after display.

Everything covered so far has been stable SQL*Plus behavior for a long time. A few real capabilities in 26ai are worth knowing specifically because they are new, not just because they are useful, since they reflect what "AI Database" actually means for the client you have been using throughout this module.

VECTOR as a bind variable type. SQL*Plus now supports VECTOR directly as a datatype for bind variables, tied to Oracle's vector search capabilities:

VARIABLE var1 VECTOR
VARIABLE var1 VECTOR='[2,3]'

DESCRIBE on a variable declared this way reports its datatype as VECTOR, the same way it would report NUMBER or VARCHAR2 for more familiar types. This matters because it means vector data, the kind used in AI-driven similarity search, is now a first-class citizen in ordinary SQL*Plus scripting rather than something you had to work around.

SET ERRORDETAILS, a genuinely new command. Unlike everything else in this lesson, SET ERRORDETAILS did not exist before 26ai. It controls whether SQL*Plus displays the Oracle Database Error Help URL alongside a failed statement:

SET ERRORDETAILS ON
SET ERRORDETAILS VERBOSE

ON, the default, shows the error help URL. VERBOSE shows the URL plus further detail. This is distinct from OERR, an older, long-standing command that has displayed cause-and-action text for Oracle errors for a very long time; OERR is not new to 26ai, SET ERRORDETAILS is what's actually new here, giving you an even faster path to Oracle's own documentation for a specific error.

SHOW CONNECTION, also new in this release. This command lists the Oracle Net service names available from your tnsnames.ora file, and can resolve a specific alias:

SHOW CONNECTION NETSERVICENAMES
SHOW CONNECTION NETSERVICENAMES cdb1_pdb1

This connects directly to material covered earlier in this course on tnsnames.ora and connect identifiers, now surfaced from inside SQL*Plus itself rather than requiring you to inspect the file directly.

DESCRIBE now shows annotation metadata. If a table or column has annotation information associated with it, an enhanced 26ai feature for attaching name/value metadata to schema objects, DESCRIBE displays that annotation information directly, controllable through a SET DESCRIBE option. Previously, DESCRIBE showed only structural information, column names and datatypes; now it can surface additional descriptive metadata the same way.

A few more commands worth knowing exist, even without full coverage here: CONFIG, which generates a config-store JSON file from your tnsnames.ora; ARGUMENT, which sets prompts and defaults for script parameters that were omitted at runtime; and PING, a SQL*Plus-level connectivity check distinct from the operating-system ping command covered in Module 2. On the database side, worth noting for anyone doing application development work: DB_DEVELOPER_ROLE is a new 26ai role built around least-privilege principles, giving an application developer most of the system and object privileges they need without broader DBA-level access. And if you ever work with point-in-time recovery, note that RECOVER...SNAPSHOT TIME has been desupported in 26ai; Oracle recommends ALTER DATABASE BEGIN/END BACKUP together with RECOVER...UNTIL TIME/SCN instead.

There are many more SQL*Plus settings beyond what this lesson covers. If you have the time, scanning through the SET command's full entry in the SQL*Plus User's Guide and Reference is worth doing at least once, just to get a sense of the full range available to you.