Declaring and Setting Bind Variables

You can declare and set variables and arrays in ttIsql that can be referenced in a SQL statement, SQL script, or PL/SQL block.

The variables declared using the variable and setvariable command must be one of the following data types: NUMBER, CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB, NCLOB, BLOB, or REFCURSOR. However, when binding arrays, TimesTen supports only binding arrays of the NUMBER, CHAR, NCHAR, VARCHAR2, or NVARCHAR2 data types.

Note:

All variables that are declared exist for the life of the ttIsql session. However, if you declare a new variable with the same name, the new variable replaces the old variable.

To see the help usage on the variable or the var command:

Command> help var;
Arguments in <> are required.
Arguments in [] are optional.

Command Usage: variable [<VariableName> [<DataType>] [:= <Value>]]
Command Aliases: var
Description: Declares a bind variable that can be referenced in a statement, or
displays the definition of the variable if the type is missing. The type can
be one of the following: (n), NUMBER, CHAR(n), NCHAR(n), VARCHAR2(n) , NVARCHAR2(n), BLOB, CLOB, NCLOB, or REFCURSOR. If only '(n)' is supplied, it
is assumed to be VARCHAR2 (n).

The syntax for binding multiple values to an array using the variable command
is as follows: variable ArrayName '[' ArraySize ']' DataType(n) := '[' Value1,... ValueX ']'

Requires an active connection: NO

Requires autocommit turned off: NO

Reports elapsed execution time: NO

Works only with a TimesTen data source: NO

Example: variable; -or- variable a varchar2(30); -or- var arr[5] number := [ 1, 2, 3 ];

To see the help usage on the define2var. It defines the value and make it a variable that you can use as a parameter variable.

Command> help define2var;
Arguments in <> are required.
Arguments in [] are optional.
Command Usage: define2var <alias> <varname>
Command Aliases: (none)
Description: Copies a substitution alias to a variable value. Both the alias
and the variable must exist. The variable must be character based. If it is
an array, you must specify which element to define and you must use a value
that fits within the size of the array. If it is not an array, the variable
may be enlarged to fit the new length if necessary.
Requires an active connection: NO
Requires autocommit turned off: NO
Reports elapsed execution time: NO
Works only with a TimesTen data source: NO
Example: define2var myalias mybindvar; define2var a1 arr[2];

To see the help usage on the var2define. It takes the value of a named variable and then define it.

Command> help var2define;
Arguments ! in <> are required.
Arguments in [] are optional.
Command Usage: var2define <varname> <alias>
Command Aliases: (none)
Description: Copies a variable value to a substitution alias. The variable
must be character based. If it is an array, you must specify which element to
define. The define does not need to exist prior to running this command. A
NULL value in the variable defines an empty string.
Requires an active connection: NO
Requires autocommit turned off: NO
Reports elapsed execution time: NO
Works only with a TimesTen data source: NO
Example: var2define mybindvar myalias; var2define arr[2] alias2;

To declare, set the value of variables, and print their values:

Command> var a varchar2(100); #declare variable a

Command> var b varchar2(100) := 'This is B'; #declare variable b and assign value to it

Command> var;  #display available variables

variable   A

datatype   VARCHAR2(100)

variable   B

datatype   VARCHAR2(100)

Command> print a; #print the value of variable a


A            : <NULL>

Command> print; #print values of all available variables

A            : <NULL>

B            : This is B

The following examples declare bind variables with the variable or var command for a number, character string, and an array. Each is assigned to a value either when declared or by using the setvariable or setvar command.

Command> VARIABLE house_number NUMBER := 268; #declare variable house_number and assign the value
Command> PRINT house_number;  #print the value of house_number variable
HOUSE_NUMBER            : 268

Command> VARIABLE street_name VARCHAR2(15); #declare variable street_name
Command> SETVARIABLE street_name := 'Oracle Parkway'; #set the value of the variable street_name
Command> PRINT street_name;
STREET_NAME      :   Oracle Parkway

Command> VARIABLE occupants[5] VARCHAR2(15);  #declare an array variable
Command> SETVARIABLE occupants[1] := 'Pat'; #set the value at the first position of the array
Command> SETVARIABLE occupants[2] := 'Terry'; #set the value at the second position of the array
Command> PRINT occupants;  #print the array
OCCUPANTS            : ARRAY [ 5 ] (Current Size 2)
OCCUPANTS[1] : Pat
OCCUPANTS[2] : Terry

The following is an example of binding multiple values in an array using square brackets to delineate the values and commas to separate each value for the array:

Command> VARIABLE occupants[5] VARCHAR2(15) := ['Pat', 'Terry']; #declare an array variable with values
Command> PRINT occupants; #print an array
OCCUPANTS : ARRAY [ 5 ] (Current Size 2)
OCCUPANTS[1] : Pat
OCCUPANTS[2] : Terry

When using array binds, PL/SQL enables you to bind each variable to a PL/SQL variable with the following declaration, where TypeName is any unique identifier for the PL/SQL data type and DataType can be specified as CHAR, NCHAR, VARCHAR2, or NVARCHAR2.

TYPE TypeName IS TABLE OF DataType(<precision>) INDEX BY BINARY_INTEGER;

If the variable is declared as array of NUMBER, you can bind it to a PL/SQL variable of the following data types: NUMBER, INTEGER, FLOAT, or DOUBLE PRECISION. To do so, use the appropriate declaration:

TYPE TypeName IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE TypeName IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
TYPE TypeName IS TABLE OF FLOAT INDEX BY BINARY_INTEGER;
TYPE TypeName IS TABLE OF DOUBLE PRECISION INDEX BY BINARY_INTEGER;

The following example declares the occupants VARCHAR2 array, which is then declared and used within a PL/SQL block:

Command> VARIABLE occupants[5] VARCHAR2(15);
Command> SETVARIABLE occupants[1] := 'Pat';
Command> SETVARIABLE occupants[2] := 'Terry';
Command> DECLARE
TYPE occuname IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
x occuname;
BEGIN
x := :occupants;
FOR LROW IN x.FIRST..x.LAST LOOP
x(LROW) := x(LROW) || ' Doe';
END LOOP;
:occupants := x;
END;
/
 
PL/SQL procedure successfully completed.
 
Command> PRINT occupants;
OCCUPANTS    : ARRAY [ 5 ] (Current Size 2)
OCCUPANTS[1] : Pat Doe
OCCUPANTS[2] : Terry Doe