Verification refers to what SQL*Plus does when it encounters a line of script containing substitution variables. By default, SQL*Plus verifies the substitution by displaying both 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 this verification is useful, especially when you are first developing a script, because it allows you to see for sure whether or not your substitutions are being made correctly. Once you’ve developed a script, though, it’s nice to be able to turn this output off.

You can turn verification off by adding the following command to your script:

SET VERIFY OFF

Turning verification off makes your output a lot cleaner, and is especially helpful if the script is a report that may be run by an end user.

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.


Commit complete.

As with verification, feedback often clutters up the output from a script. The extra lines added by feedback are sometimes just 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 it 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.

The echo setting controls whether or not 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 LIST_INDEXES, all the commands are echoed to the display as they are executed. Here’s how that would look:

SQL> set echo on
SQL> @C:jonathansql_plus_bookxe_ch_5list_indexes_H
SQL> SET HEADING OFF
SQL> SET RECSEP OFF
SQL> SET NEWPAGE 1
SQL> SET FEEDBACK OFF
SQL> SET VERIFY OFF
SQL> 
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> --Get the table name from the user
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 in order to avoid accidentally being deluged by output. The one case where I always turn echoing on is when I build a script file containing DDL commands. If I run a script to create tables, indexes, or some other object, I like to see exactly what is happening when I run it.

Sometimes it’s helpful to turn off the display output completely. You’ve already seen this done in Chapter 3 when the script to produce the Project Hours and Dollars Report was modified to 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 speed things up quite a bit. 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

The more output a report produces, the more turning off the display will make it run faster. That’s because updating and scrolling the display is one of the most time-consuming tasks SQL*Plus must perform.