Ampersand Substitution and Session Variables
Exam Topic: Restricting and Sorting Data – Use ampersand substitution to restrict and sort output at runtime
The Ampersand (&) and Double-Ampersand (&&) are used in oracle as prompts for input. They are called “Substitution Variables.” They can be used to input a value or dynamically build or modify a statement at runtime. This allows you to save interactive scripts for later use, which saves you time. The ampersand can be used in SQL Plus as well as Toad and SQL Developer.
Ampersand (&) Substitution Variables
The ampersand is used as a prompt for a variable. To create one, use & plus some descriptive text. The text will be used as the prompt. The value prompted for is a string. Rather than expecting someone to remember to enclose it with single parentheses, put the substitution variable inside single parentheses in the statement.
SQL> select zip_code from tbl_locations where state='&ST'; Enter value for st:
Ampersand substitution can be used to build a statement. Notice that &field is used twice in the query. You might think that it would only prompt you once since it has the same name, but it doesn’t.
SQL> select &field from tbl_locations where state='&ST' order by &field; Enter value for field: zip_code Enter value for st: GA Enter value for field: zip_code old 1: select &field from tbl_locations where state='&ST' order by &field new 1: select zip_code from tbl_locations where state='GA' order by zip_code
Double Amersand (&&) Session Variables
We can change the previous example to only prompt for field once by using the double ampersand substitution variable. Using double ampersands creates a session variable. This value and corresponding single-ampersand variables of the same name won’t prompt again for a value after the first time the value is set. In fact, the value remains assigned until you un-assign it (see undefine below) or end your session.
SQL> select &&field from tbl_locations where state='&ST' order by &field; Enter value for field: zip_code Enter value for st: GA old 1: select &&field from tbl_locations where state='&ST' order by &field new 1: select zip_code from tbl_locations where state='GA' order by zip_code
Run another command using the same variable, I am not prompted. It just…. knows…. (because it’s a session variable now.)
SQL> select state from tbl_locations order by &field; old 1: select state from tbl_locations order by &field new 1: select state from tbl_locations order by zip_code
DEFINE and UNDEFINE
DEFINE alone shows the session variables that are currently defined. A runtime substitution variable with a single ampersand will not show up in this list. A session variable defined with the double ampersand will. All of the session variables preceded by an underscore (_) are session variables created by Oracle.
SQL> define; DEFINE _DATE = "24-JUN-15" (CHAR) DEFINE _CONNECT_IDENTIFIER = "XE" (CHAR) DEFINE _USER = "RSIMS" (CHAR) DEFINE _PRIVILEGE = "" (CHAR) DEFINE _SQLPLUS_RELEASE = "1102000200" (CHAR) DEFINE _EDITOR = "Notepad" (CHAR) DEFINE _O_VERSION = "Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production" (CHAR) DEFINE _O_RELEASE = "1102000200" (CHAR)
DEFINE can also be used to create and/or assign value to a session variable. Once created, the variable and its value will show up in the DEFINE list.
SQL> DEFINE ST=GA; SQL> DEFINE; DEFINE _DATE = "24-JUN-15" (CHAR) DEFINE _CONNECT_IDENTIFIER = "XE" (CHAR) DEFINE _USER = "RSIMS" (CHAR) DEFINE _PRIVILEGE = "" (CHAR) DEFINE _SQLPLUS_RELEASE = "1102000200" (CHAR) DEFINE _EDITOR = "Notepad" (CHAR) DEFINE _O_VERSION = "Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production" (CHAR) DEFINE _O_RELEASE = "1102000200" (CHAR) DEFINE ST = "GA" (CHAR)
You can use DEFINE and the variable to see the value of that variable, as well.
SQL> define state='GA' SQL> define state DEFINE STATE = "GA" (CHAR) SQL> select count(*) from tbl_locations where state='&&state'; old 1: select count(*) from tbl_locations where state='&&state' new 1: select count(*) from tbl_locations where state='GA' COUNT(*) ---------- 10
UNDEFINE destroys a session variable. This will cause either a double-ampersand session variable or single-ampersand substitution variable of the same name to prompt for a value next time it is used.
I will UNDEFINE state and run DEFINE again. It is no longer on the list.
SQL> UNDEFINE state; SQL> DEFINE; DEFINE _DATE = "24-JUN-15" (CHAR) DEFINE _CONNECT_IDENTIFIER = "XE" (CHAR) DEFINE _USER = "RSIMS" (CHAR) DEFINE _PRIVILEGE = "" (CHAR) DEFINE _SQLPLUS_RELEASE = "1102000200" (CHAR) DEFINE _EDITOR = "Notepad" (CHAR) DEFINE _O_VERSION = "Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production" (CHAR) DEFINE _O_RELEASE = "1102000200" (CHAR)
SET DEFINE [ON | OFF]
Sometimes, you will have data that uses an ampersand. This often happens to me when loading data. Users will enter their data how they want, and they want to use the ampersand instead of the word “and”, so you have to make adjustments for this. It’s easy enough to do.
If you don’t want ampersands to be used for defining variables, turn it off.
SQL> insert into tbl_log values (16, sysdate, 'Open & Ready'); Enter value for ready: old 1: insert into tbl_log values (16, sysdate, 'Open & Ready') new 1: insert into tbl_log values (16, sysdate, 'Open ') 1 row created. SQL> delete from tbl_log where log_id=16; 1 row deleted. SQL> SET DEFINE OFF; SQL> insert into tbl_log values (16, sysdate, 'Open & Ready'); 1 row created. SQL> SET DEFINE ON; SQL> select log_desc from tbl_log where log_id=16; LOG_DESC -------------------------------------------------- Open & Ready
SET VERIFY [ON | OFF]
Finally, when using substitution variables, you may have a requirement to not see the OLD/NEW values. You can turn those messages on or off using VERIFY.
SQL> SET VERIFY OFF; SQL> select count(*) from tbl_locations where state='&st'; Enter value for st: GA COUNT(*) ---------- 10 SQL> SET VERIFY ON; SQL> select count(*) from tbl_locations where state='&st'; Enter value for st: GA old 1: select count(*) from tbl_locations where state='&st' new 1: select count(*) from tbl_locations where state='GA' COUNT(*) ---------- 10