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:


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:


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:


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:


set echo on




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> 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:







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.


Introduction to SQL*Plus

Command-Line SQL*Plus

Browser-Based SQL*Plus

A Lightning SQL Tutorial

Generating Reports with SQL*Plus

Creating HTML Reports

Advanced Reports

Writing SQL*Plus Scripts

Extracting and Loading Data

Exploring Your Database

Advanced Scripting

Tuning and Timing

The Product User Profile

Customizing Your SQL*Plus Environment

Appendix A. SQL*Plus Command Reference

Appendix B. SQL*Plus Format Elements

show all menu

Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151
Similar book on Amazon © 2008-2017.
If you may any questions please contact us: