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