Oracle:Substitution Variables

Main Page >> Oracle and SQL >> Workbook >> Miscellaneous features >> Substitution Variables

Substitution Variables

A Substitution Variable is a user variable name preceded by one ampersand ('&') or two ampersands ('&&'). When SQL*Plus encounters an undefined substitution variable, it will prompt the user for a value.

E.g. if the following is typed:

SELECT count(*) FROM &TABLENAME;

SQL*Plus will display the prompt:

Enter value for tablename:

This command is useful for queries that are going to be saved and reused with different values.

Things to note:

• If you use a variable with one ampersand, SQL*Plus does not define the variable when it prompts for a value. Thus, if the same command is executed later, it will prompt again for another value. However, if you use a variable with two ampersands, SQL*Plus does define the variable, so when the command is later executed, it will not prompt for a value again, instead it will use the previous value.
• If the variable is for a character or date field, then the ampersands must be enclosed in quotes, any wildcards can be added also.

For example, to count the employees from a particular department:

select count(empno) as emp_count 
from emp
where deptno = '&deptno';

When prompted for the deptno, type in a department number, such as 10.

Type RUN to run the command again and try a new department.

You can call the substitution variables anything you like, but it makes more sense to call them a similar name to the field they are related to.

Numeric fields do not need the quotes round the substitution variable.

Using Parameters with the START Command

Parameters can be passed from the command line when using the START or @ command, in the form of:

start  filename  parameter1  parameter2

With this, a SQL query can be saved to a file that lets you enter one or more parameters on the command line after the filename. This is useful if you want to run the same query several times from a batch file, but with different parameters.

A batch file is where several queries can be invoked, one after the other, from the same file.

This is done by placing an ampersand ('&') followed by a number in the SQL query. Each time the query is run, each '&1' is replaced by the first parameter on the command line, each '&2' is replaced by the second parameter and so forth.

To create a batch file that you can run with different parameters

1. Save the following to a file called batch:

ed batch

1. then add the following code and save the file:

select * from emp
where deptno = &1;

To run this command, type:

@batch 10

This will list the above details for employees in department 10.

Select employees with a given name, where name is prompted for:

SELECT * FROM EMP 
WHERE ENAME = UPPER('&ename');

Run the query and try it with different employee names.

Next Step

• Views