DEFINEs and bind variables

SQL*Plus can also create and manipulate its own in-memory variables, and it set aside a few special variables that will affect its behavior. Actually, there are two separate types of variables in SQL*Plus:

DEFINEs and bind variables. To assign a value to a DEFINE variable, you can use the DEFINE command:

SQL> DEFINE x = "the answer is 42"

 

To view the value of x, specify:

SQL> DEFINE x

DEFINE X = "the answer is 42" (CHAR)

 

You would refer to such a variable using an ampersand (&). SQL*Plus dose a simple substitution before

sending the statement to the Oracle database, so you will need single quote marks around the variable when  you want to use it as a literal string:

SELECT '&x' FROM DUAL;

 

For bind variables, you first declare the variable. You can then use it in PL/SQL, and display it using the

SQL*Plus PRINT command:

SQL> VARIABLE x VARCHAR2(10)

SQL> BEGIN

2               :x := 'hullo';

3         END;

4         /

 

PL/SQL procedure successfully completed.

 

SQL> PRINT :x

X
--------------------------------
hullo

 

This can get a little bit confusing because there are now two different “x” variables, one

that has been defined and one that has been declared:

SQL> SELECT :x, '&x' FROM DUAL;
old   1: SELECT :x, '&x' FROM DUAL
new   1: SELECT :x, 'the answer is 42' FROM DUAL
:X                               'THEANSWERIS42'
-------------------------------- ----------------
hullo                            the answer is 42

 

Just remember that DEFINEs are always character strings expanded by SQL*Plus, and declared variables are used as true bind variables in SQL and PL/SQL.