SQLPlus Substitution Variables

SQL*Plus is powerful and sometimes powerfully frustrating. One key is to be aware of the substitution variable. By default, this is the ampersand, that twisted almost-figure 8 thing – &. Yeah, that’s the culprit.

Actually it can be very useful in the right hands. Suppose you want to create a script that will prompt for a name when querying for salary:

 SQL> SELECT sal FROM scott.emp WHERE ename LIKE ‘&NAME’;
Enter value for name: SCOTT
old   1: SELECT sal FROM emp WHERE ename LIKE ‘&NAME’
new   1: SELECT sal FROM emp WHERE ename LIKE ‘SCOTT’

       SAL
———-
      3000

You can run the statement again and it will prompt for the name again. You can put in a different name and get their salary result:

SQL> /    (A forward slash will rerun the previous SQL command.)
Enter value for name: WARD
old   1: SELECT sal FROM emp WHERE ename LIKE ‘&NAME’
new   1: SELECT sal FROM emp WHERE ename LIKE ‘WARD’

       SAL
———-
      1250

A double ampersand will create a permanent substitution variable. In the above statement, replace &NAME with &&NAME and it will prompt for the name the first time it is run. Thereafter, it will keep that name as the variable even if you log out of SQL and log back in. Not very variable, is it?

But you can change it. Just type   DEFINE   to see what is set. It should show date, user, and some other things but the list should include a line like this, probably at the bottom:
DEFINE NAME  = ‘SCOTT’ (CHAR)  

Or you could simply type  DEFINE NAME  and it will only show that line without all of the other definitions. Now type  UNDEFINE NAME  and it will be variable once again, until a new value is entered.

Another way to create or change a permanent variable is to define it with a new value. Let’s see what it is now:

SQL> DEFINE NAME
DEFINE NAME = ‘SCOTT’ (CHAR)
If you run the SQL command again it will show the 3000 salary result.
SQL> /   
old   1: SELECT sal FROM emp WHERE ename LIKE ‘&NAME’
new   1: SELECT sal FROM emp WHERE ename LIKE ‘SCOTT’

       SAL
———-
     3000

Now we change it:
SQL> DEFINE NAME = ‘WARD’

And here it is changed:
SQL> DEFINE NAME
DEFINE NAME = ‘WARD’ (CHAR)

And the SQL command will show the 1250 salary.

The third way to change this permanent variable is with the ACCEPT command:
ACCEPT NAME char prompt ‘What name? ‘

When we hit the ENTER key after typing this command, we will be prompted with:
What name?

If we type  KING  then it will go back to a SQL prompt and we can see that it has indeed taken the name KING with:
SQL> DEFINE NAME
DEFINE NAME = ‘KING’ (CHAR)

And if we run our SQL command it will pull the salary info for King:

SQL> /   
old   1: SELECT sal FROM emp WHERE ename LIKE ‘&NAME’
new   1: SELECT sal FROM emp WHERE ename LIKE ‘KING’

       SAL
———-
      5000

 That’s it. You can now create, view, and change variables!

Advertisement