2.6 How do I…Pass parameters to a SQL*Plus script?

Problem

I have a script that pauses for users to enter substitution variables. I would like to pass values for the substitution variables on the command line so that the script does not prompt me to enter values. I need these parameters to be referenced throughout the script. How do I pass parameters to a SQL*Plus script?

Technique

User-defined variables, explained in How-To 1.5, can be passed as parameters on the command line. The execution of the script is not halted, and any references to the user-defined variables are replaced with the values passed. Several parameters can be passed in one command. The format of passing parameters is:

SQLPLUS USERNAME/PASSWORD PARAMETER1 [PARAMETER2 PARAMETER3 …]

After a user-defined variable has been created using either of these methods, it can be used throughout the SQL*Plus session by prefixing the variable name with an ampersand (&). The user will not be prompted for the value of the variable again, even if it is used as a substitution variable.

Steps

1. CHP2_10.SQL, shown in Figure 2.11, uses two substitution variables to represent the owner and uses extents. The two variables can be defined by passing parameters.

The script can then be run, passing two parameters:

SQL> @CHP2_10 WAITE 5

The CHP2_10 script will replace the substitution variables in the order the parameters are passed. Thus, &1 is replaced with WAITE, and &2 is replaced with 5. The script will then display all objects owned by the WAITE with five or more extents.

How It Works

The command START or @ will call a script, passing any parameters along. If there are fewer parameters than there are substitution variables, execution is stopped until the user enters values for all remaining parameters. If there are extraneous parameters, the SQL script simply ignores them. The parameters are assigned to substitution variables in the order they are passed: &1 is replaced by the first parameter, &2 is replaced by the second parameter, and so on.

Comments

This technique is useful for writing scripts that call other scripts, passing values automatically. Operating-specific scripts can also be developed to pass environ-ment variables into SQL scripts. This can be important for automatic monitoring scripts and maintenance scripts.