SQL* Plus CLI  «Prev  Next»

Lesson 16 Useful SQL*Plus settings
Objective Control the most important aspects of how SQL*Plus operates.

Useful SQL*Plus Settings

SQL*Plus has over 60 different settings to control the way it operates. You've already learned about SET LINESIZE and SET PAGESIZE. This lesson describes some other settings you should know about.


Feedback messages are the ones that SQL*Plus displays after queries that modify or select more than a certain number of rows. They look like this:
15 rows selected.
If you do not like those messages, you can use the SET FEEDBACK OFF command to disable them. By default, SQL*Plus displays feedback for any SQL statement affecting more than 6 rows. You can increase that threshold by supplying a number as an argument to the command. For example, the command SET FEEDBACK 50 changes the threshold to 50 rows.


Normally when you execute a script, SQL*Plus does not display the commands that it is reading from the file. For debugging purposes, you can use the SET ECHO ON command to tell SQL*Plus to display those commands for you to see.


When you use substitution variables, SQL*Plus displays before and after images of each line that contains such a variable. These messages look like this:

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

If you do not want to see them, you can turn them off using the SET VERIFY OFF command.


If you are spooling a report to a file, SQL*Plus pads each line with enough spaces to match the LINESIZE setting exactly. If you do not want trailing spaces in your output file, use the SET TRIMSPOOL ON command to have SQL*Plus remove them.


The RECSEP setting is used to print a separator line between records in a report. Record separator lines are typically blank lines, and by default they print after each record that contains one or more wrapped column values. You can turn that behavior off using the SET RECSEP OFF command. You can cause a record separator to be displayed after each line by issuing a SET RECSEP EACH command.


The SET DEFINE command controls the operation of the substitution variable feature. SET DEFINE "&" is the default setting. You can turn the feature completely off with the SET DEFINE OFF command.

SET NEWPAGE number_of_lines

SET NEWPAGE controls the way SQL*Plus marks a page break. Normally, page breaks are marked by printing one or more blank lines. The default is SET NEWPAGE 1, resulting in one blank line between pages. This isn’t much help when you’re sending a report to a printer because one blank line will not signal a printer to begin a new page. You can cause SQL*Plus to begin each page with a formfeed character by setting NEWPAGE to 0. There are many other SQL*Plus settings. If you have the time, you can scan the pages on the SET command in the SQL*Plus manual just to get familiar with them.

Various SqlPlus Options

Click on the link below to review your knowledge of the material presented in this lesson.

SQL*Plus Settings

Here are the correct matches:

SET FEED[BACK] {6 | n | ON | OFF}

Displays the number of records returned by a script when a script selects at least n records. ON or OFF turns this display on or off. Turning feedback ON sets n to 1. Setting feedback to zero is equivalent to turning it OFF.
SET FEEDBACK OFF also turns off the statement confirmation messages such as 'Table created' and 'PL/SQL procedure successfully completed' that are displayed after successful SQL or PL/SQL statements.