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