Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Resetting Your SQL*Plus Environment

If you are running scripts interactively, the SET commands that you execute in one script can adversely affect the operation of subsequent scripts. For example, you might SET PAGESIZE 0 to disable pagination, only to execute a report script later for which you want pagination.

Example 8-7 shows a simple, data-extraction script that sets PAGESIZE to zero in order to avoid writing column headings to the output file. Example 8-8 shows an even simpler report generation script. Example 8-9 shows the results of running these scripts interactively, from one session of SQL*Plus. The first time the report is run, the page title prints. But not the second time. Why not? It's because the effects of the SET PAGESIZE 0 command executed by ex8-7.sql linger on for the duration of the session. The effects of SET FEEDBACK OFF linger, too.

Example 8-7. A script that disables pagination as a side effect

SET PAGESIZE 0
SET FEEDBACK OFF

SPOOL ex8-7.lst
SELECT project_name
FROM project;
SPOOL OFF

Example 8-8. A report generation script that requires pagination

TTITLE LEFT "Corporate Project Listing" SKIP 2

SELECT * FROM project;

Example 8-9. A demonstration of SET command side effects

SQL> @ex8-8 Corporate Project Listing PROJECT_ID PROJECT_NAME PROJECT_BUDGET ---------- ---------------------------------------- -------------- 1001 Corporate Web Site 1912000 1002 Enterprise Resource Planning System 9999999 1003 Accounting System Implementation 897000 1004 Data Warehouse ...