Revert scan state when prompting for variable values
How do you detect the current SCAN
state and revert it after changing it?
An exerpt from the script in question:
SET SCAN OFF
SET ECHO ON
SET SQLBLANKLINES ON
SET SCAN ON
UPDATE TABLE_NAME SET CREATED_BY = &&created_by;
SET SCAN OFF
The problem is that if the script doesn't have the first line (SET SCAN OFF
), then the code to prompt the user should not turn the SCAN
state off afterwards. The pseudocode solution:
-- The next line might not exist, or be "SET SCAN ON".
SET SCAN OFF
SET ECHO ON
SET SQLBLANKLINES ON
-- Remember if the scanning wa开发者_如何学编程s on or off.
PUSH SCAN STATE
SET SCAN ON
UPDATE TABLE_NAME SET CREATED_BY = &&created_by;
-- Revert scanning to its former state.
POP SCAN STATE
The line PUSH SCAN STATE
remembers the state so that if the code is altered by removing the first line, the rest of the script still works as expected, due to the corresonding POP SCAN STATE
.
the sqlplus STORE
command is supposed to do this, unfortunately it doesn't seem to save the SCAN
state (SQLPlus 9.2.0.1.0). I will demonstrate with another setting (TIMING
):
SQL> show timing
timing OFF
SQL> -- save parameters into an os file
SQL> store set param.sql create
Created file param.sql
SQL> -- run script
SQL> SET TIMING ON
SQL> show timing
timing ON
SQL> -- reset state
SQL> @param.sql
SQL> show timing
timing OFF
精彩评论