SET SCAN OFF purpose

Question

Can anyone please explain what is the purpose of SET SCAN OFF and SET SCAN ON? I know its purpose is to disable substitution variables and parameters, but I want a clear explanation.

Answer 1

SET SCAN is obsolete but it was used to control whether or not it should scan for substitution params/variables. OFF would prevent scanning for params/variables.

SET DEFINE replaces/extends the functionality and a good writeup is here: http://shaharear.blogspot.com/2009/01/set-define.html

From the website

set define on;

select '&hello' from dual;

If define is set to on and SQL*Plus finds the current substitution prefix, it asks for a string to be entered. In the following example, I entered: hasan

Enter value for hello: this string was entered

 old 1: select '&hello' from dual 
 new 1: select 'this string was entered' from dual

This is equivalent to how the old SET SCAN would work. Basically you're controlling whether or not to prompt for a substitution

Answer 2

In SQL*Plus (and various other tools that support SQL*Plus syntax), by default, the tool scans SQL statements looking for substitution variables. This allows you to create SQL*Plus scripts that use variables defined in SQL*Plus for various reporting tasks.

Because substitution variables begin with the ampersand ('&') and need not be declared in advance, however, that creates problems if you are trying to run a SQL statement that happens to include an ampersand. For example, if you've got an INSERT statement that happens to have a string literal that includes an ampersand, you don't want SQL*Plus to pre-process the statement. Or, if I want to select the string "foo & bar"

SQL> set scan off;
SQL> ed
Wrote file afiedt.buf

  1* select 'foo & bar' from dual
SQL> /

'FOO&BAR'
---------
foo & bar

If I allow SQL*Plus to pre-process the statement, however, the text '& bar' is interpreted as a substitution variable and I'm prompted to enter the text to substitute at runtime

SQL> set scan on
SQL> /
Enter value for bar: some value
old   1: select 'foo & bar' from dual
new   1: select 'foo some value' from dual

'FOOSOMEVALUE'
--------------
foo some value