When or Why to use a "SET DEFINE OFF" in Oracle Database
Here is the example:
SQL> set define off;
SQL> select * from dual where dummy='&var';
no rows selected
SQL> set define on
SQL> /
Enter value for var: X
old 1: select * from dual where dummy='&var'
new 1: select * from dual where dummy='X'
D
-
X
With set define off
, it took a row with &var
value, prompted a user to enter a value for it and replaced &var
with the entered value (in this case, X
).
By default, SQL Plus treats '&' as a special character that begins a substitution string. This can cause problems when running scripts that happen to include '&' for other reasons:
SQL> insert into customers (customer_name) values ('Marks & Spencers Ltd');
Enter value for spencers:
old 1: insert into customers (customer_name) values ('Marks & Spencers Ltd')
new 1: insert into customers (customer_name) values ('Marks Ltd')
1 row created.
SQL> select customer_name from customers;
CUSTOMER_NAME
------------------------------
Marks Ltd
If you know your script includes (or may include) data containing '&' characters, and you do not want the substitution behaviour as above, then use set define off
to switch off the behaviour while running the script:
SQL> set define off
SQL> insert into customers (customer_name) values ('Marks & Spencers Ltd');
1 row created.
SQL> select customer_name from customers;
CUSTOMER_NAME
------------------------------
Marks & Spencers Ltd
You might want to add set define on
at the end of the script to restore the default behaviour.