RelationalDBDesignRelationalDBDesign





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:
SELECT * FROM EMP_DETAILS_VIEW
WHERE JOB_ID='&1'
AND SALARY='&2';
In the following START command, SQL*Plus would substitute PU_CLERK for &1 and
3100 for &2 in the script MYFILE:
START MYFILE PU_CLERK 3100

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
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.
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.
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.
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.
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.