Purpose SET SCAN OFF

Question

Can someone explain why the purpose of SET SCAN OFF and SET SCAN is turned on? I know that his goal is to disable substitution variables and parameters. But I want to give a clear explanation

Answer 1

SET SCAN deprecated, but it was used to control whether it should scan the parameters / substitution variables. OFF prevent scanning of parameters / variables.

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

From the website

set define on;

select '& hello' from dual;

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

Enter a value to greet: this line has been 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 will work. Basically you control whether you should request a replacement

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 the variables defined in SQL * Plus for various reporting tasks.

Since substitution variables begin with an ampersand ('&'), but do not have to be declared in advance, this creates problems if you try to run an SQL statement that includes an ampersand. For example, if you have an INSERT that has a string literal that includes an ampersand, you do not want SQL * Plus to pre-process the statement. Or if I want to select the string "foo and 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 replacement variable, and I am prompted to enter the text to replace at run time

 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 

Answer 3

SQL> set SQL check> / Enter a value for the string: some value old 1: select "foo and bar" from double new 1: select "foo some value" from double

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