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

8.8 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 Maintenance 290000
1005 VPN Implementation 415000
1006 Security Audit 99.95
6 rows selected.
SQL> @ex8-7
Corporate Web Site
Enterprise Resource Planning System
Accounting System Implementation
Data Warehouse Maintenance
VPN Implementation
Security Audit
SQL> @ex8-8
1001 Corporate Web Site 1912000
1002 Enterprise Resource Planning System 9999999
1003 Accounting System Implementation 897000
1004 Data Warehouse Maintenance 290000
1005 VPN Implementation 415000
1006 Security Audit 99.95 One solution to this problem of SET commands from one script
interfering with another is to use the STORE SET command to save your
current settings at the beginning of a script so you can restore them
later. Example 8-10 is a revamped version of Example 8-7 that does just that.

Example 8-10. A script that resets all SET options to their original state

STORE SET original_settings REPLACE
SET PAGESIZE 0
SET FEEDBACK OFF
SPOOL ex8-10.lst
SELECT project_name
FROM project;
SPOOL OFF
@original_settings The STORE SET command in Example 8-10 generates a
file of SET commands reflecting all current settings. Those SET
commands are written to the file
original_settings.sql. If the file exists, it is
replaced. Settings can be freely changed, and the last thing the
script does before it ends is to restore the original settings by
executing original_settings.sql.

I don't much like the STORE SET approach.
It's a bit of a hack, and if concurrent users are
executing the same script in the same working directory, they will
overwrite each other's STORE SET files. On Windows,
your current working directory when running the Windows GUI version
of SQL*Plus will likely be $ORACLE_HOME/bin, and
who wants to clutter up that critical directory with such files?
I'd prefer some sort of stack mechanism, whereby you
could push and pop your settings (to and from memory, not disk) at
the beginning and ending of a script. Even better would be if that
pushing and popping could be done automatically, perhaps via a new
variation on the @ command. For now though, if you need to save and
restore settings from a script, STORE SET is your only choice.