| 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.
SET FEEDBACK {ON|OFF|rows}
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.
SET ECHO {ON|OFF}
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.
SET VERIFY {ON|OFF}
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.
SET TRIMSPOOL {ON|OFF}
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.
SET RECSEP {WRAPPED|EACH|OFF}
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.
SET DEFINE {ON|OFF|"char"}
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:
You are printing a report for your boss, and the last page contains a line that tells how many rows were selected.
You do not want this line on the report. |
SET FEEDBACK OFF |
| You are executing a script that happens to have a number of ampers and (&) characters in it, and
SQL*Plus keeps prompting you for values. None of the ampersands are meant to indicate substitution variables. | SET DEFINE OFF |
| You spooled a report to a file and printed it. Each page seems to have two or three sets of page titles with about 20 rows of data between each. You need the titles to print just once per page. | SET PAGESIZE 66 |
| You've written a script for a group of end-users, but every time they run the script, they see the before and after images of lines containing substitution variables. You don't want them to see that because they keep calling you for an explanation. | SET VERIFY OFF |
| You need to debug a script and want to see each command as it is being executed. | SET ECHO ON |
| You are printing a report with some long text fields. Whenever a text field wraps to a second line, you get a blank line immediately following it. You don't want this blank line. | SET RECSEP OFF |
| You copied a spool file containing a report to a printer, and the printer did not advance to a new sheet of paper for each new page in the report. | SET VERIFY 0 |
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.