Using Variables

Using Variables

TOAD supports all kinds of SQL, from all kinds of applications. If you were to bring in SQL, for example, from a SQL*Forms application, it will contain bind variables. Bind variables are used to supply SQL with data at execution time. This allows applications to use the same SQL statement to select and manipulate different data, depending on the data supplied to the bind variables.

Using the same SQL statement makes efficient use of the Oracle RDBMS SQL pool as the SQL will not be reparsed when using bind variables. The text of the SQL remains the same, so Oracle will reuse the same execution plan, making for a better-performing database environment.

When TOAD encounters bind variables, it will prompt you for their value as in Figure 3.12. This illustration shows two bind variables. The Scan SQL button will check for any missing bind variables, which is particularly useful if you are adding and changing bind variables in this interface.

Figure 3.12. Resolving SQL bind variables.

Substitution variables work the same way as bind variables. Do remember that substitution variables are resolved into SQL text at parse time, but bind variables won't change the actual SQL text (this greatly enables SQL reuse in the Oracle SQL pool). TOAD will prompt for the data for each substitution variable as shown in Figure 3.13.

Figure 3.13. Resolving SQL substitution variables.