Toad Data Point 6.4 - Release Notes

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 specify a default value for the variable before the query is executed by using the Variables option. The default value can be specified by entering the value manually or by using advanced options to configure a list of specific values.

If bind variables are defined in the query, clicking on the Variables button will bring up a new window where all the variables present in the SQL script are listed.

If there are no variables in the SQL script, clicking on the Variables button will raise the following message:

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 (?) in applicable connections, such as ODBC

  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 the default value(s) for the bind variable before the query is run

1. In the Editor, compose a query that contains bind variables.

2. Click on the Variables toolbar icon .

   Note: The Bind Variables dialog does not display if the bind variables option is disabled.

3. Enter the bind variable information. Review the following for additional information:

   Name	Displays the variable name in the form of a clickable link that brings up the Define variable prompt values options for creating a list of predefined variable values.
   Description	Enter the description.
   Type	Select the data type for the variable from the drop-down list.
   Default value	Specify the default value(s) that will be used every time the query is run by: Entering the value(s) for the variable manually Or selecting the value(s) from the predefined list by clicking on the button and choosing the particular value(s) Note: If a variable has been defined with an IN\NOT IN operator, multiple values can be entered by using the semicolon as a delimiter (each value needs to be separated by a semicolon). The predefined list of values list needs to be configured in the Define variable prompt values before using this option

Custom list of values for bind variables

Toad enables you to define a list of specific values that can be used for a bind variable in a query that has been created in the Query Builder or Query Editor.

TThe selected values from the list can be changed at any point in time (before and during query execution) in Toad Data Point/Toad Workbook or Toad Intelligence Central (if the objects are published).his list of predefined values will be saved in the Query Builder or Query Editor file and transferred as a part of any downstream step available in Toad (Pivot, Transform and Cleanse, Dimensional View…).

The selected values from the list can be changed at any point in time (before and during query execution) in Toad Data Point/Toad Workbook or Toad Intelligence Central (if the objects are published).

To create a list of predefined variable values

To access the options for creating a list of predefined variable values, click on the variable name link that will open the Define variable prompt values window.

The following options for defining a list of variable values are available:

• Specify value(s)

  This option enables you to define a list of variable values manually by entering multiple values separated by a semicolon.
  

  Toad also allows you to load the variable values from a file by clicking on the ellipsis button (…) and selecting a specific .txt or .csv file.
  

  Note: Each value in the .txt or .csv file needs to be placed in a new line and no other delimiter should be specified.
  
  

• Get values from the column

  This option enables you to create a predefined list of values that contains all the values from a particular column. The column is selected from the drop-down which lists all the columns defined in the query.
  
  

• Get values from the SQL script

  Clicking on the Edit Query link will open a mini editor in the SQL Script Edit window. This option allows the user to specify a SQL query that will return a result set for a single column which will be used to populate the list of predefined variable values. After the query is specified, it needs to be run in the editor in order to populate the predefined values list.
  

  The query can be entered manually in the editor or it can be loaded from a Query Builder (.tsm) or SQL file (.sql, .tef) by using the Open button.
  

  If the specified query returns a result set with more than one column, it will not be valid and the following message will be displayed (the same message will appear if the query is not run).
  

Additional Define variable prompt values options

The Define variable prompt values also provides additional options:

• Allow user to select multiple values – This option is selected by default and it enables the use of multiple values in the specific variable, meaning that multiple values can be selected for a single variable when using the IN\NOT IN operator.
  

  If this option is deselected, only a single value can be chosen from the list and passed as a variable value.

• Restrict variable value(s) only to list – This option enables the user to restrict the variable values to the predefined list which is created in the Define variable prompt values options. That means that the values cannot be entered manually and can only be selected from the predefined list of values.

Predefined Variable Value List

If the predefined list has been configured in the Define variable prompt values, it will be available in the Variables window within the Default value field, by clicking on the button.

When the button is clicked, it will open the Predefine Values window which contains:

• A Search box (field) that enables the user to search or filter for specific value(s)
  

• The list of values that have been defined in the Define variable prompt values options. Depending on the settings that have been applied, the values will be displayed next to a checkbox (with the Allow user to select multiple values selected) that allows for the selection of multiple values (as shown in the image above) or next to a radio button (with the Allow user to select multiple values deselected), which allows for the selection of a single value.
  
  

• The Refresh button which can be used to refresh the predefined list of values if they are obtained through Get values from the column or Get values from the SQL script options.

To use a list of predefined variable values in Toad Automation

In Toad Automation, the options for creating a list of predefined variable values are available when using Set Variable from System Activities as an automation step.

To enable these options, the Prompt during run checkbox needs to be selected. After the checkbox is ticked, the Define variable prompt values link appears along with a field for selecting the connection that will be used to run the query that populates the list of predefined values for the specific variable.

Clicking on the Define variable prompt values link opens a new window that contains options for defining a list of variable values.

The options and functionality are identical to those that are found in Toad Query Builder, Editor and Workbook with the exception of the Get values from the column option, which is not available in Toad Automation.

To use a list of predefined variable values in Toad Workbook

To access the options for defining a custom list of variable values in Toad Workbook, you need to click on the Variables button, which will open the Workbook Variables dialog.

Options and functionality for creating a list of predefined variable values match the ones found in the Query Builder and Query Editor.

Workbook Variables defined with the same name

If a variable is defined in a specific Workbook Workflow with a custom list of predefined values, this list of variable values (and the option which is utilized to populate the list) will be used for any subsequent variables that are defined with the same name regardless of the Workflow that is currently selected or changes that have been made in the Workflow order.

For example – a variable is defined as variable1 in Workflow 2 and a predefined list of values has been created by using the option Get values from the column.

If another variable is defined with the same name (variable1) in Workflow 1, there will be only one variable present with that particular name in the Workbook Variables dialog, and the values that were initially defined in Workflow 2 will also be used in Workflow 1.

The Workbook Variables dialog contains information about all the Workflows that are using a certain variable in the Used in Workflows column.

To set a bind variable during query run time

1. 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.

2. Enter the bind variable information. Review the following for additional information:

   Include	An option for including\excluding a specific bind variable at run time. The include option is selected by default.
   Name	Displays the name of the bind variable that has been defined in the query.
   Description	Enter the description.
   Type	Select the data type for the variable from the drop-down list.
   Direction	Select the direction of variable from the list.
   Value	Specify the values for the run by: Entering the value(s) for the variable manually Or selecting the value(s) from the predefined list by clicking on the button and choosing the particular value(s) Or loading the values from a specific .txt or .csv file by clicking on the ellipsis (…) button and selecting the appropriate file. Note: If a variable has been defined with an IN\NOT IN operator, multiple values can be entered by using the semicolon as a delimiter (each value needs to be separated by a semicolon). If you selected a direction of OUT or RETURN, this value is ignored. If default values have not previously been set in the Variables option, this field will be populated with values that have been specified in the last run. If default values have previously been configured, any changes made will apply only for the current run. If the values for the run are loaded from a .txt or .csv file, each value in the .txt or .csv file needs to be placed in a new line and no other delimiter should be specified.

    

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 in a global variable

Toad allows you to store a value for a bind variable by defining a global 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.

1. Click in the Project Manager or Toad Views Manager toolbar.
2. 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.

Note: Defining and using a custom list of values for bind variables is not supported for global variables.

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. About Toad Script

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

1. Enter a SQL statement in the Editor.

2. 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

3. Execute the statement. The Bind Variable dialog opens.
4. Enter a string to use in place of :name and click OK.
5. 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.

Related Topics

About Editing SQL