Substitution Variables in Oracle

 

Happy new year to you!

I am writing after a long time. Recently I changed my company and It took some time to settle down. But there is no change in learning curve, still learning sql 🙂 I am planning to scribble something on Substitution variables today.

Oracle supports substitution variables to pass run time values with we run a procedure. These substitution variables are prefixed with & (or) && symbol. People says mostly & will be replaced with && for safety measures. At present I dont say what is the difference between the two!!

Whenever Sqlplus finds a variable starts with &, it prompts the user to enter a value for that variable. If that variable is used more than once, then it will prompt once and substitute that value to all other places. Onething, it wont prompt the user for the same.

You can see an example below.

 

SQL> CREATE OR REPLACE PROCEDURE substitutetest
2 (
3 c_name VARCHAR2 := &c_name
4 )
5 IS
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE(c_name);
8 END;
9 /
Enter value for c_name: pandian
old 3: c_name VARCHAR2 := &c_name
new 3: c_name VARCHAR2 := pandian

Warning: Procedure created with compilation errors.

SQL> show errors;
Errors for PROCEDURE SUBSTITUTETEST:

LINE/COL ERROR
-------- -------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
3/20 PLS-00201: identifier 'PANDIAN' must be declared
SQL> /
Enter value for c_name: 'pandian'
old 3: c_name VARCHAR2 := &c_name
new 3: c_name VARCHAR2 := 'pandian'

Procedure created.

SQL> exec substitutetest
pandian

PL/SQL procedure successfully completed.

SQL>

WRT a page I found in OraFaq.com here is the difference between & and &&.

“&” is used to create a temporary substitution variable and will prompt you for a value every time it is referenced.

“&&” is used to create a permanent substitution variable as with the DEFINE command and the OLD_VALUE or NEW_VALUE clauses of a COLUMN statement. Once you have entered a value it will use that value every time the variable is referenced.

Ref:
http://www.sap-img.com/oracle-database/using-substitution-variables.htm
http://www.orafaq.com/faqplus.htm

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