Hi,

Another  benefit of SQL environment variable, Today I would like to tell our experience about this variable. few days back for a deployment one of my friend faced this issue,  though it is a simple matter it took more than an hour to find and fix.

The issue here was before deploying the scripts we set our environment variable scripts in which it is SCAN=Off which suppresses the processing of substitution variables. SCAN=on is default one.

From Doc
SET SCAN {ON|OFF} (obsolete)

Controls scanning for the presence of substitution variables and parameters. OFF suppresses processing of substitution variables and parameters; ON enables normal processing.

ON functions in the same manner as SET DEFINE ON.

Here is our experiment

SQL> sho scan
scan ON
SQL> conn a/a
Connected.
SQL> define schema_name=a
SQL> update &schema_name..t1 set rno=3;
old   1: update &schema_name..t1 set rno=3
new   1: update a.t1 set rno=3

700 rows updated.

SQL> set scan off
SQL> update &schema_name..t1 set rno=4;

SP2-0552: Bind variable "SCHEMA_NAME" not declared.

SQL> set scan on
SQL> update &schema_name..t1 set rno=4;
old   1: update &schema_name..t1 set rno=4
new   1: update a.t1 set rno=4

700 rows updated.

So whenever you get this error first we can check this variable and proceed with other things any..,

Happy Reading :) ..