Using, Declaring, and Setting Variables in ttIsql

You can declare, set and use bind variables in ttIsql.

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.

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.

Note:

See ttIsql in the Oracle TimesTen In-Memory Database Reference.

Command> VARIABLE house_number NUMBER := 268;
Command> PRINT house_number;
HOUSE_NUMBER            : 268

Command> VARIABLE street_name VARCHAR2(15);
Command> SETVARIABLE street_name := 'Oracle Parkway'; 

Command> VARIABLE occupants[5] VARCHAR2(15);
Command> SETVARIABLE occupants[1] := 'Pat'; 
Command> SETVARIABLE occupants[2] := 'Terry';
Command> PRINT occupants;
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'];
Command> PRINT occupants;
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

Automatically Creating Bind Variables for Retrieved Columns

When you set autovariables on in ttIsql, TimesTen creates an automatic bind variable named after each column in the last fetched row. An automatic bind variable can be used in the same manner of any bind variable.

The following example selects all rows from the employees table. Since all columns are retrieved, automatic variables are created and named for each column. The bind variable contains the last value retrieved for each column.

Command> SET AUTOVARIABLES ON;
Command> SELECT * FROM employees;
...
< 204, Hermann, Baer, HBAER, 515.123.8888, 1994-06-07 00:00:00, PR_REP, 10000,
 <NULL>, 101, 70 >
< 205, Shelley, Higgins, SHIGGINS, 515.123.8080, 1994-06-07 00:00:00, AC_MGR, 
12000, <NULL>, 101, 110 >
< 206, William, Gietz, WGIETZ, 515.123.8181, 1994-06-07 00:00:00, AC_ACCOUNT, 
8300, <NULL>, 205, 110 >

Command> PRINT;
EMPLOYEE_ID          : 206
FIRST_NAME           : William
LAST_NAME            : Gietz
EMAIL                : WGIETZ
PHONE_NUMBER         : 515.123.8181
HIRE_DATE            : 1994-06-07 00:00:00
JOB_ID               : AC_ACCOUNT
SALARY               : 8300
COMMISSION_PCT       : <NULL>
MANAGER_ID           : 205
DEPARTMENT_ID        : 110

If you provide an alias for a column name, the automatic bind variable name uses the alias, rather than the column name.

Command> SET AUTOVARIABLES ON;
Command> SELECT employee_id ID, First_name SURNAME, last_name LASTNAME 
 FROM employees;
 
ID, SURNAME, LASTNAME
...
< 204, Hermann, Baer >
< 205, Shelley, Higgins >
< 206, William, Gietz >
107 rows found.
Command> PRINT;
ID                   : 206
SURNAME              : William
LASTNAME             : Gietz

For any query that fetches data without a known named column, set columnlabels on to show the column names. The following example shows that the columns returns from ttConfiguration built-in procedure are paramname and paramvalue.

Command> SET AUTOVARIABLES ON;
Command> SET COLUMNLABELS ON;

Command> call TTCONFIGURATION('LockLevel');

PARAMNAME, PARAMVALUE
< LockLevel, 0 >
1 row found.

Command> IF :paramvalue = 1 THEN "e:Database-level locking is enabled";
Command> IF NOT  :paramvalue = 1 THEN "e:Row-level locking is enabled";
Row-level locking is enabled

You can also use the describe command to show the column names. The following example uses the describe command to display the column names for the ttConfiguration built-in procedure.

Command> DESCRIBE TTCONFIGURATION;
 
Procedure TTCONFIGURATION:
  Parameters:
    PARAMNAME                       TT_VARCHAR (30)
  Columns:
    PARAMNAME                       TT_VARCHAR (30) NOT NULL
    PARAMVALUE                      TT_VARCHAR (1024)
 
1 procedure found.