goelweb.com --> Software --> Oracle Help --> Using Substitution Variables

Using Substitution Variables

Prompting for Undefined Variables

If a variable is referenced using an "&" prefix, but the variable value is not yet defined, SQL*Plus prompts you for a value:

    SQL> define myname
    SP2-0135: symbol myname is UNDEFINED

    SQL> select employee_id from employees where last_name = '&myname';
    Enter value for myname:

After you enter a value, SQL*Plus substitutes the variable and executes the query.

The Oracle Globalization Language setting (e.g. the language component of the NLS_LANG environment variable) determines the exact language used for the "Enter value for" prompt. The prompt text cannot otherwise be changed.

Difference Between "&" and "&&" Prefixes

Both single ampersand (&) and double ampersand (&&) can prefix a substitution variable name in a statement. SQL*Plus pre-processes the statement and substitutes the variable's value. The statement is then executed. If the variable was not previously defined then SQL*Plus prompts you for a value before doing the substitution.

If a single ampersand prefix is used with an undefined variable, the value you enter at the prompt is not stored. Immediately after the value is substituted in the statement the value is discarded and the variable remains undefined. If the variable is referenced twice, even in the same command, then you are prompted twice. Different values can be entered at each prompt:

    SQL> prompt Querying table &mytable
    Enter value for mytable: employees
    Querying table employees
    SQL> select employee_id from &mytable where last_name = 'Jones;
    Enter value for mytable: employees

    EMPLOYEE_ID
    -----------
            195

If a double ampersand reference causes SQL*Plus to prompt you for a value, then SQL*Plus defines the variable as that value. Any subsequent reference to the variable (even in the same command) using either "&" or "&&" substitutes the newly defined value. SQL*Plus will not prompt you again:

    SQL> prompt Querying table &&mytable
    Enter value for mytable: employees
    Querying table employees
    SQL> select employee_id from &mytable where last_name = 'Jones;

    EMPLOYEE_ID
    -----------
            195

Information collected from http://www.oracle.com/technology/support/tech/sql_plus/htdocs/sub_var2.html