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.