Oracle Database - SQL Plus - Substitution Variables

About
You can define variables, called substitution variables, for repeated use in a single script. Note that you can also define substitution variables to use in titles and to save your keystrokes (by defining a long string as the value for a variable with a short name).

A substitution variable is preceded by one or two ampersands (&).

How to Create a Substitution Variable

Temporary substitution variable:-

When SQL Plus find a substitution variable define by using only one ampersand (&), it tries to replace it with the value of one permanent substitution variable previously defined otherwise, it will prompt you to enter a value that will be use only once.

Permanent Substitution Variables:-

A permanent Substitution Variables is a variable available for the complete session.

Set up a substitution Variable ?

To define a substitution variable, you can use:-

-> DEFINE command

-> Two Ampersands

-> ACCEPT command

-> COLUMN NEW_VALUE

DEFINE command:

         DEFINE L_NAME = SMITH

Note that any substitution variable you define explicitly through DEFINE takes only CHAR values (that is, the value you assign to the variable is always treated as a CHAR datatype). You can define a substitution variable of datatype NUMBER implicitly through the ACCEPT command.

Two ampersands:

SQL*Plus automatically DEFINEs any substitution variable preceded by two ampersands, but does not DEFINE those preceded by only one ampersand.

Oradata@orcl>select &&MySubstitutionVariable FROM dual;

Enter VALUE FOR mysubstitutionvariable: 'Value'

old 1: SELECT &&MySubstitutionVariable FROM dual

NEW 1: SELECT 'Value' FROM dual

'VALUE'

-------

VALUE

Oradata@orcl>DEFINE MySubstitutionVariable

DEFINE MYSUBSTITUTIONVARIABLE = "'Value'" (CHAR)

NOTE: One thing to note here is: Any character string values to be given to variable, must be define in single quotes else the value we provide, will itself be considered as Variable.

Enter value for counter: dsfs

old   5: v_count := &counter;

new   5: v_count := dsfs;

ERROR at line 5:

ORA-06550: line 5, column 12:

PLS-00201: identifier 'DSFS' must be declared

ORA-06550: line 5, column 1:

PL/SQL: Statement ignored

ACCEPT command:
ACCEPT pswd CHAR PROMPT 'Password: ' HIDE 

For a number

ACCEPT salary NUMBER FORMAT '999.99' DEFAULT '000.0' -
PROMPT 'Enter weekly salary: '

PROMPT You have enter: &salary

If we do not Enter salary in defined format, it will keep prompting for right format salary:

SQL> ACCEPT salary NUMBER FORMAT '999.99' DEFAULT '000.0'

4553.223

SP2-0598: "4553.223" does not match input format "999.99"

444.33

COLUMN NEW_VALUE

You can store the value of a column value in a variable using this statement

COLUMN column_name NEW_VALUE variable_name

Example:

define SpoolFileId=idle

column ReleaseId NEW_VALUE SpoolFileId 

select TO_CHAR(SYSDATE,'YYYYMMDD_HH24MMSS_') || '_Release.log' ReleaseId from dual; 

PROMPT The value of SpoolFileId is now: &SpoolFileId

then to define a log file, you can use this statement:

SPOOL '&SpoolFileId'

How to delete a substitution variable?
To delete a substitution variable, use the SQL*Plus command UNDEFINE followed by the variable name.

UNDEFINE MySubstitutionVariable

How to list?

All substitution variables:

To list all substitution variable definitions, enter DEFINE by itself.

SQL> DEFINE

DEFINE _DATE = "14-JUN-10" (CHAR)

DEFINE _CONNECT_IDENTIFIER = "bidb" (CHAR)

DEFINE _USER = "DWH" (CHAR)

DEFINE _PRIVILEGE = "" (CHAR)

DEFINE _SQLPLUS_RELEASE = "1002000300" (CHAR)

DEFINE _EDITOR = "Notepad" (CHAR)

DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.2.0.

4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options" (

CHAR)

DEFINE _O_RELEASE = "1002000400" (CHAR)

DEFINE MY_SUBSTITUTION_VARIABLE = "1" (CHAR)

One substitution variable

PROMPT The value of MySubstitutionVariable is: &MySubstitutionVariable

or

DEFINE MySubstitutionVariable

Parameters:

SET DEFINE:

SET DEFINE defines the substitution character (by default the ampersand ”&”) and turns substitution on and off.

SET DEFINE OFF:

To turn off this functionality (also in SQL Developer).

SET VERIFY:

After you enter a value at the prompt, SQL*Plus lists the line containing the substitution variable twice: once before substituting the value you enter and once after substitution. You can suppress this listing by setting the SET command variable VERIFY to OFF.

SET VERIFY ON. Lists each line of the script before and after substitution. This can be used for debug purpose also. We are able to see which value got assigned to which value variable.

Like in below example:
SQL>  define pswd=12
SQL> define charctr=123
SQL> DECLARE
  2  v_count NUMBER;
  3  v_char VARCHAR2(30);
  4  BEGIN
  5  v_count := &pswd;
  6  v_char := &charctr;
  7  DBMS_OUTPUT.PUT_LINE('First Value of v_count ' ||v_count);
  8  DBMS_OUTPUT.PUT_LINE('Value of v_char ' ||v_char);
  9  END;
 10  /
First Value of v_count 12
Value of v_char 123

PL/SQL procedure successfully completed.

SQL>
SQL> set verify on
SQL> DECLARE
  2  v_count NUMBER;
  3  v_char VARCHAR2(30);
  4  BEGIN
  5  v_count := &pswd;
  6  v_char := &charctr;
  7  DBMS_OUTPUT.PUT_LINE('First Value of v_count ' ||v_count);
  8  DBMS_OUTPUT.PUT_LINE('Value of v_char ' ||v_char);
  9  END;
 10  /
old   5: v_count := &pswd;
new   5: v_count := 12;
old   6: v_char := &charctr;
new   6: v_char := 123;
First Value of v_count 12
Value of v_char 123

PL/SQL procedure successfully completed.

http://www.oracle-base.com/articles/misc/literals-substitution-variables-and-bind-variables.php