- Overview
- Predefined Shortcuts
- User-Defined Shortcuts
- Using Variables
- Table and Column Name Select Lists
- Using Aliases and Autoreplacement Substitutions
- SQL Templates
- Code Completion Templates
- Executing SQL Statements
- Executing SQL Scripts
- Editing Result-Set Data
- Saving Result-Set Data
- Printing Result-Set Data
- Examining Explain Plans
- Examining Basic Performance Info
- Using Auto Trace
- SQL*Plus Compatibility
- Scripts That Write Scripts
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
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.
NOTE
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.
){kind=small}
Figure
3.12Resolving 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.13Resolving SQL substitution variables.