Backing up SQL Plus environment during script

Question

Some scripts I have inherited will blindly call SET FEEDBACK OFF or SET ECHO OFF at the beginning of the script, then set them to ON or OFF at the end of the script. I would like to modify these scripts to determine what value was set before the script was run, and set the environment back to that value when the script completes.

How do I query SQL Plus environment values, store them, and restore them when a script has finished?

One method I have thought of:

SPOOL env-backup.sql
SHOW ECHO FEEDBACK TIMING

REM ...

@env-backup.sql

But

• The values SHOW ECHO FEEDBACK TIMING spits out can't be executed directly (ECHO OFF vs SET ECHO OFF)
• I would rather not create yet another file (or any modifications to the DB)

Not that it is necessarily related, but I'm using SqlPlus from Oracle XE (10g) on Windows

Answer 1

SQL*Plus has the STORE command just for this. It outputs a file which has all the environment settings. Executing the file would restore these settings. Type `HELP STORE' from the SQL*Plus prompt for more info.

Answer 2

Why not just put the desired values for your connection in the glogin.sql script provided?

It's normally found in %ORACLE_HOME%\sqlplus\admin

From my glogin.sql:

--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--
set pagesize 60
set linesize 500
set wrap off

Then just reconnnect after running any scripts that alter your environment (or run @glogin.sql itself).

Answer 3

You might be interested in those scripts

Answer 4

If you want each SQL to be run independently you could call them using the HOST command. That is, for misbehaving scripts call HOST SQLPLUS username/password@tnsname @script.sql and it will run in a new process.