What is SET DEF[INE]?

SET DEFINE is the command to set the prefix-character for substitution variables.
 
Please check this workout below :
(Tested on : Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production)
Ranit>> show define
define "&" (hex 26)

Ranit>> select 'Hello &x' name
  2  from dual;
Enter value for x: Ranit
old   1: select 'Hello &x' name
new   1: select 'Hello Ranit' name

NAME                                                                                                                                                                                                                       
-----------                                                                                                                                                                                                                
Hello Ranit  

Ranit>> select 'Hello $x' name
  2  from dual;

NAME                                                                                                                                                                                                                       
--------                                                                                                                                                                                                                   
Hello $x  

Ranit>> select &x,$y
  2  from dual;
Enter value for x: Ranit
old   1: select &x,$y
new   1: select Ranit,$y
select Ranit,$y
             *
ERROR at line 1:
ORA-00911: invalid character 

Now, we'll try to change the prefix-character of the Substitution variables from '&' (default)
to '$' and just check out the difference now -

Ranit>> set define $
Ranit>> show define
define "$" (hex 24)
Ranit>> select 'Hello &x' name
  2  from dual;

NAME                                                                                                                                                                                                                       
--------                                                                                                                                                                                                                   
Hello &x                                                                                                                                                                                                                    

Ranit>> select 'Hello $x' name
  2  from dual;
Enter value for x: Biswas
old   1: select 'Hello $x' name
new   1: select 'Hello Biswas' name

NAME                                                                                                                                                                                                                        ------------                                                                                                                                                                                                               
Hello Biswas                                                                                                                                                                                                               

Ranit>> select &x,$y
  2  from dual;
Enter value for y: Hello
old   1: select &x,$y
new   1: select &x,Hello
SP2-0552: Bind variable "X" not declared.
Ranit>> 

Here, there's another interesting thing to notice - When you have a prefix-character other
than '&' ('$' in our example), then if you execute SET DEFINE ON even once, it gets reverted
again to the default '&'.

Ranit>> show define
define "$" (hex 24)
Ranit>> set define on
Ranit>> show define
define "&" (hex 26)

Doing 'SET DEFINE OFF' and then again 'ON' even has a similar effect of the symbol getting reset
to the default '&'.
Refer (from Oracle docs) - http://docs.oracle.com/cd/B28359_01/server.111/b31189/ch12040.htm#i2698854

HTH. And please rectify me if I'm wrong at any point.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: