Monthly Archives: January 2013

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

My Introduction

Hello Oracle Enthusiasts,

This is my debut attempt for blogging some technical stuff.

I’m an Oracle PL/SQL Developer and enthusiast with 4+ yrs of work experience. My work is not just a profession for me; but passion!!!

I started this blog in order to share some of my findings and experiences – my “ubiquitious” note – which I always keep handy. ;)

Passionate about – Singing/Music, Oracle, Technology, Stock Market and many more…

When I am not doing anything, I could be found on OTN (Oracle Technology Network) forum, learning myself or helping others.

Mail me : ranitbiswas@live.com
About me : http://about.me/ranitbiswas

------------------------------------------------------------------------------------------------------------------------
Disclaimer : All content and technical information contained on the site is just my professional opinion and is in no way related to any prior or current employer. Also, please don’t try these directly on Production environment, w/o doing an impact analysis and proper testing.
------------------------------------------------------------------------------------------------------------------------