sub_var7

iSQL*Plus Release 10.1 interactively prompts for substitution values as the statement referencing them is executed. Each undefined variable is prompted for on its own HTML page. This is similar to command-line SQL*Plus.

Sometimes it is convenient to prompt for more than one value at a time. A separate HTML form can be used to prompt for all input. The values can be passed to iSQL*Plus as script parameters and referenced as substitution variables. See 7.2 iSQL*Plus Parameters.

The prompting model is different in iSQL*Plus 9i. When a script is run in iSQL*Plus Release 9.0 or 9.2, a single HTML page for undefined substitution variables is displayed. After you enter a value for each variable, the script executes and generates its results.

Some differences may be noticed between command-line SQL*Plus and iSQL*Plus Releases 9.0 or 9.2 when your script does one of the following:

These differences include being unnecessarily prompted in iSQL*Plus for values, and an empty string being used instead of the expected value. These problems do not occur in iSQL*Plus 10.1.

iSQL*Plus Releases 9.0 and 9.2 cannot prompt for input in the middle of an executing script. This is due to the way the iSQL*Plus server interacts with the SQL*Plus engine. (The engine is the same statement-executing code used by command-line SQL*Plus. The iSQL*Plus server generates the iSQL*Plus HTML interface and handles HTTP requests). If a script explicitly changes variable definitions, for example by undefining a variable, then iSQL*Plus cannot subsequently prompt for a new value. In this example an empty string is used instead.

In iSQL*Plus, parsing for "&" is performed twice, once by the iSQL*Plus server and once by the SQL*Plus engine. The iSQL*Plus server scans each script for "&" and "&&" references and creates a page with entry fields for undefined variables. When you have given values for the variables, they are transparently sent to the engine as DEFINE commands at the start of your script.

Finds the session's current values of SET DEFINE, SET ESCAPE and SET CONCAT. By default these are "&", OFF and "." respectively. (The values of SET ESCAPE and SET CONCAT are not relevant for this example.)
Parses the script as if it were a single stream of arbitrary words. Since SET DEFINE is not OFF, all "&" variables that were undefined prior to the script being started are recorded. In this example these are "sortcol" and "mytable". No SQL*Plus statements are recognized or processed so the "define mytable" is ignored and the iSQL*Plus server records that an unknown variable "mytable" was referenced in the line "from &mytable".
Creates a page prompting for values of "sortcol" and "mytable". Each variable name occurs only once on the page.
After you enter a value for each variable on the page and click "OK", iSQL*Plus prepends explicit DEFINE commands for the variables and their values to your script. Because only single "&" prefixes were used in this example, iSQL*Plus also appends matching UNDEFINE commands at the end of the script. All extra commands are removed when the script finishes and do not display in the iSQL*Plus Work screen.

The modified script is then sent to the SQL*Plus engine for processing. The engine:

Finds the session's current values of SET DEFINE, SET ESCAPE and SET CONCAT. These are still "&", OFF and "." respectively. Again, only the value of SET DEFINE is relevant to this example.
When the script in this example is run then the two variables get defined by the new, transparently added, DEFINE commands. The script's original define of "mytable" runs next and its value "employees" is the one finally used by the rest of the script.

Since SET DEFINE is not OFF, when "&sortcol" and "&mytable" are seen by the engine's preprocessor, the defined values are used.

At the conclusion of the script, the two variables are undefined in the session by the explicit UNDEFINE commands of the transparently modified script.

The undesired behavior in this example is being prompted for a value for "mytable" despite the script explicitly defining it. However, the prompted value is not used and the correct results are displayed.

The iSQL*Plus server creates the page to enter variable values unless SET DEFINE is OFF before the script is submitted to the SQL*Plus engine for execution. To stop iSQL*Plus prompting for "&" values, make sure DEFINE is OFF. In iSQL*Plus Release 9.2 go to the System Variable page and change the DEFINE radio button to OFF. Then execute your script. In iSQL*Plus Release 9.0 you need to execute an explicit second script containing "SET DEFINE OFF" prior to entering and executing the main script.

If you want to use "&" prefixed variables in a script but know all values are generated in the script (using DEFINE, COLUMN NEW_VALUE or OLD_VALUE) then make sure SET DEFINE is OFF prior to executing the script (to stop the variable entry page being displayed). Also make the first line of the script "SET DEFINE ON" (to allow the SQL*Plus engine to do variable substitution using the script-created variables) and make the last line "SET DEFINE OFF" (to prevent subsequent re-executions of the script from unnecessarily creating the variable entry page).

If you want the main script to prompt for some values, but not others, explicitly define these latter variables before running your main script. The variables you explicitly define do not generate prompts when the main script is later executed. These variables can be defined by executing an initial script containing DEFINE commands. This initial script does not have to give valid values to the variables if the main script is later going to provide them. When the main script is run, iSQL*Plus sees that the variables have already been defined in the current session and does not include them on the variable entry page.

If variables you do want to be prompted for in a script are prefixed with "&&" then make sure the script undefines them at its end. Otherwise the variables become defined in the session. When the script is re-run iSQL*Plus sees the variables have a value and does not include them on the variable entry page. Explicitly undefining the variables allows the script to re-prompt for values each time it is run.

Variables can be passed to iSQL*Plus dynamic reports using the URL syntax. These become defined as if they are named parameters. For example, the iSQL*Plus 10.1 URL:

would define "myv" as "emp" and then run mys.sql. The script can reference "&myv". In SQL*Plus 9.2, the equivalent URL is:

For compatibility with command-line SQL*Plus scripts, you can use numeric names for parameters, e.g. "1=employees"

The "&" in the URL is the character for separating URL variables. It is only coincidentally the same as the default SQL*Plus substitution variable prefix and cannot be changed using SET DEFINE.