Oracle Certification - SQL Fundamentals I - Ampersand Substitution

Single Ampersand Substitution

When Oracle finds a variable starting with an ampersand (&) character it attempts to resolve it by first checking to see if the variable has been defined using the DEFINE command, otherwise the user is prompted for the value, i.e.

Double Ampersand Substitution

When a variable will be repeated with the same value throughout a SQL statement it is preferable to use a double ampersand (&&) substitution. This way the first time Oracle comes across the variable Oracle prompts the user, and then all subsequent instances of that variable with be given the value entered. This double ampersand substitution causes a session variable to be created for each distinct ampersand substitution.

DEFINE, UNDEFINE and VERIFY

VERIFY controls whether or not variable substitutions are echoed to the screen. The basic syntax is SET VERIFY ON|OFF.

DEFINE creates a session variable, syntax DEFINE variable=value; is will also list all defined session variables if called as DEFINE;.

UNDEFINE allows a session variable to be removed, the syntax is UNDEFINE variable.

It is also possible to control whether or not Oracle scans for ampersand variables to substitute. This can be useful when running a script that has ampersands that are not intended for substitution. The syntax is SET DEFINE ON|OFF.

Moving on, Single Row Functions.