When or Why to use a "SET DEFINE OFF" in Oracle Database

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 offSQL> 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.

Here is the example:

SQL> set define off;SQL> select * from dual where dummy='&var';no rows selectedSQL> set define onSQL> /Enter value for var: Xold   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).