Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

8.4 Cleaning Up the Display

As you've followed the development of the index
listing script, you no doubt saw the following lines interspersed in
the output:

old 9: AND ui.table_name = UPPER('&table_name')
new 9: AND ui.table_name = UPPER('project_hours') If you happened to execute the script against a table with more than
a few indexed columns, you would have seen a message such as the
following:

...
6 rows selected.

These lines add no value to running the script and serve only to
clutter the output. It would be nice to get rid of them, and it is
possible to do that by turning verification and feedback off. The
commands to do that are described next.

8.4.1 Turning Off Verification

Verification refers to what SQL*Plus does when it encounters a line
of script containing
substitution
variables. By default, SQL*Plus enables you to verify the
substitution by displaying the old and the new versions of the line
involved. The output from verification looks like this:

old 9: AND ui.table_name = UPPER('&table_name')
new 9: AND ui.table_name = UPPER('project_hours') Sometimes it's useful to see these before and after
images, especially when you are first developing a script, because
they let you verify if your substitutions are being made correctly.
Once you've developed a script,
it's nice to be able to turn this output off, and
you can do that by adding the following command to your
script:
SET VERIFY OFF Turning verification off makes your output a lot cleaner and is
helpful if the script is a report that may be run by an end user.

8.4.2 Turning Off Feedback

Feedback refers to the short messages that SQL*Plus displays after
executing a SQL statement such as SELECT or COMMIT.
Feedback looks like this:

6 rows selected.

Or, in the case of a COMMIT, it looks like this:

Commit complete.

As with verification, feedback can clutter the output from a script.
The extra lines added by feedback are sometimes enough to scroll
output that you want to see off the top of the display, which can be
a bit annoying.

You can turn feedback off by adding the following line to your
scripts: SET FEEDBACK OFF You may want to turn feedback back on at the end of the script. Use
SET FEEDBACK ON for this purpose, so that you get the normal feedback
messages when executing interactive commands.

You'll find a modified version of Example 8-4 in the file ex8-4b.sql. This modified version has the additional commands SET VERIFY OFF and SET FEEDBACK OFF.

8.4.3 Turning Off Command Echoing

The echo setting controls
whether commands
from script
files are displayed to the screen as they are executed. Normally off
by default, command echoing can be a useful debugging tool. To turn
echo on, use the following command: SET ECHO ON Now, when you execute a script, such as
ex8-4b.sql, all the commands are echoed to the
display as they are executed. Here's how that would
look:

SQL> set echo on
SQL> @ex8-4b
SQL> PROMPT
SQL> PROMPT This script will first DESCRIBE a table. Then
This script will first DESCRIBE a table. Then
SQL> PROMPT it will list the definitions for all indexes
it will list the definitions for all indexes
SQL> PROMPT on that table.
on that table.
SQL> PROMPT
SQL>
SQL> SET HEADING OFF
SQL> SET RECSEP OFF
SQL> SET NEWPAGE 1
SQL> SET VERIFY OFF
SQL> SET FEEDBACK OFF
SQL>
SQL> ACCEPT table_name CHAR PROMPT 'Enter the table name >'
Enter the table name >
...

As you can see, echoing is something you usually want turned off. As
a safety measure, I often include SET ECHO OFF in my script files to
avoid accidentally being deluged by output. The one case where I
always turn on echoing is when I build a script file containing DDL
commands. If I run a script to create tables, indexes, or some other
object, I generally like to see what is happening when I run it.

When running a script to create or otherwise manage database objects such as tables and indexes, it's helpful to SET ECHO ON and spool the resulting output to a file. Then you can review the output later to check for problems.

8.4.4 Turning Off All Terminal Output

Turning off
the display output completely is helpful sometimes.
You've seen this done in Chapter 5; there, I modified the script to produce
the Project Hours and Dollars Report so it would spool the output to
a file. Usually, you want to turn off the display when you are
spooling a report to a file, or when you are extracting data to a
file. You may not want to look at all the data scrolling by on the
screen, and turning off the display can accelerate things. The
command to turn off terminal output is SET TERMOUT OFF. To turn
output back on again, use SET TERMOUT ON. When using these commands,
you usually want to bracket the SQL query that produces the report,
as shown here:

SET TERMOUT OFF
SELECT P.PROJECT_ID,
P.PROJECT_NAME
FROM PROJECT P;
SET TERMOUT ON When using the Windows GUI version of SQL*Plus, disabling the display
can make a dramatic difference in report performance. The difference
is less pronounced in command-line SQL*Plus.

Any attempt to SET TERMOUT OFF is ignored when you issue the command from the interactive SQL*Plus prompt. You can only disable terminal output from within a script.