Using Bind Variables
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.
Publish with Variables. To learn how to use variables when publishing to Intelligence Central, see Variables in Views/Scripts and Publishing to Intelligence Central.
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 = ?;
-
Dollar sign ($) in PostgreSQL connections
Example: Select * from employee WHERE user = $1;
Toad also supports these variable formats:
-
Double ampersand (&&variable) as a literal replacement SQL*Plus format in Oracle connections
Example: SELECT * from employee WHERE user = '&&user';
Notes:
- Avoid using the same symbol to represent a bind variable and statement delimiter in the same SQL statement.
-
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:
- If you selected a direction of OUT or RETURN, this value is ignored.
- Values for bind variables are not saved between different sessions of Toad.
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.
- Click
in the Project Manager or Toad Views Manager toolbar.
- Enter a name and a value for the bind variable, and specify a data type.
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.
Use Quote Function to Substitute a String at Run Time
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
- Enter a SQL statement in the Editor.
-
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
- Execute the statement. The Bind Variable dialog opens.
- Enter a string to use in place of :name and click OK.
- Toad inserts the string (quoted if necessary) into the SQL statement and executes it.
Tip: See About Toad Script for more built-in functions that can be used in scripts that you execute in Toad.