PL/SQL Parameters   «Prev  Next»

Lesson 3How to use parameters in SQL*Plus
Objective Use parameters when starting a SQL*Plus script.

Sendig Parameters to SQL*Plus

In SQL*Plus, parameters can be sent in two different ways:
  1. You can pass parameters to a SQL*Plus script when starting SQL*Plus from the operating system command line
  2. You can pass parameters to a SQL*Plus script when using the START commands

The parameters are automatically named &1, &2, and so on based on their position. Parameters are always separated by blank space. If you need to specify a parameter that contains blanks, enclose the parameter in double quotes.
Use the following Slideshow to see how to use parameters both ways.

Sending and using Parameters in SQL*Plus


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

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

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

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

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

Using SQL/Plus Parameters
Behind the scenes, SQL*Plus has defined two variables with these two names and has populated the variables with the values sent from the command line. Because SQL*Plus treats the parameters just like variables, SQL*Plus prompts the user for the parameters if the user does not provide values when starting a SQL*Plus script that references the parameters. In this case, the user must type in the value and press Enter. Then the script continues to execute.
You cannot specify parameters when using the RUN or the "/" commands to execute a SQL*Plus script.
The next lesson describes how to use parameters in PL/SQL.

SQL/Plus Variable Arguments

Click the link below to read about SQL*Plus parameters like those used in the Slideshow.
SQL/PLUS Variable Arguments