PL/SQL Parameters   «Prev 

Passing Parameters through the START Command

You can bypass the prompts for values associated with substitution variables by passing values to parameters in a script through the START command. You do this by placing an ampersand (&) followed by a numeral in the script in place of a substitution variable. Each time you run this script, START replaces each &1 in the file with the first value (called an argument) after START filename, then replaces each &2 with the second value, and so forth. For example, you could include the following commands in a script called MYFILE:
In the following START command, SQL*Plus would substitute PU_CLERK for &1 and
3100 for &2 in the script MYFILE:

When you use arguments with the START command, SQL*Plus DEFINEs each parameter in the script with the value of the appropriate argument.

Sending and using parameters in SQL*Plus

You can pass parameters when you start up SQL*Plus from the operating system. Here you set an MS_DOS window. The command shown is starting SQL*Plus, logging in as the PETSTORE user with the GREATPETS password. The SQL*Plus script named CUSTOMER will be run using the two parameters: 1) Amy and 2) HI

Here you see the results of running the script. SQL*Plus displays lines showing the parameter values as they get placed into the script. Then the query is executed and the results are displayed.

I have typed L and pressed Enter, SQL * Plus displays the query that was executed. Notice the two parameters (&1 and &2) in lines 3 and 4.

Demonstrate the second method of sending parameters, I will use the START command. In this window, you see the START command, along with the CUSTOMER script name and different values for the two parameters.

After pressing Enter, SQL*Plus substitutes the values I have specified and executes the query, displaying the results on the screen. The same technique can be used in the Windows version of SQL * Plus in exactly the same way.