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

Question

I'm watching a Script in Oracle and I see something I don't recognize

REM INSERTING into database1."Users"  SET DEFINE OFF; Insert into database1."Users" ("id","right") values ('1','R'); 

I'm looking for documentation about "set define off" and it's literally writing "disable the parsing of commands to replace substitution variable with their values"

I don't really understand what they want to say.

Can anyone help me?

Answer 1

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.

Answer 2

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