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
.
TYPETypeName
IS TABLE OFDataType
(<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:
TYPETypeName
IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPETypeName
IS TABLE OF INTEGER INDEX BY BINARY_INTEGER; TYPETypeName
IS TABLE OF FLOAT INDEX BY BINARY_INTEGER; TYPETypeName
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.