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. View the following series of images to see how to use parameters both ways.

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.

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 following section discusses SQL*Plus parameters like those used in the series of images above.

The following steps were completed:

1. You started SQL*Plus, logged in as PETSTORE, and executed the FUNNY script, passing Dog and 01-NOV-99 as parameters. To do this, you typed SQLPLUS PETSTORE/GREATPETS @FUNNY Dog 01-NOV-99 and pressed Enter.
2. You saw the results displayed in the window. Now you start another script file and pass two more parameters by typing START FUNNIER "All wet" 10 and pressing Enter.
3. You saw the results of the query and that ended the simulation.

You do not pass parameters to an external procedure directly. Instead, you pass them to the PL/SQL subprogram that registered the external procedure. So, you must specify PL/SQL datatypes for the parameters. Each PL/SQL datatype maps to a default external datatype. (In turn, each external datatype maps to a C datatype.)

In some cases, you can use the PARAMETERS clause to override the default datatype mappings. For example, you can re-map the PL/SQL datatype BOOLEAN from external datatype INT to external datatype CHAR.
The next lesson describes how to use parameters in PL/SQL.