Runtime variable substitution

SQL *Plus allows you to import and execute SQL scripts from previously saved text files (command files). This can be very handy because you often have functions that you need over and over, especially as a DBA. The problem with this approach is that you may want to perform the same function with a different database object, for instance you may want a script that creates a user account on an Oracle system but probably want it to specify the new account name each time. You can achieve this with variable substitution.

Stored command files are fetched and run by specifying the file name (with full path) after the "@" symbol. For example :-
@C:\SCRIPTS\QUERY\JD11\FINDSECT.SQL
You can use variable substitution on any WHERE clause, text string, column name or table name.

The first type of substitution is the single ampersand "&".
SELECT TITLE FROM JD11.BOOK WHERE SECTION = &SEC_NUMBER;

If this statement were typed into SQL *Plus or retrieved as a command file you would see a prompt appear asking you to specify the value of &SEC_NUMBER, this means you can vary the result set whilst using the same pre prepared SQL statement. Remember that date and string values must always be enclosed in single quotes, you can get around having to type them in by putting them into the SQL statement.

SELECT AUTHOR FROM JD11.BOOK WHERE TITLE = '&REQUIRED_TITLE';

You now don't have to remember the quotes as they are part of the prepared statement.SELECT AUTHOR, &OTHER_FIELD FROM JD11.BOOK WHERE &CONDITION
The statement above will generate two prompts. The first prompt will ask you to specify a column name, this allows you to vary the fields you want the result set to display. The second prompt will ask you to specify the entire WHERE clause, this gives you the freedom to change the WHERE clause each time you run the statement.

The second type of substitution is the double ampersand "&&".

This type of substitution means that you only type a variable value in once.

SELECT TITLE FROM JD11.BOOK WHERE SECTION = &&SEC_NUMBER;