Toad supports the use of bind variables (bind parameters) in SQL statements. When you execute a statement containing a bind variable, Toad prompts you to enter a value.
Toad also allows you to store values for bind variables. You can store and manage multiple variable-value pairs.
Automation Scripts. To learn about entering variable values for Automation scripts at run time, see the Set Variable and Send Email activities in Use System Automation Activities.
Toad supports the following bind variable formats:
Colon (:variable)
Example: SELECT * from employee WHERE user = :user;
At symbol (@variable) in SQL Server connections
Example: SELECT * from employee WHERE user = @user;
Question mark (?)
Example: SELECT * from employee WHERE user = ?;
Notes:
In the Editor, click to enable/disable binding variables in the SQL. When disabled, Toad does not scan SQL for parameters. This button is enabled (depressed) by default.
To set a bind variable
In the Editor, compose and execute a query that contains bind variables. Toad immediately displays the Bind Variables dialog.
Note: The Bind Variables dialog does not display if the bind variables option is disabled.
Enter the bind variable information. Review the following for additional information:
Type |
Select the data type for the variable from the list. |
Description |
Enter the description. Tip: You can enter a description in advance when preparing a query. It will be picked up automatically in the Description field of the Bind Variables window. Add a comment in the following format after a bind variable: :user; /* description */ |
Direction |
Select the direction of variable from the list. |
Value |
Enter the value of the variable. Notes:
|
To use a bind variable in the Query Builder
In the Where, Having, or OR Condition editor, enter the bind variable in place of a constant.
To store a value for a bind variable
Toad allows you to store a value for a bind variable. Then, when you execute a SQL statement containing that bind variable, Toad will not prompt you for a value. You can access this feature from the Project Manager or the Toad Views manager.
Tip: After you store a bind variable, it is available to be used as a variable in Automation. The stored bind variables appear in the list of variables in the expression editors in various activities.
In addition to supporting the use of bind variables, Toad also provides a way for you to easily substitute a string, such as a database name or table name, in your SQL statement at execution time. This method uses the Quote() function, one of Toad's built-in functions (known as Toad Script) for SQL statements. The Quote function can insert the value of a bind variable as a literal value before the statement is sent to the server. This is useful if you want to use bind variables in places where they are not allowed. See About Toad Script for more information.
For example, suppose you want to create the same table in several different databases, but you do not want to re-write the SQL statement for each database. You can use one statement that includes the Quote() function in place of the database name.
Create table {{Quote(:dbname)}}.NewTable as select * from dual
When you execute the statement, Toad prompts you to enter a string value in place of {{Quote(:dbname)}}. You then enter the database name.
To use the Toad Quote function to substitute a string at run time
Use the following syntax in place of the string that you want to substitute.
{{Quote(:name)}}
For example:
Create table {{Quote(:name)}} as select * from dual
Tip: See About Toad Script for more built-in functions that can be used in scripts that you execute in Toad.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. 이용 약관 개인정보 보호정책 Cookie Preference Center