SET DEFINE

'SET DEFINE' is useful when you are working with sql*plus. There are three combinations of 'SET DEFINE' and those are:

1. set define on.
2. set define off.
3. set define x.
   

1. set define x: set define x specifies the prefix-character for substitution variables. The default is the ampersand (&).

set define &;

select * from dba_objects where object_name like '%&object_name%';


2. set define on: Turns on substitution variables.

set define on;

select '&hello' from dual;

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

Enter value for hello: this string was entered

old 1: select '&hello' from dual
new 1: select 'this string was entered' from dual

'HASAN'
---------------
hasan

It might be annoying to see the following lines printed by SQL*Plus:

old 1: select '&hello' from dual
new 1: select 'this string was entered' from dual

This behavior can be turned off by setting verify off.


3. set define off


Turns off substitution variables.

set define off;
select '&hello' from dual;

'&HELL
----------------
&hello