View source for Oracle:Substitution Variables

[[Main Page]] >> [[Oracle|Oracle and SQL]] >> [[Oracle_Workbook|Workbook]] >> [[Oracle:Misc|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

* Save the following to a file called batch:

 ed batch

* 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 ==
*[[Oracle:Views|Views]]