A data type defines a set of values. A reference to a data type specifies the set of values that can occur in a given context. A data type is associated with each value retrieved from a table or computed in an expression and each constant.
TimesTen follows the ODBC standard for type conversion. For more information, refer to ODBC API reference documentation, which is available from Microsoft or a variety of third parties. The following site contains Microsoft's ODBC API reference documentation:
https://msdn.microsoft.com/en-us/library/ms714562(VS.85).aspx
If you are using TimesTen Cache, see "Mappings between Oracle Database and TimesTen data types" in Oracle TimesTen Application-Tier Database Cache User's Guide. This section compares valid data types for creating cache group columns and type conversions for passthrough queries.
The following subjects describe data types in TimesTen:
Table 1-1, "Supported data types" shows the supported data types in TimesTen.
Table 1-1 Supported data types
Data type | Description |
---|---|
Fixed-length binary value of Legal values for See "BINARY and VARBINARY data types" for details. |
|
A 64-bit floating-point number
Minimum positive finite value: 2.22507485850720E-308 Maximum positive finite value: 1.79769313486231E+308 See "BINARY_DOUBLE" for details. |
|
A 32-bit floating-point number
Minimum positive finite value: 1.17549E-38F Maximum positive finite value: 3.40282E+38F See "BINARY_FLOAT" for details. |
|
A variable-length binary large object. The valid range is from 1 to 16,777,216 bytes. See "BLOB" for details. |
|
Fixed-length character string of length
If you do not specify A zero-length string is interpreted as
See "CHAR" for details. |
|
A variable-length character large object containing single-byte or multibyte characters. The valid range is from 1 to 4,194,304 bytes of data depending on the database character set. Specifically, the maximum size of a See "CLOB" for details. |
|
Date and time information: century, year, month, day, hour, minute, and second Format is:
Valid date range is from January 1, 4712 BC to December 31, 9999 AD. There are no fractional seconds. See "DATE" for details. |
|
Interval type TimesTen partially supports interval types, expressed with the type Note: You cannot specify a column of an interval type. These are non-persistent types used in SQL expressions at runtime. In addition, for those comparisons where an interval data type is returned, the interval data type cannot be the final result of a complete expression. The See "TimesTen intervals" for details. |
|
Fixed-length string of The number of bytes required is 2* A zero-length string is interpreted as
See "NCHAR" for details. |
|
A national variable-length character large object containing Unicode characters. The valid range is from 1 to 2,097,152 characters. See "NCLOB" for details. |
|
Number having precision and scale. The precision ranges from 1 to 38 decimal. The scale ranges from -84 to 127. Both precision and scale are optional. If you do not specify a precision or a scale, TimesTen assumes the maximum precision of 38 and flexible scale.
See "NUMBER" for details. |
|
Variable-length string of The number of bytes required is 2* The valid range is from 1 to 2,097,152 characters. You must specify A zero-length string is interpreted as Nonpadded comparison semantics are used. See "NVARCHAR2" for details. |
|
An 18-byte character string that represents the address of a table row or materialized view row in TimesTen Classic. Specify a literal See "ROWID data type" for details. |
|
A time of day between 00:00:00 (midnight) and 23:59:59 (11:59:59 pm), inclusive The format is: Alternatively, specify See "TIME" for details. |
|
|
Year, month, and day values of the date plus hour, minute, and second values of the time The
The fractional seconds precision range is 0 to 9. The default is 6. Format is:
See "TIMESTAMP" for details. |
A signed eight-byte integer in the range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. Use See "TT_BIGINT" for details. |
|
Date information: century, year, month, and day The format is Valid dates are between 1753-01-01 (January 1, 1753) and 9999-12-31 (December 31, 9999). See "TT_DATE" for details. |
|
A signed integer in the range -2,147,483,648 to 2,147,483,647.
See "TT_INTEGER" for details. |
|
A native signed 16-bit integer in the range -32,768 to 32,767. Use
See "TT_SMALLINT" for details. |
|
A date and time between 1753-01-01 00:00:00 (midnight on January 1, 1753) and 9999-12-31 23:59:59 pm (11:59:59 pm on December 31, 9999), inclusive Any values for the fraction not specified in full microseconds result in a "
You can specify See "TT_TIMESTAMP" for details. |
|
Unsigned integer ranging from 0 to 255. Use Since See "TT_TINYINT" for details. |
|
Variable-length binary value with length The valid range is from 1 to 4,194,304 bytes. Alternatively, specify See "BINARY and VARBINARY data types" for details. |
|
Variable-length character string with a length of
For byte-length semantics, the valid range is from 1 to 4,194,304 bytes. For character-length semantics, the valid range is from 1 to 1,048,576 characters. If you do not specify A zero-length string is interpreted as Nonpadded comparison semantics are used. Do not use the See "VARCHAR2" for details. |
TimesTen supports ANSI SQL data types. These data types are converted to TimesTen data types with data stored as TimesTen data types. Table 1-2 shows how the ANSI SQL data types are mapped to TimesTen data types.
Table 1-2 Data type mapping: ANSI SQL to TImesTen
ANSI SQL data type | TimesTen data type |
---|---|
Character semantics is supported. |
|
Floating-point number with a binary precision of 126. Alternatively, specify |
|
Floating-point number with binary precision
Internally, |
|
|
|
|
|
|
|
Specifies a fixed-point number with precision |
|
Floating-point number with a binary precision of 63. Alternatively, specify |
|
|
TimesTen supports the data types shown in Table 1-3.
Table 1-3 Data types supported for backward compatibility
Data type | Description |
---|---|
Fixed-length character string of length Default is one byte.
If you insert a zero-length (empty) string into a column, the SQL
|
|
Fixed-length string of The number of bytes required is 2* If you insert a zero-length (empty) string into a column, the SQL
|
|
Variable-length string of The number of bytes required is 2* If you insert a zero-length (empty) string into a column, the SQL Blank-padded comparison semantics are used. |
|
Variable-length character string having maximum length You must specify
If you insert a zero-length (empty) string into a column, the SQL Blank-padded comparison semantics are used. |
Character data types store character (alphanumeric) data either in the database character set or the UTF-16 format. Character data is stored in strings with byte values. The byte values correspond to one of the database character sets defined when the database is created. TimesTen supports both single and multibyte character sets.
The character types are as follows:
The CHAR
type specifies a fixed length character string. If you insert a value into a CHAR
column and the value is shorter than the defined column length, then TimesTen blank-pads the value to the column length. If you insert a value into a CHAR
column and the value is longer than the defined length, TimesTen returns an error.
By default, the column length is defined in bytes. Use the CHAR
qualifier to define the column length in characters. The size of a character ranges from one byte to four bytes depending on the database character set. The BYTE
and CHAR
qualifiers override the NLS_LENGTH_SEMANTICS
parameter setting. For more information about NLS_LENGTH_SEMANTICS
, see "ALTER SESSION". Also see "Setting globalization support attributes" in Oracle TimesTen In-Memory Database Operations Guide.
Note:
With theCHAR
type, a zero-length string is interpreted as NULL
. With the TT_CHAR
type, a zero-length string is a valid non-NULL
value. Both CHAR
and TT_CHAR
use blank padded comparison semantics. The TT_CHAR
type is supported for backward compatibility.The following example creates a table. Columns are defined with type CHAR
and TT_CHAR
. Blank padded comparison semantics are used for these types.
Command> CREATE TABLE typedemo (name CHAR (20), nnme2 TT_CHAR (20)); Command> INSERT INTO typedemo VALUES ('SMITH ','SMITH '); 1 row inserted. Command> DESCRIBE typedemo; Table USER.TYPEDEMO: Columns: NAME CHAR (20) NAME2 TT_CHAR (20) 1 table found. (primary key columns are indicated with *) Command> SELECT * FROM typedemo; < SMITH , SMITH > 1 row found. Command> -- Expect 1 row found; blank-padded comparison semantics Command> SELECT * FROM typedemo WHERE name = 'SMITH'; < SMITH , SMITH > 1 row found. Command> SELECT * FROM typedemo WHERE name2 = 'SMITH'; < SMITH , SMITH > 1 row found. Command> -- Expect 0 rows; blank padded comparison semantics. Command> SELECT * FROM typedemo WHERE name > 'SMITH'; 0 rows found. Command> SELECT * FROM typedemo WHERE name2 > 'SMITH'; 0 rows found.
The following example alters table typedemo
adding column name3
. The column name3
is defined with character semantics.
Command> ALTER TABLE typedemo ADD COLUMN name3 CHAR (10 CHAR); Command> DESCRIBE typedemo; Table USER.TYPEDEMO: Columns: NAME CHAR (20) NAME2 TT_CHAR (20) NAME3 CHAR (10 CHAR) 1 table found.
The NCHAR
data type is a fixed length string of two-byte Unicode characters. NCHAR
data types are padded to the specified length with the Unicode space character U+0020 SPACE
. Blank-padded comparison semantics are used.
Note:
With theNCHAR
type, a zero-length string is interpreted as NULL
. With the TT_NCHAR
type, a zero-length string is a valid non-null value. Both NCHAR
and TT_NCHAR
use blank padded comparison semantics. The TT_NCHAR
type is supported for backward compatibility.
The NCHAR
data type is encoded as UTF-16.
The following example alters table typedemo
to add column Name4
. Data type is NCHAR
.
Command> ALTER TABLE typedemo ADD COLUMN Name4 NCHAR (10); Command> DESCRIBE typedemo; Table USER.TYPEDEMO: Columns: NAME CHAR (20) NAME2 TT_CHAR (20) NAME3 CHAR (10 CHAR) NAME4 NCHAR (10) 1 table found.
The VARCHAR2
data type specifies a variable length character string. When you define a VARCHAR2
column, you define the maximum number of bytes or characters. Each value is stored exactly as you specify it. The value cannot exceed the maximum length of the column.
You must specify the maximum length. The minimum must be at least one byte. Use the CHAR
qualifier to specify the maximum length in characters. For example, VARCHAR2(10 CHAR)
.
The size of a character ranges from one byte to four bytes depending on the database character set. The BYTE
and CHAR
qualifiers override the NLS_LENGTH_SEMANTICS
parameter setting. For more information on NLS_LENGTH_SEMANTICS
, see "ALTER SESSION". Also see "Setting globalization support attributes" in Oracle TimesTen In-Memory Database Operations Guide.
The NULL
value is stored as a single bit for each nullable field within the row. An INLINE VARCHAR2(
n
)
whose value is NULL
takes (null bit) + four bytes + n
bytes of storage, or n
more bytes of storage than a NOT INLINE VARCHAR2(
n
)
whose value is NULL
. This storage principal holds for all variable length data types: TT_VARCHAR
, TT_NVARCHAR
, VARCHAR2
, NVARCHAR2
, VARBINARY
.
Notes:
Do not use the VARCHAR
data type. Use VARCHAR2
. Even though both data types are currently synonymous, the VARCHAR
data type may be redefined as a different data type with different semantics.
With the VARCHAR2
type, a zero-length string is interpreted as NULL
. With the TT_VARCHAR
type, a zero-length string is a valid non-NULL value. VARCHAR2
uses nonpadded comparison semantics. TT_VARCHAR
uses blank-padded comparison semantics. The TT_VARCHAR
type is supported for backward compatibility.
The following example alters table typedemo
, adding columns name5
and name6
. The name5
column is defined with type VARCHAR2
. The name6
column is defined with TT_VARCHAR
. The example illustrates the use of nonpadded comparison semantics with column name5
and blank-padded comparison semantics with column name6
:
Command> ALTER TABLE typedemo ADD COLUMN name5 VARCHAR2 (20); Command> ALTER TABLE typedemo ADD COLUMN name6 TT_VARCHAR (20); Command> DESCRIBE typedemo; Table USER.TYPEDEMO: Columns: NAME CHAR (20) NAME2 TT_CHAR (20) NAME3 CHAR (10 CHAR) NAME4 NCHAR (10) NAME5 VARCHAR2 (20) INLINE NAME6 TT_VARCHAR (20) INLINE 1 table found. (primary key columns are indicated with *) Command> -- Insert SMITH followed by 5 spaces into all columns Command> INSERT INTO typedemo VALUES ('SMITH ', 'SMITH ', 'SMITH ', 'SMITH ', 'SMITH ', 'SMITH'); 1 row inserted. Command> -- Expect 0; Nonpadded comparison semantics Command> SELECT COUNT (*) FROM typedemo WHERE name5 = 'SMITH'; < 0 > 1 row found. Command> -- Expect 1; Blank-padded comparison semantics Command> SELECT COUNT (*) FROM typedemo WHERE name6 = 'SMITH'; < 1 > 1 row found. Command> -- Expect 1; Nonpadded comparison semantics Command> SELECT COUNT (*) FROM typedemo WHERE name5 > 'SMITH'; < 1 > 1 row found. Command> -- Expect 0; Blank-padded comparison semantics Command> SELECT COUNT (*) FROM typedemo WHERE name6 > 'SMITH'; < 0 > 1 row found.
The NVARCHAR2
data type is a variable length string of two-byte Unicode characters. When you define an NVARCHAR2
column, you define the maximum number of characters. Each value is stored exactly as you specify it. The value cannot exceed the maximum length of the column.
Note:
With theNVARCHAR2
type, a zero-length string is interpreted as NULL
. With the TT_NVARCHAR
type, a zero-length string is a valid non-NULL value. NVARCHAR2
uses nonpadded comparison semantics. TT_NVARCHAR
uses blank-padded comparison semantics. The TT_NVARCHAR
type is supported for backward compatibility.
The NVARCHAR2
data type is encoded as UTF-16.
The following example alters table typedemo
to add column name7
. Data type is NVARCHAR2
.
Command> ALTER TABLE typedemo ADD COLUMN Nnme7 NVARCHAR2 (20); Command> DESCRIBE typedemo; Table USER1.TYPEDEMO: Columns: NAME CHAR (20) NAME2 TT_CHAR (20) NAME3 CHAR (10 CHAR) NAME4 NCHAR (10) NAME5 VARCHAR2 (20) INLINE NAME6 TT_VARCHAR (20) INLINE NAME7 NVARCHAR2 (20) INLINE 1 table found.
Numeric types store positive and negative fixed and floating-point numbers, zero, infinity, and values that are the undefined result of an operation (NaN
, meaning not a number).
TimesTen supports both exact and approximate numeric data types. Arithmetic operations can be performed on numeric types only. Similarly, SUM
and AVG
aggregates require numeric types.
Following are the exact numeric types.
The NUMBER
data type stores both zero and positive and negative fixed numbers with absolute values from 1.0 x 10 -130 up to but not including 1.0 x 10 126. Each NUMBER
value requires from five to 22 bytes.
Specify a fixed-point number as NUMBER(
p,s
)
, where the following holds:
The argument p
is the precision or the total number of significant decimal digits, where the most significant digit is the left-most nonzero digit and the least significant digit is the right-most known digit.
The argument s
is the scale, or the number of digits from the decimal point to the least significant digit. The scale ranges from -84 to 127.
Positive scale is the number of significant digits to the right of the decimal point up to and including the least significant digit.
Negative scale is the number of significant digits to the left of the decimal point up to but not including the least significant digit. For negative scale, the least significant digit is on the left side of the decimal point, because the number is rounded to the specified number of places to the left of the decimal point.
Scale can be greater than precision. For example, in the case of E-notation. When scale is greater than precision, the precision specifies the maximum number of significant digits to the right of the decimal point. For example, if you define the column as type NUMBER(4,5)
and you insert .000127 into the column, the value is stored as .00013. A zero is required for the first digit after the decimal point. TimesTen rounds values after the fifth digit to the right of the decimal point.
If a value exceeds the precision, then TimesTen returns an error. If a value exceeds the scale, then TimesTen rounds the value.
NUMBER(
p
)
represents a fixed-point number with precision p
and scale 0 and is equivalent to NUMBER(
p
,0)
.
Specify a floating-point number as NUMBER
. If you do not specify precision and scale, TimesTen uses the maximum precision and scale.
The following example alters table numerics
by adding columns col6
, col7
, col8
, and col9
defined with the NUMBER
data type and specified with different precisions and scales.
Command> ALTER TABLE numerics ADD col6 NUMBER; Command> ALTER TABLE numerics ADD col7 NUMBER (4,2); Command> ALTER TABLE numerics ADD col8 NUMBER (4,-2); Command> ALTER TABLE numerics ADD col8 NUMBER (2,4); Command> ALTER TABLE numerics ADD col9 NUMBER (2,4); Command> DESCRIBE numerics; Table USER1.NUMERICS: Columns: COL1 TT_TINYINT COL2 TT_SMALLINT COL3 TT_INTEGER COL4 TT_INTEGER COL5 TT_BIGINT COL6 NUMBER COL7 NUMBER (4,2) COL8 NUMBER (4,-2) COL9 NUMBER (2,4) 1 table found. (primary key columns are indicated with *)
The next example creates table numbercombo
and defines columns with the NUMBER
data type using different precisions and scales. The value 123.89 is inserted into the columns.
Command> CREATE TABLE numbercombo (col1 NUMBER, col2 NUMBER (3), col3 NUMBER (6,2), col4 NUMBER (6,1), col5 NUMBER (6,-2)); Command> DESCRIBE numbercombo; Table USER1.NUMBERCOMBO: Columns: COL1 NUMBER COL2 NUMBER (3) COL3 NUMBER (6,2) COL4 NUMBER (6,1) COL5 NUMBER (6,-2) 1 table found. (primary key columns are indicated with *) Command> INSERT INTO numbercombo VALUES (123.89,123.89,123.89,123.89,123.89); 1 row inserted. Command> VERTICAL ON; Command> SELECT * FROM numbercombo; COL1: 123.89 COL2: 124 COL3: 123.89 COL4: 123.9 COL5: 100 1 row found.
The next example creates a table and defines a column with data type NUMBER(4,2)
. An attempt to insert a value of 123.89 results in an overflow error.
Command> CREATE TABLE invnumbervalue (col6 NUMBER (4,2)); Command> INSERT INTO invnumbervalue VALUES (123.89); 2923: Number type value overflow The command failed.
The next example creates a table and defines columns with the NUMBER
data type using a scale that is greater than the precision. Values are inserted into the columns.
Command> CREATE TABLE numbercombo2 (col1 NUMBER (4,5), col2 NUMBER (4,5), col3 NUMBER (4,5), col4 NUMBER (2,7), col5 NUMBER (2,7), col6 NUMBER (2,5), col7 NUMBER (2,5)); Command> INSERT INTO numbercombo2 VALUES (.01234, .00012, .000127, .0000012, .00000123, 1.2e-4, 1.2e-5); 1 row inserted. Command> DESCRIBE numbercombo2; Table USER1.NUMBERCOMBO2: Columns: COL1 NUMBER (4,5) COL2 NUMBER (4,5) COL3 NUMBER (4,5) COL4 NUMBER (2,7) COL5 NUMBER (2,7) COL6 NUMBER (2,5) COL7 NUMBER (2,5) 1 table found. (primary key columns are indicated with *) Command> SELECT * FROM numbercombo2; COL1: .01234 COL2: .00012 COL3: .00013 COL4: .0000012 COL5: .0000012 COL6: .00012 COL7: .00001 1 row found.
The TT_BIGINT
data type is a signed integer that ranges from -9,223,372,036,854,775,808 (-263) to 9,223,372,036,854,775,807 (263-1). It requires eight bytes of storage and thus is more compact than the NUMBER
data type. It also has better performance than the NUMBER
data type. You cannot specify BIGINT
.
This example alters table numerics
and attempts to add col5
with a data type of BIGINT
. TimesTen generates an error. A second ALTER TABLE
successfully adds col5
with the data type TT_BIGINT
.
Command> ALTER TABLE numerics ADD COLUMN col5 BIGINT; 3300: BIGINT is not a valid type name; use TT_BIGINT instead The command failed. Command> ALTER TABLE numerics ADD COLUMN col5 TT_BIGINT; Command> DESCRIBE numerics; Table USER1.NUMERICS: Columns: COL1 TT_TINYINT COL2 TT_SMALLINT COL3 TT_INTEGER COL4 TT_INTEGER COL5 TT_BIGINT 1 table found. (primary key columns are indicated with *)
The TT_INTEGER
data type is a signed integer that ranges from -2,147,483,648 (-231) to 2,147,483,647 (231 -1). It requires four bytes of storage and thus is more compact than the NUMBER
data type. It also has better performance than the NUMBER
data
type. You can specify TT_INT
for TT_INTEGER
. If you specify either INTEGER
or INT
, these types are mapped to NUMBER(38)
.
The following example alters the table numerics
and adds col3
with the data type INT
. Describing the table shows that the data type is NUMBER(38)
. The column col3
is dropped. A second ALTER TABLE
adds col2
with the data type INTEGER
. Describing the table shows that the data type is NUMBER(38)
. The column col3
is dropped. Columns col3
and col4
are then added with the data types TT_INTEGER
and TT_INT
. Describing the table shows both data types as TT_INTEGER
.
Command> ALTER TABLE numerics ADD col3 INT; Command> DESCRIBE numerics; Table USER1.NUMERICS: Columns: COL1 TT_TINYINT COL2 TT_SMALLINT COL3 NUMBER (38) 1 table found. (primary key columns are indicated with *) Command> ALTER TABLE numerics col3; Command> ALTER TABLE numerics ADD col3 INTEGER; Command> DESCRIBE numerics; Table USER1.NUMERICS: Columns: COL1 TT_TINYINT COL2 TT_SMALLINT COL3 NUMBER (38) 1 table found. (primary key columns are indicated with *) Command> ALTER TABLE numerics col3; Command> ALTER TABLE numerics ADD COLUMN col3 TT_INTEGER; Command> DESCRIBE numerics; Table USER1.NUMERICS: Columns: COL1 TT_TINYINT COL2 TT_SMALLINT COL3 TT_INTEGER 1 table found. (primary key columns are indicated with *) Command> ALTER TABLE numerics ADD col4 TT_INT; Command> DESCRIBE numerics; Table USER1.NUMERICS: Columns: COL1 TT_TINYINT COL2 TT_SMALLINT COL3 TT_INTEGER COL4 TT_INTEGER 1 table found. (primary key columns are indicated with *)
The TT_SMALLINT
data type is a signed integer that ranges from -32,768 (-215) to 32,767 (215-1). It requires two bytes of storage and thus is more compact than the NUMBER
data type. It also has better performance than the NUMBER
data type. You can specify the data type SMALLINT
, but it maps to NUMBER(38)
.
The following example alters the table numerics
and adds col2
with the data type SMALLINT
. Describing the table shows that the data type is NUMBER(38)
. The column col2
is dropped. A second ALTER TABLE
adds col2
with the data type TT_SMALLINT
.
Command> ALTER TABLE numerics ADD COLUMN col2 SMALLINT; Command> DESCRIBE Numerics; Table USER1.NUMERICS: Columns: COL1 TT_TINYINT COL2 NUMBER (38) 1 table found. (primary key columns are indicated with *) Command> ALTER TABLE numerics COLUMN col2; Command> ALTER TABLE numerics ADD COLUMN col2 TT_SMALLINT; Command> DESCRIBE numerics; Table USER1.NUMERICS: Columns: COL1 TT_TINYINT COL2 TT_SMALLINT 1 table found. (primary key columns are indicated with *)
The TT_TINYINT
data type is an unsigned integer that ranges from 0 to 255 (28 -1). It requires one byte of storage and thus is more compact than the NUMBER
data type. It also has better performance than the NUMBER
data type. The data type of a negative TT_TINYINT
is TT_SMALLINT
. You cannot specify TINYINT
.
The following example first attempts to create a table named numerics
that defines a column named col1
with data type TINYINT
. TimesTen returns an error. The example then redefines the column with data type TT_TINYINT
.
Command> CREATE TABLE numerics (col1 TINYINT); 3300: TINYINT is not a valid type name; use TT_TINYINT instead The command failed. Command> CREATE TABLE numerics (col1 TT_TINYINT); Command> DESCRIBE numerics; Table USER1.NUMERICS: Columns: COL1 TT_TINYINT 1 table found. (primary key columns are indicated with *)
Floating-point numbers can be with or without a decimal point. An exponent may be used to increase the range (for example, 1.2E-20).
Floating-point numbers do not have a scale because the number of digits that can appear after the decimal point is not restricted.
Binary floating-point numbers are stored using binary precision (the digits 0 and 1). For the NUMBER
data type, values are stored using decimal precision (the digits 0 through 9).
Literal values that are within the range and precision supported by NUMBER
are stored as NUMBER
because literals are expressed using decimal precision.
Use one of the following data types for floating-point numbers:
BINARY_DOUBLE
is a 64-bit, double-precision, floating-point number.
Both BINARY_FLOAT
and BINARY_DOUBLE
support the special values Inf
, -Inf
, and NaN
(not a number) and conform to the IEEE standard.
Floating-point number limits:
BINARY_FLOAT
Minimum positive finite value: 1.17549E-38F
Maximum positive finite value: 3.40282E+38F
BINARY_DOUBLE
Minimum positive finite value: 2.22507485850720E-308
Maximum positive finite value: 1.79769313486231E+308
The following example creates a table and defines two columns with the BINARY_FLOAT
and BINARY_DOUBLE
data types.
Command> CREATE TABLE BfBd (Col1 BINARY_FLOAT, Col2 BINARY_DOUBLE); Command> DESCRIBE BfBd; Table UISER1.BFBD: Columns: COL1 BINARY_FLOAT COL2 BINARY_DOUBLE 1 table found. (primary key columns are indicated with *)
TimesTen also supports the ANSI type FLOAT
. FLOAT
is an exact numeric type and is implemented as the NUMBER
type. The value of n
indicates the number of bits of precision that can be stored, from 1 to 126. To convert from binary precision to decimal precision, multiply n
by 0.30103. To convert from decimal precision to binary precision, multiply the decimal precision by 3.32193. The maximum 126 digits of binary precision is equivalent to approximately 38 digits of decimal precision.
The BINARY
data type is a fixed-length binary value with a length of n
bytes, where the value of n
ranges from 1 to 8300 bytes. The BINARY
data type requires n
bytes of storage. Data is padded to the maximum column size with trailing zeros. Zero padded comparison semantics are used.
The VARBINARY
data type is a variable-length binary value having a maximum length of n
bytes, where the value of n
ranges from 1 to 4,194,304 (222) bytes.
The following example creates a table and defines two columns: col1
is defined with data type BINARY
and col2
with data type VARBINARY
. Then, binary data is inserted into each column. Note that the BINARY
value is padded to the right with zeros.
Note:
For details on assigning hexadecimal literals as binary data in TimesTen, see the description for theHexadecimalLiteral
in "Constants".Command> CREATE TABLE bvar (col1 BINARY (10), col2 VARBINARY (10)); Command> DESCRIBE bvar; Table USER1.BVAR: Columns: COL1 BINARY (10) COL2 VARBINARY (10) INLINE 1 table found. (primary key columns are indicated with *) Command> INSERT INTO bvar (col1, col2) VALUES (0x4D7953514C, 0x39274D); 1 row inserted. Command> SELECT * FROM bvar; < 4D7953514C0000000000, 39274D > 1 row found.
The result type of an expression is determined by the operand with the highest type precedence. The numeric precedence order is as follows (highest to lowest):
BINARY_DOUBLE
BINARY_FLOAT
NUMBER
TT_BIGINT
TT_INTEGER
TT_SMALLINT
TT_TINYINT
For example, the sum of TT_INTEGER
and BINARY_FLOAT
values is type BINARY_FLOAT
because BINARY_FLOAT
has higher numeric precedence. Similarly, the product of NUMBER
and BINARY_DOUBLE
values is type BINARY_DOUBLE
.
LOB data types are not supported in TimesTen Scaleout.
The large object (LOB) data types can store large and unstructured data such as text, image, video, and spatial data. LOBs include the BLOB
, CLOB
and NCLOB
data types.
You can insert or update data in a column that is of a LOB data type. For update operations, you can set the LOB value to NULL
, an empty value through EMPTY_CLOB
or EMPTY_BLOB
, or replace the entire LOB with new data. You can update a LOB value with another LOB value. If you delete a row containing a LOB column, you also delete the LOB value.
LOB data type semantics are similar to the following SQL semantics:
BLOB
data types use SQL VARBINARY
semantics.
CLOB
data types use SQL VARCHAR2
semantics.
NCLOB
data types use SQL NVARCHAR2
semantics.
The following SQL statements, operators, and functions accept one or more of the LOB data types as arguments.
SQL statements: CREATE TABLE
, SELECT
, INSERT
, and UPDATE
Operators: LIKE
and IS [NOT] NULL
Functions: ASCIISTR
, CONCAT
, INSTR
, INSTRB
, INSTR4
, LENGTH
, LENGTHB
, LOWER
, LPAD
, NLSSORT
, NVL
, TRIM
, LTRIM
, RTRIM
, SUBSTR
, SUBSTRB
, SUBSTR4
, REPLACE
, RPAD
, SOUNDEX
, TO_DATE
, TO_NUMBER
, TO_CHAR
, and UPPER
Note:
Support for LOB data types is detailed in documentation for the above statements, operators, and functions. Refer to Chapter 6, "SQL Statements", Chapter 5, "Search Conditions", and Chapter 4, "Functions", respectively.Description
LOB conversion SQL functions (TO_BLOB, TO_CLOB, and TO_LOB) convert to the desired LOB data type.
LOB columns are always stored out of line, so you cannot use the INLINE
attribute when declaring LOB columns.
You can define multiple columns of the LOB data type within a single table.
You cannot create a primary key on a LOB column. You cannot define an index on a LOB column.
You cannot create a materialized view if the detail table contains a LOB column.
In addition to SQL, you can use LOB specific APIs in PL/SQL, ODBC, JDBC, OCI, and PRO*C/C++ for creating and updating LOBs. See the appropriate TimesTen developer's guide for more information on these APIs.
The following sections describe each LOB data type in more detail:
In addition, the following sections provide more details on LOBs in general:
The Binary LOB (BLOB
) data type stores unstructured binary large objects. The maximum size for BLOB
data is 16 MB.
Note:
For details on assigning hexadecimal literals as binary data in TimesTen, see the description for theHexadecimalLiteral
in "Constants".When you define a BLOB
in a column, you do not define the maximum number of characters as you would with VARBINARY
and other variable length data types. Instead, the definition for the column would be as follows:
Command> CREATE TABLE blob_content (id NUMBER PRIMARY KEY, blob_column BLOB );
To manipulate a BLOB
, the following functions are provided:
There are two methods to initialize a BLOB
, including the EMPTY_BLOB
function to initialize an empty BLOB
. For details on initializing a BLOB
, see "Initializing LOBs". For details on how an empty LOB is different from a NULL
LOB, see "Difference between NULL and empty LOBs".
To convert a binary value to a BLOB
, use the TO_LOB
or TO_BLOB
functions. See "TO_BLOB" and "TO_LOB" for more details.
The Character LOB (CLOB
) data type stores single-byte and multibyte character data. The maximum size for CLOB
data is 4 MB. The maximum number of characters that can be stored in the CLOB
depends on whether you are using a single or multibyte character set.
When you define a CLOB
in a column, you do not define the maximum number of characters as you would with VARCHAR
and other variable length data types. Instead, the definition for the column would be as follows:
Command> CREATE TABLE clob_content (id NUMBER PRIMARY KEY, clob_column CLOB );
To manipulate a CLOB
, the following functions are provided:
There are two methods to initialize a CLOB
, including the EMPTY_CLOB
function to initialize an empty CLOB
. For details on initializing a CLOB
, see "Initializing LOBs". For details on how an empty LOB is different from a NULL
LOB, see "Difference between NULL and empty LOBs" below.
To convert a character string to a CLOB
, use the TO_LOB
or TO_CLOB
functions. See "TO_CLOB" and "TO_LOB" for more details.
The National Character LOB (NCLOB
) data type stores Unicode data. The maximum size for an NCLOB
data is 4 MB.
When you define a NCLOB
in a column, you do not define the maximum number of characters as you would with VARCHAR
and other variable length data types. Instead, the definition for the column would be as follows:
Command> CREATE TABLE nclob_content (id NUMBER PRIMARY KEY, nclob_column NCLOB );
The following functions support the NCLOB
data type:
There are two methods to initialize an NCLOB
, including the EMPTY_CLOB
function to initialize an empty NCLOB
. For details on initializing a NCLOB
, see "Initializing LOBs". For details on how an empty LOB is different from a NULL
LOB, see "Difference between NULL and empty LOBs", immediately below.
To convert a character string to an NCLOB
, use the TO_LOB
or TO_CLOB
functions. See "TO_CLOB" and "TO_LOB" for more details.
A NULL
LOB has a different meaning than an empty LOB.
A NULL
LOB has the value of NULL
, so NULL
is returned if you request a NULL
LOB.
An empty LOB is initialized with either the EMPTY_CLOB
or EMPTY_BLOB
functions. These functions initialize the LOB to be a zero-length, non-NULL
value. You can also use the EMPTY_CLOB
or EMPTY_BLOB
functions to initialize a LOB in a non-nullable column.
You can initialize a LOB in one of two ways:
You can insert an empty LOB into a BLOB
, CLOB
or NCLOB
column by using the EMPTY_BLOB
or EMPTY_CLOB
functions. This is useful when you do not have any data, but want to create the LOB in preparation for data. It is also useful for initializing non-nullable LOB columns.
Initialize the LOB by inserting data directly. There is no need to initialize a LOB using the EMPTY_BLOB
or EMPTY_CLOB
functions, you can simply insert the data directly.
The following demonstrates examples of each type of initialization:
You can initialize a LOB with the EMPTY_CLOB
function, as shown with the following example:
Command> INSERT INTO clob_content (id, clob_column) VALUES (1, EMPTY_CLOB( ) ); 1 row inserted.
You can initialize a LOB by inserting data directly, as shown with the following example:
Command> INSERT INTO clob_content(id, clob_column) VALUES (4, 'Demonstration of the LOB initialization.'); 1 row inserted.
You can initialize or update an existing LOB value with the UPDATE
statement, as shown with the following examples:
Command> UPDATE blob_content SET blob_column = 0x000AF4511 WHERE id = 1; 1 row updated. Command> SELECT * FROM blob_content; < 1, 0000AF4511 > 1 rows found. Command> UPDATE clob_content SET clob_column = 'Demonstration of the CLOB data type ' WHERE id = 1; 1 row updated. Command> SELECT * FROM clob_content; < 1, Demonstration of the CLOB data type >
The ROWID
data type is not supported in TimesTen Scaleout. The address of a row in a table or materialized view is called a rowid. The rowid data type is ROWID
. You can examine a rowid by querying the ROWID
pseudocolumn. See "ROWID pseudocolumn" for details on the ROWID
pseudocolumn.
Specify literal ROWID
values in SQL statements as constants enclosed in single quotes, as follows:
Command> SELECT ROWID, last_name FROM employees WHERE department_id = 20; < BMUFVUAAACOAAAALhM, Hartstein > < BMUFVUAAACOAAAAMhM, Fay > 2 rows found. Command> SELECT ROWID, last_name FROM employees WHERE ROWID='BMUFVUAAACOAAAALhM'; < BMUFVUAAACOAAAALhM, Hartstein > 1 row found.
Use the ROWID
data type as follows:
As the data type for a table column or materialized view column
In ORDER BY
and GROUP BY
clauses
In INSERT...SELECT
statements. Column col1
has been defined with the ROWID
data type for these examples:
Command> DESCRIBE master; Table MYUSER.MASTER: Columns: *ID ROWID NOT NULL NAME CHAR (30) 1 table found. (primary key columns are indicated with *) Command> INSERT INTO master(id, name) SELECT ROWID, last_name FROM employees; 107 rows inserted. Command> SELECT * FROM master; < BMUFVUAAACOAAAAGhG, King > < BMUFVUAAACOAAAAHhG, Kochhar > < BMUFVUAAACOAAAAIhG, De Haan > ... 107 rows found.
You can use the TO_CHAR
function with the ROWID
pseudocolumn as shown below:
Command> INSERT INTO master(id, name) SELECT TO_CHAR(ROWID), last_name FROM employees; 107 rows inserted. Command> SELECT * FROM master; < BMUFVUAAACOAAAAGhG, King > < BMUFVUAAACOAAAAHhG, Kochhar > ... 107 rows found.
You can use the CAST
function with the ROWID
pseudocolumn as shown below:
Command> CREATE TABLE master (id CHAR(20) NOT NULL PRIMARY KEY, name CHAR(30)); Command> INSERT INTO master(id, name) SELECT CAST(ROWID AS CHAR(20)), last_name FROM employees; 107 rows inserted.
Implicit type conversions are supported for assigning values and comparison operations between ROWID
and CHAR
or between ROWID
and VARCHAR2
data.
When CHAR
, VARCHAR2
, and ROWID
operands are combined in COALESCE
, DECODE
, NVL
, or CASE
expressions (see "CASE expressions"), the result data type is ROWID
. Expressions with CHAR
and VARCHAR2
values are converted to ROWID
values to evaluate the expression.
To use ROWID
values with string functions such as CONCAT
, the application must convert ROWID
values explicitly to CHAR
values using the SQL TO_CHAR
function.
The datetime data types are as follows:
The format of a DATE
value is YYYY-MM-DD HH:MI:SS
and ranges from -4712-01-01 (January 1, 4712 BC) to 9999-12-31 (December 31, 9999 AD). There are no fractional seconds. The DATE
type requires seven bytes of storage.
TimesTen does not support user-specified NLS_DATE_FORMAT
settings. You can use the SQL TO_CHAR
and TO_DATE
functions to specify other formats.
The format of a TIME
value is HH:MI:SS
and ranges from 00:00:00 (midnight) to 23:59:59 (11:59:59 pm). The TIME
data type requires eight bytes of storage.
The format of a TIMESTAMP
value is YYYY-MM-DD HH:MI:SS [.FFFFFFFFF]
. The fractional seconds precision range is 0 to 9. The default is 6. The date range is from -4712-01-01 (January 1, 4712 BC) to 9999-12-31 (December 31, 9999 AD). The TIMESTAMP
type requires 12 bytes of storage. The TIMESTAMP
type has a larger date range and supports more precision than TT_TIMESTAMP
.
TimesTen does not support user-specified NLS_TIMESTAMP_FORMAT
settings. The SQL TO_CHAR
and TO_DATE
functions can be used to specify other formats.
The format of a TT_DATE
value is YYYY-MM-DD
and ranges from 1753-01-01 (January 1, 1753 AD) to 9999-12-31 (December 31, 9999 AD). The TT_DATE
data type requires four bytes of storage.
The format of a TT_TIMESTAMP
value is YYYY-MM-DD HH:MI:SS [.FFFFFFFFF]
. The fractional seconds precision is 6. The range is from 1753-01-01 00:00:00 (January 1, 1753, midnight) to 9999-12-31 23:59:59 (December 31, 9999, 11:59:59 PM). The TT_TIMESTAMP
type requires eight bytes of storage. TT_TIMESTAMP
is faster than the TIMESTAMP
data type and has a smaller storage size.
This section includes the following topics:
TimesTen supports interval types only in a constant specification or intermediate expression result. Interval types cannot be the final result. Columns cannot be defined with an interval type. See "Type specifications".
You can specify a single-field literal that is an interval in an expression, but you cannot specify a complete expression that returns an interval data type. Instead, the EXTRACT
function must be used to extract the desired component of the interval result.
TimesTen supports interval literals of the following form:
INTERVAL [+/-]
CharString
IntervalQualifier
This section shows some DATE
, TIME
, and TIMESTAMP
data type examples:
To create a table named sample
that contains a column dcol
of type DATE
and a column tcol
of type TIME
, use the following:
CREATE TABLE sample (tcol TIME, dcol DATE);
To insert DATE
and TIME
values into the sample
table, use this:
INSERT INTO sample VALUES (TIME '12:00:00', DATE '1998-10-28');
To select all rows in the sample
table that are between noon and 4:00 p.m. on October 29, 1998, use the following:
SELECT * FROM sample WHERE dcol = DATE '1998-10-29' AND tcol BETWEEN TIME '12:00:00' AND TIME '16:00:00';
To create a table named sample2
that contains a column tscol
of type TIMESTAMP
and then select all rows in the table that are between noon and 4:00 p.m. on October 29, 1998, use these statements:
CREATE TABLE sample2 (tscol TIMESTAMP); INSERT INTO sample2 VALUES (TIMESTAMP '1998-10-28 12:00:00'); SELECT * FROM sample2 WHERE tscol BETWEEN TIMESTAMP '1998-10-29 12:00:00' AND '1998-10-29 16:00:00';
Note:
TimesTen enables both literal and string formats of theTIME
, DATE
, and TIMESTAMP
types. For example, timestring ('12:00:00')
and timeliteral (TIME '16:00:00')
are both valid ways to specify a TIME
value. TimesTen reads the first value as CHAR
type and later converts it to TIME
type as needed. TimesTen reads the second value as TIME
. The examples above use the literal format. Any values for the fraction not specified in full microseconds result in a "Data truncated
" error.TimesTen does not support TIMEZONE
. TIME
and TIMESTAMP
data type values are stored without making any adjustment for time difference. Applications must assume one time zone and convert TIME
and TIMESTAMP
to that time zone before sending values to the database. For example, an application can assume its time zone to be Pacific Standard Time. If the application is using TIME
and TIMESTAMP
values from Pacific Daylight Time or Eastern Standard Time, for example, the application must convert TIME
and TIMESTAMP
to Pacific Standard Time.
You can perform numeric operations on date, timestamp and interval data. TimesTen calculates the results based on the rules:
You can add or subtract a numeric value to or from a DATE
or TIMESTAMP
value. TimesTen internally converts TIMESTAMP
values to DATE
values.
You can add or subtract a numeric value to or from a TT_DATE
or TT_TIMESTAMP
value and the resulting value is TT_DATE
or TT_TIMESTAMP
respectively.
Numeric values are treated as number of days. For example, SYSDATE
+ 1 is tomorrow. SYSDATE
- 7 is one week ago.
Subtracting two date columns results in the number of days between the two dates. The return type is numeric.
You cannot add date values. You cannot multiple or divide date or timestamp values.
Table 1-4 is a matrix of datetime arithmetic operations.
Table 1-4 DateTime arithmetic operations
DATE | TT_DATE | TIMESTAMP | TT_TIMESTAMP | NUMERIC | INTERVAL | |
---|---|---|---|---|---|---|
DATE |
n/a |
n/a |
n/a |
n/a |
n/a |
n/a |
+ (plus) |
unsupported |
unsupported |
unsupported |
unsupported |
DATE |
DATE |
- (minus) |
NUMBER |
NUMBER |
INTERVAL |
INTERVAL |
DATE |
DATE |
* (multiply) |
unsupported |
unsupported |
unsupported |
unsupported |
unsupported |
unsupported |
/ (divide) |
unsupported |
unsupported |
unsupported |
unsupported |
unsupported |
unsupported |
TT_DATE |
n/a |
n/a |
n/a |
n/a |
n/a |
n/a |
+ (plus) |
unsupported |
unsupported |
unsupported |
unsupported |
TT_DATE |
TT_DATE |
- (minus) |
NUMBER |
TT_BIGINT |
INTERVAL |
INTERVAL |
TT_DATE |
TT_DATE |
* (multiply) |
unsupported |
unsupported |
unsupported |
unsupported |
unsupported |
unsupported |
/ (divide) |
unsupported |
unsupported |
unsupported |
unsupported |
unsupported |
unsupported |
TIMESTAMP |
n/a |
n/a |
n/a |
n/a |
n/a |
n/a |
+ (plus) |
unsupported |
unsupported |
unsupported |
unsupported |
DATE |
TIMESTAMP |
- (minus) |
INTERVAL |
INTERVAL |
INTERVAL |
INTERVAL |
DATE |
TIMESTAMP |
* (multiply) |
unsupported |
unsupported |
unsupported |
unsupported |
unsupported |
unsupported |
/ (divide) |
unsupported |
unsupported |
unsupported |
unsupported |
unsupported |
unsupported |
TT_TIMESTAMP |
n/a |
n/a |
n/a |
n/a |
n/a |
n/a |
+ (plus) |
unsupported |
unsupported |
unsupported |
unsupported |
TT_TIMESTAMP |
TT_TIMESTAMP |
- (minus) |
INTERVAL |
INTERVAL |
INTERVAL |
INTERVAL |
TT_TIMESTAMP |
TT_TIMESTAMP |
* (multiply) |
unsupported |
unsupported |
unsupported |
unsupported |
unsupported |
unsupported |
/ (divide) |
unsupported |
unsupported |
unsupported |
unsupported |
unsupported |
unsupported |
NUMERIC |
n/a |
n/a |
n/a |
n/a |
n/a |
n/a |
+ (plus) |
DATE |
TT_DATE |
DATE |
TT_TIMESTAMP |
Not applicable |
unsupported |
- (minus) |
unsupported |
unsupported |
unsupported |
unsupported |
Not applicable |
unsupported |
* (multiply) |
unsupported |
unsupported |
unsupported |
unsupported |
Not applicable |
INTERVAL |
/ (divide) |
unsupported |
unsupported |
unsupported |
unsupported |
Not applicable |
unsupported |
INTERVAL |
n/a |
n/a |
n/a |
n/a |
n/a |
n/a |
+ (plus) |
DATE |
TT_DATE |
TIMESTAMP |
TT_TIMESTAMP |
unsupported |
INTERVAL |
- (minus) |
unsupported |
unsupported |
unsupported |
unsupported |
unsupported |
INTERVAL |
* (multiply) |
unsupported |
unsupported |
unsupported |
unsupported |
INTERVAL |
unsupported |
/ (divide) |
unsupported |
unsupported |
unsupported |
unsupported |
INTERVAL |
unsupported |
Note:
An interval data type cannot be the final result of a complete expression. TheEXTRACT
function must be used to extract the desired component of this interval result.SELECT tt_date1 - tt_date2 FROM t1; SELECT EXTRACT(DAY FROM timestamp1-timestamp2) FROM t1; SELECT * FROM t1 WHERE timestamp1 - timestamp2 = NUMTODSINTERVAL(10, 'DAY'); SELECT SYSDATE + NUMTODSINTERVAL(20,'SECOND') FROM dual; SELECT EXTRACT (SECOND FROM timestamp1-timestamp2) FROM dual; /* select the microsecond difference between two timestamp values d1 and d2 */ SELECT 1000000*(EXTRACT(DAY FROM d1-d2)*24*3600+ EXTRACT(HOUR FROM d1-d2)*3600+ EXTRACT(MINUTE FROM d1-d2)*60+EXTRACT(SECOND FROM d1-d2) FROM d1;
This example inserts TIMESTAMP
values into two columns and then subtracts the two values using the EXTRACT
function:
Command> CREATE TABLE ts (id TIMESTAMP, id2 TIMESTAMP); Command> INSERT INTO ts VALUES (TIMESTAMP '2007-01-20 12:45:23', TIMESTAMP '2006-12-25 17:34:22'); 1 row inserted. Command> SELECT EXTRACT (DAY FROM id - id2) FROM ts; < 25 > 1 row found.
The following queries return errors. You cannot select an interval result:
SELECT timestamp1 - timestamp2 FROM t1;
You cannot compare an INTERVAL YEAR TO MONTH
with an INTERVAL DAY TO SECOND
:
SELECT * FROM t1 WHERE timestamp1 - timestamp2 = NUMTOYMINTERVAL(10, 'YEAR');
You cannot compare an INTERVAL DAY TO SECOND
with an INTERVAL DAY
:
SELECT * FROM t1 WHERE timestamp1 - timestamp2 = INTERVAL '10' DAY;
You cannot extract YEAR
from an INTERVAL DAY TO SECOND
:
SELECT EXTRACT (YEAR FROM timestamp1 - timestamp2) FROM dual;
Consider these restrictions when performing datetime and interval arithmetic:
The results for addition and subtraction with DATE
and TIMESTAMP
types for INTERVAL YEAR
and INTERVAL MONTH
are not closed. For example, adding one year to the DATE
or TIMESTAMP
of '2004-02-29' results in a date arithmetic error (TimesTen error 2787) because February 29, 2005 does not exist (2005 is not a leap year). Adding INTERVAL '1'
month to DATE '2005-01-30'
also results in the same error because February never has 30 days.
The results are closed for INTERVAL DAY
.
An interval data type cannot be the final result of a complete expression. The EXTRACT
function must be used to extract the desired component of the interval result.
Variable-length columns whose declared column length is greater than 128 bytes are stored out of line. Variable-length columns whose declared column length is less than or equal to 128 bytes are stored inline. All LOB data types are stored out of line.
For character semantics, the number of bytes stored out of line depends on the character set. For example, for a character set with four bytes per character, variable-length columns whose declared column length is greater than 32 (128/4) are stored out of line.
Table 1-5 shows the storage requirements of the various data types.
Table 1-5 Data type storage requirements
Type | Storage required |
---|---|
|
|
Eight bytes |
|
Four bytes |
|
If character semantics, the length of the column ( |
|
Seven bytes |
|
An interval type cannot be stored in TimesTen |
|
Bytes required is 2* |
|
Five to 22 bytes |
|
For 2*(length of value) + 24 bytes (minimum of 40 bytes). For 2*(length of column) + 8 bytes. |
|
Twelve bytes |
|
Twelve bytes |
|
Eight bytes |
|
Four bytes |
|
Four bytes |
|
Two bytes |
|
Eight bytes |
|
Eight bytes |
|
One byte |
|
For Length of value + 24 bytes (minimum of 40 bytes). For Length of column + 8 bytes. |
|
For Length of value + 24 bytes (minimum of 40 bytes). This storage principal holds for all variable length For n + 8 bytes. If character semantics, the length of the column ( |
|
Length of value + 48 bytes (minimum of 56 bytes) |
|
2 * (length of value) + 48 bytes (minimum of 56 bytes) |
This section describes how values of each data type are compared in TimesTen.
A larger value is greater than a smaller value: -1 is less than 10, and -10 is less than -1.
The floating-point value NaN
is greater than any other numeric value and is equal to itself.
A later date is considered greater than an earlier one. For example, the date equivalent of '10-AUG-2005' is less than that of '30-AUG-2006', and '30-AUG-2006 1:15 pm' is greater than '30-AUG-2006 10:10 am'.
Character values are compared in the following ways:
In binary sorting, TimesTen compares character strings according to the concatenated value of the numeric codes of the characters in the database character set. One character is greater than the other if it has a greater numeric values than the other in the character set. Blanks are less than any character.
Linguistic sorting is useful if the binary sequence of numeric codes does not match the linguistic sequence of the characters you are comparing. In linguistic sorting, SQL sorting and comparison are based on the linguistic rule set by NLS_SORT
. For more information on linguistic sorts, see "Linguistic sorts" in Oracle TimesTen In-Memory Database Operations Guide.
The default is binary sorting.
With blank-padded semantics, if two values have different lengths, TimesTen adds blanks to the shorter value until both lengths are equal. Values are then compared character by character up to the first character that differs. The value with the greater character in the first differing position is considered greater. If two values have no differing characters, then they are considered equal. Thus, two values are considered equal if they differ only in the number of trailing blanks.
Blank-padded semantics are used when both values in the comparison are expressions of type CHAR
or NCHAR
or text literals.
With nonpadded semantics, two values are compared, character by character, up to the first character that differs. The value with the greater character in that position is considered greater. If two values that have differing lengths are identical up to the end of the shorter one, then the longer one is considered greater. If two values of equal length have no differing characters, they are considered equal.
Nonpadded semantics are used when both values in the comparison have the type VARCHAR2
or NVARCHAR2
.
An example with blank-padded semantics:
'a ' = 'a'
An example with nonpadded semantics:
'a ' > 'a'
Generally an expression cannot contain values of different data types. However, TimesTen supports both implicit and explicit conversion from one data type to another. Because algorithms for implicit conversion are subject to change across software releases and the behavior of explicit conversions is more predictable, TimesTen recommends explicit conversion.
TimesTen converts a value from one data type to another when such a conversion is sensible.
Table 1-6 and Table 1-7 use a matrix to illustrate TimesTen implicit data type conversions. YES
in the cell indicates the conversion is supported. NO
in the cell indicates the conversion is not supported. The rules for implicit conversion follow the table.
Table 1-6 Implicit data type conversion
CHAR | VARCHAR2 | NCHAR | NVARCHAR2 | DATE | TT_DATE | TIMESTAMP | TT_TIMESTAMP | |
---|---|---|---|---|---|---|---|---|
CHAR |
n/a |
YES |
YES |
YES |
YES |
YES |
YES |
YES |
VARCHAR2 |
YES |
n/a |
YES |
YES |
YES |
YES |
YES |
YES |
NCHAR |
YES |
YES |
n/a |
YES |
YES |
YES |
YES |
YES |
NVARCHAR2 |
YES |
YES |
YES |
n/a |
YES |
YES |
YES |
YES |
DATE |
YES |
YES |
YES |
YES |
n/a |
YES |
YES |
YES |
TT_DATE |
YES |
YES |
YES |
YES |
YES |
n/a |
YES |
YES |
TIMESTAMP |
YES |
YES |
YES |
YES |
YES |
YES |
n/a |
YES |
TT_TIMESTAMP |
YES |
YES |
YES |
YES |
YES |
YES |
YES |
n/a |
NUMERIC |
YES |
YES |
YES |
YES |
NO |
NO |
NO |
NO |
BLOB |
NO |
NO |
NO |
NO |
NO |
NO |
NO |
NO |
CLOB |
YES |
YES |
YES |
YES |
NO |
NO |
NO |
NO |
NCLOB |
YES |
YES |
YES |
YES |
NO |
NO |
NO |
NO |
BINARY/ VARBINARY |
YES |
YES |
YES |
YES |
NO |
NO |
NO |
NO |
ROWID |
YES |
YES |
YES |
YES |
NO |
NO |
NO |
NO |
Table 1-7 Implicit data type conversion (continuation of preceding table)
NUMERIC | BLOB | CLOB | NCLOB | BINARY/ VARBINARY | ROWID | |
---|---|---|---|---|---|---|
CHAR |
YES |
YES |
YES |
YES |
YES |
YES |
VARCHAR2 |
YES |
YES |
YES |
YES |
YES |
YES |
NCHAR |
YES |
YES |
YES |
YES |
YES |
YES |
NVARCHAR2 |
YES |
YES |
YES |
YES |
YES |
YES |
DATE |
NO |
NO |
NO |
NO |
NO |
NO |
TT_DATE |
NO |
NO |
NO |
NO |
NO |
NO |
TIMESTAMP |
NO |
NO |
NO |
NO |
NO |
NO |
TT_TIMESTAMP |
NO |
NO |
NO |
NO |
NO |
NO |
NUMERIC |
n/a |
NO |
NO |
NO |
NO |
NO |
BLOB |
NO |
n/a |
NO |
NO |
YES |
NO |
CLOB |
NO |
NO |
n/a |
YES |
NO |
NO |
NCLOB |
NO |
NO |
YES |
n/a |
NO |
NO |
BINARY/ VARBINARY |
NO |
YES |
YES |
YES |
n/a |
NO |
ROWID |
NO |
NO |
NO |
NO |
NO |
n/a |
The following rules apply:
During arithmetic operations on and comparisons between character and non-character data types, TimesTen converts from any character data type to a numeric or datetime data type as appropriate. In arithmetic operations between CHAR
/VARCHAR2
and NCHAR
/NVARCHAR2
, TimesTen converts to a NUMBER
.
During arithmetic operations, floating point values INF
and NAN
are not supported when converting character values to numeric values.
During concatenation operations, TimesTen converts non-character data types to CHAR
, NCHAR
, VARCHAR2
, or NVARCHAR2
depending on the other operand.
When comparing a character value with a numeric value, TimesTen converts the character data to a numeric value.
When comparing a character value with a datetime value, TimesTen converts the character data to a datetime value.
During conversion from a timestamp value to a DATE
value, the fractional seconds portion of the timestamp value is truncated.
Conversions from BINARY_FLOAT
to BINARY_DOUBLE
are exact.
Conversions from BINARY_DOUBLE
to BINARY_FLOAT
are inexact if the BINARY_DOUBLE
value uses more bits of precision that supported by the BINARY_FLOAT
.
Conversions between either character values or exact numeric values (TT_TINYINT
, TT_SMALLINT
, TT_INTEGER
, TT_BIGINT
, NUMBER
) and floating-point values (BINARY_FLOAT
, BINARY_DOUBLE
) can be inexact because the character values and the exact numeric values use decimal precision whereas the floating-point numbers use binary precision.
When manipulating numeric values, TimesTen usually adjusts precision and scale to allow for maximum capacity. In such cases, the numeric data type resulting from such operations can differ from the numeric data type found in the underlying tables.
When making assignments, TimesTen converts the value on the right side of the equal sign (=) to the data type of the target of the assignment on the left side.
When you use a SQL function or operator with an argument of a data type other than the one it accepts, TimesTen converts the argument to the accepted data type as long as TimesTen supports the implicit conversion.
During INSERT
, INSERT... SELECT
, and UPDATE
operations, TimesTen converts the value to the data type of the affected column.
Implicit and explicit CHAR
/VARCHAR2
<-> NCHAR
/NVARCHAR
2 conversions are supported. An example of implicit conversion:
Command> CREATE TABLE convdemo (c1 CHAR (10), x1 TT_INTEGER); Command> CREATE TABLE convdemo2 (c1 NCHAR (10), x2 TT_INTEGER); Command> INSERT INTO convdemo VALUES ('ABC', 10); 1 row inserted. Command> INSERT INTO convdemo VALUES ('def', 100); 1 row inserted. Command> INSERT INTO convdemo2 SELECT * FROM convdemo; 2 rows inserted. Command> SELECT x1,x2,convdemo.c1, convdemo2.c1 FROM convdemo, convdemo2 WHERE Convdemo.c1 = convdemo2.c1; X1, X2, C1, C1 < 10, 10, ABC , ABC > < 100, 100, def , def > 2 rows found.
The value NULL
indicates the absence of a value. It is a placeholder for a value that is missing. Use a NULL
when the actual value is not known or when a value would not be meaningful. Do not use NULL
to represent a numeric value of zero, because they are not equivalent. Any parameter in an expression can contain NULL
regardless of its data type. In addition, any column in a table can contain NULL
, regardless of its data type, unless you specify NOT NULL
or PRIMARY KEY
integrity constraints for the column when you create the table.
The following properties of NULL
affect operations on rows, parameters, or local variables:
By default, NULL
is sorted as the highest value in a sequence of values. However, you can modify the sort order value for NULL
with NULLS FIRST
or NULLS LAST
in the ORDER BY
clause.
Two NULL
values are not equal to each other except in a GROUP BY
or SELECT DISTINCT
operation.
An arithmetic expression containing a NULL
evaluates to NULL
. In fact, all operators (except concatenation) return NULL
when given a NULL
operand. For example, (5-col)
, where col
is NULL
, evaluates to NULL
.
To test for NULL
, use the comparison conditions IS NULL
or IS NOT NULL
. Because NULL
represents a lack of data, a NULL
cannot be equal or unequal to any value or to another NULL
. Thus, the statement select * from employees where mgr_id = NULL
evaluates to 0, since you cannot use this comparison to NULL
. However, the statement select * from employees where mgr_id is NULL
provides the CEO of the company, since that is the only employee without a manager. For details, see "IS NULL predicate".
You can use the NULL
value itself directly as an operand of an operator or predicate. For example, the (1 = NULL)
comparison is supported. This is the same as if you cast NULL
to the appropriate data type, as follows: (1 = CAST(NULL AS INT))
. Both methods are supported and return the same results.
Because of these properties, TimesTen ignores columns, rows, or parameters containing NULL
when:
Joining tables if the join is on a column containing NULL
.
Executing aggregate functions.
In several SQL predicates, you can explicitly test for NULL
. APIs supported by TimesTen offer ways to handle null values. For example, in an ODBC application, use the functions SQLBindCol
, SQLBindParameter
, SQLGetData
, and SQLParamData
to handle input and output of NULL
values.
TimesTen supports the IEEE floating-point values Inf
(positive infinity), -Inf
(negative infinity), and NaN
(not a number).
You can use constant values in places where a floating-point constant is allowed. The following constants are supported:
BINARY_FLOAT_INFINITY
-BINARY_FLOAT_INFINITY
BINARY_DOUBLE_INFINITY
-BINARY_DOUBLE_INFINITY
BINARY_FLOAT_NAN
BINARY_DOUBLE_NAN
In the following example, a table is created with a column of type BINARY_FLOAT
and a column of type TT_INTEGER
. BINARY_FLOAT_INFINITY
and BINARY_FLOAT_NAN
are inserted into the column of type BINARY_FLOAT
.
Command> CREATE TABLE bfdemo (id BINARY_FLOAT, Ii2 TT_INTEGER); Command> INSERT INTO bfdemo VALUES (BINARY_FLOAT_INFINITY, 50); 1 row inserted. Command> INSERT INTO bfdemo VALUES (BINARY_FLOAT_NAN, 100); 1 row inserted. Command> SELECT * FROM bfdemo; < INF, 50 > < NAN, 100 > 2 rows found.
Inf
, -Inf
, and NaN
are acceptable values in columns defined with a primary key. This is different from NULL
, which is not allowed in columns defined with a primary key.
You can only insert Inf
, -Inf
, and NaN
values into BINARY_FLOAT
and BINARY_DOUBLE
columns.
Floating-point conditions determine whether an expression is infinite or is the undefined result of an operation (NaN
, meaning not a number).
Consider the following syntax:
Expression IS [NOT] {NAN|INFINITE}
Expression
must either resolve to a numeric data type or to a data type that can be implicitly converted to a numeric data type.
The following table describes the floating-point conditions.
Condition | Operation | Example |
---|---|---|
IS [NOT] NAN |
Returns TRUE if Expression is the value NaN when NOT is not specified. Returns TRUE if Expression is not the value NaN when NOT is specified. |
SELECT * FROM bfdemo WHERE id IS NOT NAN; ID, ID2 < INF, 50 > 1 row found. |
IS [NOT] INFINITE |
Returns TRUE if Expression is the value +Inf or -Inf when NOT is not specified. Returns TRUE if Expression is neither +Inf nor -Inf when NOT is specified. |
SELECT * FROM bfdemo WHERE id IS NOT INFINITE; ID, ID2 < NAN, 100 > 1 row found. |
Note:
The constant keywords represent specificBINARY_FLOAT
and BINARY_DOUBLE
values. The comparison keywords correspond to properties of a value and are not specific to any type, although they can only evaluate to TRUE
for BINARY_FLOAT
or BINARY_DOUBLE
types or types that can be converted to BINARY_FLOAT
or BINARY_DOUBLE
.The following rules apply to comparisons with Inf
and NaN
:
Comparison between Inf
(or -Inf
) and a finite value are as expected. For example, 5 > -Inf
.
(Inf = Inf)
and (Inf > -Inf)
both evaluate to TRUE
.
(NaN = NaN)
evaluates to TRUE
.
In reference to collating sequences:
-Inf
sorts lower than any other value.
Inf
sorts lower than NaN
and NULL
and higher than any other value.
NaN
sorts higher than Inf
.
NULL
sorts higher than NaN
. NULL
is always the largest value in any collating sequence.
Expressions containing floating-point values may generate Inf
, -Inf
, or NaN
. This can occur either because the expression generated overflow or exceptional conditions or because one or more of the values in the expression was Inf
, -Inf
, or NaN
. Inf
and NaN
are generated in overflow or division-by-zero conditions.
Inf
, -Inf
, and NaN
values are not ignored in aggregate functions. NULL
values are. If you want to exclude Inf
and NaN
from aggregates, or from any SELECT
result, use both the IS NOT NAN
and IS NOT INFINITE
predicates.
Some operations can result in data overflow or truncation. Overflow results in an error and can generate Inf
. Truncation results in loss of least significant data.
Exact values are truncated only when they are stored in the database by an INSERT
or UPDATE
statement, and if the target column has smaller scale than the value. TimesTen returns a warning when such truncation occurs. If the value does not fit because of overflow, TimesTen returns the special value Inf
and does not insert the specified value.
TimesTen may truncate approximate values during computations, when values are inserted into the database, or when database values are updated. TimesTen returns an error only upon INSERT
or UPDATE
. When overflow with approximate values occurs, TimesTen returns the special value Inf
.
There are several circumstances that can cause overflow:
During arithmetic operations, overflow can occur when multiplication results in a number larger than the maximum value allowed in its type. See Chapter 3, "Expressions" for more information.
When aggregate functions are used, overflow can occur when the sum of several numbers exceeds the maximum allowable value of the result type.
During type conversion, overflow can also occur when, for example, a TT_INTEGER
value is converted to a TT_SMALLINT
value.
Truncation can cause an error or warning for alphanumeric or numeric data types, as follows.
For character data, an error occurs if a string is truncated because it is too long for its target type. For NCHAR
and NVARCHAR2
types, truncation always occurs on Unicode character boundaries. In the NCHAR
data types, a single-byte value (half a Unicode character) has no meaning and is not possible.
For numeric data, a warning occurs when any trailing nonzero digit is dropped from the fractional part of a numeric value.