Set DEFINE in SQL with examples.

 

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: agarwal

 

Enter value for hello: this string was entered

 

old 1: select ‘&hello’ from dual

new 1: select ‘this string was entered’ from dual

 

‘AGARWAL’

—————

agarwal

 

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 behaviour 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

 

Advertisements