Skip Headers
Oracle® TimesTen In-Memory Database SQL Reference
Release 11.2.1

Part Number E13070-07
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

1 Data Types

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. A discussion of this standard is not included in this guide. See Appendix D either in the Microsoft ODBC 2.0 Programmer's Reference and SDK Guide or the Microsoft ODBC 3.0 Developer's Kit and Programmer's Reference for more information.

If you are using IMDB Cache, see "Mappings between Oracle and TimesTen data types" in Oracle In-Memory Database Cache User's Guide. This section compares valid data types for creating cache group columns, as well as type conversions for passthrough queries.

Type specifications

TimesTen supports the data types in Table 1-1 in the default Oracle type mode. The type mode is a data store creation attribute. TypeMode=0 indicates Oracle type mode. TypeMode=1 indicates TimesTen mode.

For more information on types modes, see "TypeMode" in Oracle TimesTen In-Memory Database Reference.

Table 1-1 Data types supported in Oracle type mode

Data type Description

BINARY (n)

Fixed-length binary value of n bytes. Legal values for n range from 1 to 8300.

BINARY data is padded to the maximum column size with trailing zeroes.

Alternatively, specify TT_BINARY (n).

BINARY_DOUBLE

64-bit floating-point number. BINARY_DOUBLE is a double-precision native floating point number. Supports +Inf,

-Inf and Nan values. BINARY_DOUBLE is an approximate numeric value consisting of an exponent and mantissa. You can use Exponential or E-notation. BINARY_DOUBLE has binary precision 53.

Minimum positive finite value: 2.22507485850720E-308

Maximum positive finite value: 1.79769313486231E+308

BINARY_FLOAT

32-bit floating-point number. BINARY_FLOAT is a single-precision native floating-point type. Supports +Inf, -Inf and NaN values. BINARY_FLOAT is an approximate numeric value consisting of an exponent and mantissa. You can use Exponential or E-notation. BINARY_FLOAT has binary precision 24.

Minimum positive finite value: 1.17549E-38F

Maximum positive finite value: 3.40282E+38F

CHAR[ACTER] [(n [BYTE|CHAR])]

Fixed-length character string of length n bytes or characters. Default is 1 byte.

BYTE indicates that the column has byte length semantics. Legal values for n range from a minimum of 1 byte to a maximum 8300 bytes.

CHAR indicates that the column has character length semantics. The minimum CHAR length is 1 character. The maximum CHAR length depends on how many characters fit in 8300 bytes. This is determined by the database character set in use. For character set AL32UTF8, up to four bytes per character may be needed, so the CHAR length limit ranges from 2075 to 8300 depending on the character set.

A zero-length string is interpreted as NULL.

CHAR data is padded to the maximum column size with trailing blanks. Blank-padded comparison semantics are used.

Alternatively, specify ORA_CHAR[(n [BYTE|CHAR])].

DATE

Stores date and time information: century, year, month, date, hour, minute and second. Format is:

YYYY-MM-DD HHMMSS.

Valid date range is from January 1, 4712 BC to December 31, 9999 AD.

There are no fractional seconds.

Alternatively, specify ORA_DATE.

INTERVAL [+/-] IntervalQualifier

TimesTen partially supports INTERVAL types, expressed with the type INTERVAL and an IntervalQualifier. An IntervalQualifier can only specify a single field type with no precision. The default leading precision is 8 digits for all INTERVAL types. The single field type can be: YEAR, MONTH, DAY, HOUR, MINUTE or SECOND. Currently, INTERVAL type can be specified only with a constant.

Note: You cannot specify a column of type INTERVAL. This is a non-persistent type used in SQL expressions at runtime.

NCHAR[(n)]

Fixed-length string of length n two-byte Unicode characters.

The number of bytes required is 2* n where n is the specified number of characters. NCHAR character limits are 1/2 the byte limits so the maximum size is 4150.

A zero-length string is interpreted as NULL.

NCHAR data is padded to the maximum column size with U+0020 SPACE. Blank-padded comparison semantics are used.

Alternatively, specify ORA_NCHAR[(n)].

NUMBER [(precision [,scale])]

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.

NUMBER supports scale > precision and negative scale.

NUMBER stores zero as well as positive and negative fixed numbers with absolute values from 1.0 x 10-130 to (but not including) 1.0 x 10126. If you specify an arithmetic expression whose value has an absolute value greater than or equal to 1.0 x 10126, then TimesTen returns an error.

NVARCHAR2(n)

Variable-length string of n two-byte Unicode characters.

The number of bytes required is 2* n where n is the specified number of characters. NVARCHAR2 character limits are 1/2 the byte limits so the maximum size is 2,097,152 (221). You must specify n.

A zero-length string is interpreted as NULL.

Nonpadded comparison semantics are used.

Alternatively, specify ORA_NVARCHAR2(n).

ROWID

An 18-byte character string that represents the address of a table row or materialized view row.

Specify a literal ROWID value as a CHAR constant enclosed in single quotes.

TIME

A time of day between 00:00:00 (12 midnight) and 23:59:59 (11:59:59 pm), inclusive. The format is: HH:MI:SS.

Alternatively, specify TT_TIME.

TIMESTAMP

[(fractional_seconds_precision)]

Stores year, month, and day values of the date data type plus hour, minute, and second values of time. Fractional_seconds_precision is the number of digits in the fractional part of the seconds field. Valid date range is from January 1, 4712 BC to December 31, 9999 AD.

TT_TIMESTAMP has a smaller storage size than TIMESTAMP. TT_TIMESTAMP is faster than TIMESTAMP because TT_TIMESTAMP is an 8 byte integer containing the number of microseconds since January 1, 1754. Comparisons are very fast. TIMESTAMP has a larger range than TT_TIMESTAMP in that TIMESTAMP can store datetime data as far back as 4712 BC. TIMESTAMP also supports up to 9 digits of fractional second precision whereas TT_TIMESTAMP supports 6 digits of fractional second precision.

The fractional seconds precision range is 0 to 9. The default is 6. Format is:

YYYY-MM-DD HH:MI:SS [.FFFFFFFFF]

Alternatively, specify ORA_TIMESTAMP[(fractional_seconds_precision)]

TT_BIGINT

A signed 8-byte integer in the range

-9,223,372,036,854,775,808 -(263) to

9,223,372,036,854,775,807 (263 - 1).

Use TT_BIGINT rather than the NUMBER data type. TT_BIGINT is more compact and offers faster performance than the NUMBER type. If you need to store greater than 19 digit integers, use NUMBER (p) where p > 19.

TT_DATE

Stores date information: century, year, month, date. The format is YYYY-MM-DD. MM is expressed as an integer. For example:

2006-10-28

Valid dates are between 1753-01-01 (January 1,1753) and 9999-12-31 (December 31, 9999).

TT_INT[EGER]

A signed integer in the range –2,147,483,648 --(231) to 2,147,483,647 (231–1).

TT_INTEGER is a native signed integer data type. Use TT_INTEGER rather than INTEGER. INTEGER maps to the NUMBER data type. TT_INTEGER is more compact and offers faster performance than the NUMBER type. If you need to store greater than 19 digit integers, use NUMBER (p) where p > 19.

TT_SMALLINT

A native signed 16 bit integer in the range –32,768 –(215) to 32,767 (215–1).

Use TT_SMALLINT rather than SMALLINT. SMALLINT maps to the NUMBER data type.

TT_SMALLINT is more compact and offers faster performance than the NUMBER type. If you need to store greater than 19 digit integers, use NUMBER (p) where p > 19.

TT_TIMESTAMP

A data and time between 1753-01-01 00:00:00 (January 1, 1753 midnight) 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 "Data Truncated" error. The format is YYYY-MM-DD HH:MI:SS [.FFFFFF].

TT_TIMESTAMP has a smaller storage size than TIMESTAMP and TT_TIMESTAMP is faster than TIMESTAMP because TT_TIMESTAMP is an 8 byte integer containing the number of microseconds since January 1, 1754. Comparisons are very fast. TIMESTAMP has a larger range than TT_TIMESTAMP in that TIMESTAMP can store datetime data as far back as 4712 BC. TIMESTAMP also supports up to 9 digits of fractional second precision whereas TT_TIMESTAMP supports 6 digits of fractional second precision.

You can specify TT_TIMESTAMP (6).

TT_TINYINT

Unsigned integer ranging from 0 to 255 (28-1).

Use TT_TINYINT rather than the NUMBER data type. TT_TINYINT is more compact and offers faster performance than the NUMBER type. If you need to store greater than 19 digit integers, use NUMBER (p) where p > 19.

Since TT_TINYINT is unsigned, the negation of a TT_TINYINT is TT_SMALLINT.

VARBINARY (n)

Variable-length binary value having maximum length n bytes. Legal values for n range from 1 to 4194304 (222).

Alternatively, specify TT_VARBINARY(n).

VARCHAR[2] (n [BYTE|CHAR])

Variable-length character string having maximum length n bytes or characters.

BYTE indicates that the column has byte length semantics.Legal values for n range from a minimum of 1 byte to a maximum 4194304 (222) bytes. You must specify n.

CHAR indicates that the column has character length semantics.

A zero-length string is interpreted as NULL.

Nonpadded comparison semantics are used.

Do not use the VARCHAR type. Although it is currently synonymous with VARCHAR2, the VARCHAR type is scheduled to be redefined.

Alternatively, specifyORA_VARCHAR2 (n [BYTE|CHAR]).


ANSI SQL data types

TimesTen supports ANSI SQL data types in Oracle type mode. These data types are converted to TimesTen data types and the data is 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 VARYING (n [BYTE|CHAR]) or CHAR VARYING(n[BYTE|CHAR])

VARCHAR2 (n [BYTE|CHAR])

Character semantics is supported.

DOUBLE [PRECISION]

NUMBER

Floating- point number with a binary precision of 126.

Alternatively, specify FLOAT (126) or ORA_FLOAT (126).

FLOAT [(b)]

NUMBER

Floating-point number with binary precision b. Acceptable values for b are between 1 and 126 binary digits.

FLOAT is an exact numeric type. Use FLOAT to define a column with a floated scale and a specified precision. A floated scale is supported with the NUMBER type, but you cannot specify the precision. A lower precision requires less space, so because you can specify a precision with FLOAT, it may be more desirable than NUMBER. If you do not specify b, then the default precision is 126 binary (38 decimal).

BINARY_FLOAT and BINARY_DOUBLE are inexact numeric types and are therefore different floating types than FLOAT. In addition, the semantics are different between FLOAT and BINARY_FLOAT/BINARY_DOUBLE because BINARY_FLOAT and BINARY_DOUBLE conform to the IEEE standard.

Internally, FLOAT is implemented as type NUMBER.

Alternatively, specify ORA_FLOAT. For example:

FLOAT (24) = ORA_FLOAT (24)

FLOAT (53) = ORA_FLOAT (53)

FLOAT (n) = ORA_FLOAT (n)

INT[EGER]

NUMBER (38,0)

TT_INTEGER is a native 32 bit integer type. Use TT_INTEGER as this data type is more compact and offers faster performance than the NUMBER type.

NATIONAL CHARACTER (n) or

NATIONAL CHAR (n)

NCHAR (n)

NATIONAL CHARACTER VARYING (n) or

NATIONAL CHAR VARYING (n) or

NCHAR VARYING (n)

NVARCHAR2 (n)

NUMERIC [(p[,s])] or

DEC[IMAL] [(p[,s])]

NUMBER (p,s)

Specifies a fixed-point number with precision p and scale s. Can only be used for fixed-point numbers. If no scale is specified, s defaults to 0.

REAL

NUMBER

Floating -point number with a binary precision of 63.

Alternatively, specify

ORA_FLOAT (63) or FLOAT (63).

SMALLINT

NUMBER (38,0)

TT_SMALLINT is a native signed integer data type. Use TT_SMALLINT as this data type is more compact and offers faster performance than the NUMBER type.


Types supported for backward compatibility in Oracle type mode

TimesTen supports the data types shown in Table 1-3 for backward compatibility in Oracle type mode.

Table 1-3 Data types supported for backward compatibility in Oracle type mode

Data type Description

TT_CHAR [(n [BYTE|CHAR])]

Fixed-length character string of length n bytes or characters. Default is 1 byte.

BYTE indicates that the column has byte length semantics. Legal values for n range from a minimum of 1 byte to a maximum 8300 bytes.

CHAR indicates that the column has character length semantics. The minimum CHAR length is 1 character. The maximum CHAR length depends on how many characters fit in 8300 bytes. This is determined by the database character set in use. For character set AL32UTF8, up to four bytes per character may be needed, so the CHAR length limit ranges from 2075 to 8300 depending on the character set.

If you insert a zero-length (empty) string into a column, the SQL NULL value is inserted. This is true in Oracle type mode only.

TT_CHAR data is padded to the maximum column size with trailing blanks. Blank-padded comparison semantics are used.

TT_DECIMAL[(p[,s])]

An exact numeric value with a fixed maximum precision (total number of digits) and scale (number of digits to the right of the decimal point). The precision p must be between 1 and 40. The scale must be between 0 and p. The default precision is 40 and the default scale is 0.

Use the NUMBER data type rather than TT_DECIMAL. NUMBER offers better performance.

TT_NCHAR[(n)]

Fixed-length string of n two-byte Unicode characters.

The number of bytes required is 2* n where n is the specified number of characters. NCHAR character limits are 1/2 the byte limits so the maximum size is 4150.

If you insert a zero-length (empty) string into a column, the SQL NULL value is inserted. This is true in Oracle type mode only.

TT_NCHAR data is padded to the maximum column size with U+0020 SPACE. Blank-padded comparison semantics are used.

TT_NVARCHAR(n)

Variable-length string of n two-byte Unicode characters.

The number of bytes required is 2* n where n is the specified number of characters. TT_NVARCHAR character limits are 1/2 the byte limits so the maximum size is 2,097,152 (221). You must specify n.

If you insert a zero-length (empty) string into a column, the SQL NULL value is inserted. This is true in Oracle type mode only.

Blank-padded comparison semantics are used.

TT_VARCHAR (n [BYTE|CHAR])

Variable-length character string having maximum length n bytes or characters. You must specify n.

BYTE indicates that the column has byte length semantics. Legal values for n range from a minimum of 1 byte to a maximum 4194304 (222) bytes.

CHAR indicates that the column has character length semantics.

If you insert a zero-length (empty) string into a column, the SQL NULL value is inserted. This is true in Oracle type mode only.

Blank-padded comparison semantics are used.


TimesTen type mapping

The names of the data types listed in the left column of Table 1-4 are the data types that existed in previous releases of TimesTen. If TypeMode is set to 0 (the default), indicating Oracle type mode, then the name of the data type may be changed to a new name in Oracle type mode. (The name of the data type in Oracle type mode is listed in the right column.) The table illustrates the mapping of the data type in the left column to the corresponding data type in the right column.

Table 1-4 Data type mapping: TimesTen data type to TimesTen data type in Oracle type mode

TimesTen data type TimesTen data type in Oracle type mode

BIGINT

TT_BIGINT

In Oracle type mode, specify TT_BIGINT. For more information on TT_BIGINT, see "Type specifications".

BINARY (n)

BINARY (n)

In Oracle type mode, the data type has the same name. For more information on BINARY (n), see "Type specifications".

CHAR[ACTER][(n)]

TT_CHAR [(n [BYTE|CHAR])]

In Oracle type mode, specify TT_CHAR. Character semantics is supported. For more information on type TT_CHAR, see "Types supported for backward compatibility in Oracle type mode".

DATE

TT_DATE

In Oracle type mode, specify TT_DATE. For more information on TT_DATE, see "Type specifications".

DEC[IMAL][(p[,s])] or

NUMERIC[(p[,s])]

TT_DECIMAL[(p[,s])]

In Oracle type mode, specify TT_DECIMAL.

For more information on TT_DECIMAL, see "Types supported for backward compatibility in Oracle type mode".

DOUBLE [PRECISION] or

FLOAT [(53)]

BINARY_DOUBLE

In Oracle type mode, specify BINARY_DOUBLE. For more information on BINARY_DOUBLE, see "Type specifications".

INT[EGER]

TT_INT[EGER]

In Oracle type mode, specify TT_INTEGER. For more information on TT_INTEGER, see "Type specifications".

INTERVAL IntervalQualifier

INTERVAL IntervalQualifier

In Oracle type mode, the data type has the same name. For more information on INTERVAL, see "Type specifications".

NCHAR [(n)]

TT_NCHAR[(n)]

In Oracle type mode, specify TT_CHAR. For more information on TT_NCHAR, see "Types supported for backward compatibility in Oracle type mode".

NVARCHAR (n)

TT_NVARCHAR(n)

In Oracle type mode, specify TT_NVARCHAR. For more information on TT_NVARCHAR, see "Types supported for backward compatibility in Oracle type mode".

REAL or

FLOAT (24)

BINARY_FLOAT

In Oracle type mode, specify BINARY_FLOAT. For more information on BINARY_FLOAT, see "Type specifications".

SMALLINT

TT_SMALLINT

In Oracle type mode, specify TT_SMALLINT. For more information on TT_SMALLINT, see "Type specifications".

TIME

TIME

In Oracle type mode, the data type has the same name. For more information on TIME, see "Type specifications".

TIMESTAMP

TT_TIMESTAMP

In Oracle type mode, specify TT_TIMESTAMP. For more information on TT_TIMESTAMP, see "Type specifications".

TINYINT

TT_TINYINT

In Oracle type mode, specify TT_TINYINT. For more information on TT_TINYINT, see "Type specifications".

VARBINARY (n)

VARBINARY (n)

In Oracle type mode, the data type has the same name. For more information on VARBINARY (n), see "Type specifications".

VARCHAR (n)

TT_VARCHAR (n [BYTE|CHAR])

In Oracle type mode, specify TT_VARCHAR. Character semantics is supported. For more information on TT_VARCHAR, see "Types supported for backward compatibility in Oracle type mode".


Character data types

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 data store character sets defined when the data store is created. TimesTen supports both single byte and multibyte character sets.

The character types are:

CHAR

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, then 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 1 byte to 4 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" and "Setting globalization support attributes" in Oracle TimesTen In-Memory Database Operations Guide.

Note:

With the CHAR 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.

NCHAR

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 the NCHAR 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 following example ALTERs table typedemo adding 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.

VARCHAR2

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 1 byte. Use the CHAR qualifier to specify the maximum length in characters. For example, VARCHAR2 (10 CHAR).

The size of a character ranges from 1 byte to 4 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" and "Setting globalization support attributes" in Oracle TimesTen In-Memory Database Operations Guide.

The NULL value is stored as a single bit inside the tuple for each nullable field. A NOT INLINE VARCHAR2(n) whose value is NULL takes (null bit) + 4 bytes of storage on 32-bit platforms, whereas an INLINE VARCHAR2(n) whose value is NULL takes (null bit) + 4 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.

Note:

Do not use the VARCHAR data type. Use VARCHAR2. Even though both data types are currently synonymous, the VARCHAR data type is scheduled to be redefined as a different data type with different semantics.

Note:

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. name5 is defined with type VARCHAR2. name6 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.

NVARCHAR2

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. You must specify a length.

Note:

With the NVARCHAR2 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 following example alters table typedemo adding 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 data types

Numeric types store positive and negative fixed and floating-point numbers, zero, infinity, and values that are the undefined result of an operation (NaN or "not a number").

Exact and approximate numeric data types

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.

The exact numeric types are:

The approximate types are:

NUMBER

The NUMBER data type stores zero as well as positive and negative fixed numbers with absolute values from 1.0 X 10 -130 to but not including 1.0 X 10 126. Each NUMBER value requires from 5 to 22 bytes.

Specify a fixed- point number as NUMBER (p,s) where:

  • p is the precision or the total number of significant decimal digits, where the most significant digit is the left-most non-zero digit and the least significant digit is the right-most known digit.

  • 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 to and including the least significant digit.

    • Negative scale is the number of significant digits to the left of the decimal point 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.

This 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 *)

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

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

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

TT_BIGINT

The TT_BIGINT data type is a signed integer that ranges from -9,223,372,036,854,775,808 (-2 63) to 9,223,372,036,854,775,807 (263 -1). It requires 8 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 a data type of 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 *)

TT_INTEGER

The TT_INTEGER data type is a signed integer that ranges from -2,147,483,648 (-2 31) to 2,147,483,647 (231 -1). It requires 4 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 example alters the table numerics and adds col3 with a data type of INT. Describing the table shows that the data type is NUMBER (38). col3 is dropped. A second ALTER TABLE adds col2 with a data type of INTEGER. Describing the table shows that the data type is NUMBER (38). col3 is dropped. col3 and col4 are then added with a data type of TT_INTEGER and TT_INT. Describing the table shows the data types are 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 DROP 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 DROP 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 *)

TT_SMALLINT

The TT_SMALLINT data type is a signed integer that ranges from -32,768 (-215) to 32,767 (215 - 1). It requires 2 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 example alters the table numerics and adds col2 with a data type of SMALLINT. Describing the table shows that the data type is NUMBER (38). col2 is dropped. A second ALTER TABLE adds Col2 with a data type of 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 DROP 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 *)

TT_TINYINT

The TT_TINYINT data type is an unsigned integer that ranges from 0 to 255 (28 -1). It requires 1 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 example first attempts to create a table named numerics that defines a column named col1 with data type TINYINT. TimesTen returns an error. The column is redefined 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

Floating-point numbers can have a decimal point or can have no decimal point. An exponent may be used to increase the range (for example, 1.2 e-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.

BINARY_DOUBLE

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

This 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 *)
BINARY_FLOAT

BINARY_FLOAT is a 32-bit single- precision floating -point number.

FLOAT and FLOAT (n)

TimesTen also supports the ANSI type FLOAT. FLOAT is an exact numeric type and is implemented as the NUMBER type. The number n indicates the number of bits of precision the value can store. The value ranges 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, multiple the decimal precision by 3.32193. The maximum 126 digits of binary precision is equivalent to approximately 38 digits of decimal precision.

BINARY and VARBINARY data types

The BINARY data type is a fixed-length binary value with a length of n bytes. 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. The value of n ranges from 1 to 4,194,304 (222) bytes.

The example creates a table and defines 2 columns. col1 is defined with data type BINARY and col2 is defined with data type VARBINARY.

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

Numeric precedence

The result type of an expression is determined by the operand with the highest type precedence. For example, the sum of TT_INTEGER and BINARY_FLOAT types results in type BINARY_FLOAT because BINARY_FLOAT has higher numeric precedence than TT_INTEGER. Similarly, the product of NUMBER and BINARY_DOUBLE types result in type BINARY_DOUBLE because BINARY_DOUBLE has higher precedence than NUMBER.

The numeric precedence order is as follows (highest to lowest):

ROWID data type

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 specification".

Specify literal ROWID values in SQL statements as constants enclosed in single quotes. For example:

Command> SELECT ROWID, last_name
       > FROM employees
       > WHERE ROWID='BMUFVUAAABTAAAAFi8';
< BMUFVUAAABTAAAAFi8, Hartstein >
1 row found.

The ROWID data type can be used as follows:

Implicit type conversions are supported for assigning values and comparison operations between ROWID and CHAR or ROWID and VARCHAR2 data.

When CHAR, VARCHAR2 and ROWID operands are combined in CASE, COALESCE, DECODE or NVL 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 TO_CHAR function.

Datetime data types

The datetime data types are:

DATE

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 7 bytes of storage.

TimesTen does not support user-specified NLS_DATE_FORMAT settings. The SQL TO_CHAR and TO_DATE functions can be used to specify other formats.

TIME

The format of a TIME value is HH:MI:SS and ranges from 00:00:00 (12:00:00 AM to 23:59:59 (11:59:59 PM). The TIME data type requires 8 bytes of storage.

TIMESTAMP

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 than the TT_TIMESTAMP and supports more precision than the 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.

TT_DATE

The format of a TT_DATE value is YYYY-MM-DD and ranges from 1753-01-01 (January 1, 1753) to 9999-12-31 (December 31, 9999 AD). The TT_DATE data type requires 4 bytes of storage.

TT_TIMESTAMP

The format of a TT_TIMESTAMP value is YYYY-MM-DD HH:MI:SS [.FFFFFF]. 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 8 bytes of storage. TT_TIMESTAMP is faster than the TIMESTAMP data type and has a smaller storage size than the TIMESTAMP type.

TimesTen intervals

This section includes the following topics:

Using INTERVAL data types

If you are using TimesTen type mode, for information on INTERVAL, refer to the Oracle TimesTen In-Memory Database Release 7.0.5 documentation.

TimesTen supports interval type only in a constant specification and intermediate expression result. Interval type cannot be the final result. Columns cannot be defined with an INTERVAL type. See "Type specifications".

You can specify a single-field interval literal in an expression, but you cannot specify a complete expression that returns an interval data type.

TimesTen supports interval literals of the form:

INTERVAL [+\-] CharString IntervalQualifier

Using DATE and TIME data types

This section shows some DATE, TIME and TIMESTAMP data type examples:

To create a table named sample that contains both a column named dcol with the type DATE and a column named tcol with the type TIME, use:

CREATE TABLE sample (tcol TIME, dcol DATE);

To insert DATE and TIME values into the sample table, use:

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:

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 named tscol with the type TIMESTAMP and then select all rows in the table that are between noon and 4:00 p.m. on October 29, 1998, use the 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 allows both literal and string formats of the TIME, 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 then 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.

Handling timezone conversions

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 timezone to be Pacific Standard Time. If the application is using TIME and TIMESTAMP values from the Pacific Daylight Time or Eastern Daylight/Standard Time, the application must convert TIME and TIMESTAMP to Pacific Standard Time.

Datetime and interval data types in arithmetic operations

If you are using TimesTen type mode, see the Oracle TimesTen In-Memory Database Release 7.0.5 documentation for information about datetime and interval types in arithmetic operations.

Datetime refers to DATE, TIME, and TIMESTAMP data types. Date and time arithmetic is supported with the following syntax:

  • TimeVal1 - TimeVal2 or TimestampVal1 - TimestampVal2 or DateVal1 - DateVal2 returns the difference as an interval day to second.

  • TT_DateVal1 - TT_DateVal2 returns the number of days difference as an integer.

  • DateTimeVal {+|-} IntervalVal

  • IntervalVal + DateTimeVal

  • IntervalVal1 {+|-} IntervalVal2

  • IntervalVal {*|/} NumericVal

  • NumericVal * IntervalVal

The INTERVAL data type cannot be the final result of a complete expression. The EXTRACT function must be used to extract the desired component of this interval result.

The following table lists the data type that results from each operation:

Operand 1 Operator Operand 2 Result type
TIME |

DATE |

TIMETAMP

- TIME |

DATE |

TIMESTAMP

INTERVAL DAY TO SECOND
TT_DATE - TT_DATE TT_BIGINT (number of days)
datetime + or - INTERVAL datetime
INTERVAL + datetime datetime
INTERVAL + or - INTERVAL INTERVAL
INTERVAL * or / Numeric INTERVAL
Numeric * INTERVAL INTERVAL

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 2 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;
SELECT DATE1 - DATE2 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 - timdstamp2 = INTERVAL '10' DAY;

You cannot extract YEAR from an INTERVAL DAY TO SECOND:

SELECT EXTRACT (YEAR FROM timestamp1 - timestamp2) FROM dual;

Restrictions on datetime and interval arithmetic operations

Consider these restrictions when performing datetime and interval arithmetic:

  • The results for addition and subtraction with DATE and TIMESTAMP types for INTERVAL YEAR, INTERVAL MONTH are not closed. For example, adding 1 year to the DATE or TIMESTAMP of '2004-02-29' results in a date arithmetic error (TimesTen error message 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.

Storage requirements

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. For character semantics, the number of bytes stored out of line is dependent on the character set. For example, for a character set with 4 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

BINARY (n)

n bytes

BINARY_DOUBLE

8 bytes

BINARY_FLOAT

4 bytes

CHAR (n

[BYTE|CHAR])

n bytes or if character semantics, n characters. If character semantics, the length of the column (n) is based on length semantics and character set.

DATE

7 bytes

INTERVAL

An INTERVAL cannot be stored in TimesTen.

NCHAR(n)

Bytes required is 2* n where n is the number of characters.

NUMBER

5 to 22 bytes

NVARCHAR2 (n)

For NOT INLINE columns:

On 32-bit platforms, 2*(length of value) + 20 bytes (minimum of 28 bytes).

On 64-bit platforms, 2*(length of value) + 24 bytes (minimum of 40bytes).

For INLINE columns:

On 32-bit platforms, 2*(length of column) + 4 bytes.

On 64-bit platforms, 2*(length of column) + 8 bytes.

ROWID

12 bytes

TIMESTAMP

12 bytes

TT_BIGINT

8 bytes

TT_DATE

4 bytes

TT_DECIMAL(p,s)

Approximately p/2 bytes

TT_INT[EGER]

4 bytes

TT_SMALLINT

2 bytes

TT_TIME

8 bytes

TT_TIMESTAMP

8 bytes

TT_TINYINT

1 byte

VARBINARY (n)

For NOT INLINE columns:

On 32-bit platforms, length of value + 20 bytes (minimum of 28 bytes).

On 64-bit platforms, length of value + 24 bytes (minimum of 40 bytes).

For INLINE columns:

On 32-bit platforms, length of column + 4 bytes.

On 64-bit platforms, length of column + 8 bytes.

VARCHAR2 (n

[BYTE|CHAR])

For NOT INLINE columns:

On 32-bit platforms, length of value + 20 bytes (minimum of 28 bytes). NULL value is stored as (null bit) + 4 bytes, or 4.125 bytes.

On 64-bit platforms, length of value + 24 bytes (minimum of 40 bytes). NULL value is stored as (null bit)+8 bytes, or 8.125 bytes.

This storage principal holds for all variable length NOT INLINE data types: TT_VARCHAR, TT_NVARCHAR, VARCHAR2, NVARCHAR2, VARBINARY.

For INLINE columns:

On 32-bit platforms, n + 4 bytes. NULL value is stored as (null bit)+n+4 bytes.

On 64-bit platforms, n + 8 bytes. NULL value is stored as (null bit)+n+8 bytes.

If character semantics, the length of the column (n) is based on length semantics and character set.


Data type comparison rules

This section describes how values of each data type are compared in TimesTen.

Numeric values

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.

Date values

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:15pm' is greater than '30-AUG-2006 10:10am'.

Character values

Character values are compared by:

Binary and linguistic sorting

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.

Blank-padded and nonpadded comparison semantics

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'

Data type conversion

Generally an expression cannot contain values of different data types. However, TimesTen supports both implicit and explicit conversion from one data type to another. We recommend explicit conversion.

Implicit data type conversion

The following rules apply:

  • Conversions between 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 exact numeric values use decimal precision whereas the floating-point numbers use binary precision.

  • When comparing a character value with any date, time, or datetime value, TimesTen converts the character data to the date, time, or datetime value.

  • Implicit and explicit CHAR/VARCHAR2 <-> NCHAR/NVARCHAR2 conversions are supported except when the character set is TIMESTEN8. An example of explicit 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 Ccnvdemo.c1 = convdemo2.c1;
    X1, X2, C1, C1
    < 10, 10, ABC       , ABC        >
    < 100, 100, def       , def        >
    2 rows found.
    

NULL values

A NULL value indicates the absence of a value. It is a placeholder for a value that is missing. Any column in a table or parameter in an expression, regardless of its data type, can contain NULL values unless you specify NOT NULL for the column when you create the table.

The following properties of NULL values affect operations on rows, parameters, or local variables:

Because of these properties, TimesTen ignores columns, rows, or parameters containing NULL values when:

In several SQL predicates, described in Chapter 4, "Search Conditions," you can explicitly test for NULL values. In an application, you can use the ODBC functions SQLBindCol, SQLBindParameter, SQLGetData, and SQLParamData, or you can use the JDBC functions PreparedStatement.setNull and ResultSet.getXXXX with ResultSet.wasNull to handle input and output of NULL values.

INF and NAN

TimesTen supports the IEEE floating-point values Inf (positive infinity), -Inf (negative infinity) and NaN (not a number).

Constant values

You can use constant values in places where a floating-point constant is allowed: The supported constant values are:

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

Primary key values

Inf, -Inf, and NaN are acceptable values in columns defined with a primary key. This is a deviation in behavior from NULL. NULL values are not allowed on columns defined with a primary key.

You can only insert Inf, -Inf, and NaN values into BINARY_FLOAT and BINARY_DOUBLE columns.

Selecting Inf and NaN (floating-point conditions)

Floating-point conditions determine whether an expression is infinite or is the undefined result of an operation (NaN or "not a number").

In the 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 specific BINARY_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 higher than any other value, but lower than Nan and NULL.

  • NaN sorts higher than Inf.

  • NULL sorts higher than NaN. NULL is always the largest value in any collating sequence.

Expressions involving Inf and NaN

  • 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 0 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 selection), use both the IS NOT NAN and IS NOT INFINITE predicates.

Overflow and truncation

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 data store 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 computation and when the values are inserted into the data store or when data store values are updated. TimesTen returns an error only upon insertion or update. When overflow with approximate values occurs, TimesTen returns the special value Inf.

There are several circumstances that can cause overflow:

Truncation can cause an error or warning for alphanumeric or numeric data types:

Underflow

When an approximate numeric value is too close to zero to be represented by the hardware, TimesTen underflows to zero and returns a truncation warning.

Replication limits

TimesTen places the following limits on the size of data types in a data store that is being replicated:

TimesTen type mode (backward compatibility)

TimesTen supports a data type backward compatibility mode called TimesTen type mode. The type mode is a data store creation attribute. TypeMode=1 indicates TimesTen mode.

For more information on types modes, see "TypeMode" in Oracle TimesTen In-Memory Database Reference.

For information on data type usage in TimesTen type mode, refer to the Oracle TimesTen In-Memory Database Release 7.0.5 documentation.

Data types supported in TimesTen type mode

Table 1-6 Data types supported in TimesTen type mode

Data type Description

BIGINT

A signed 8-byte integer in the range -9,223,372,036,854,775,808-(263) to 9,223,372,036,854,775,807 (263 - 1).

Alternatively, specify TT_BIGINT.

BINARY (n)

Fixed-length binary value of n bytes. Legal values for n range from 1 to 8300.

BINARY data is padded to the maximum column size with trailing zeroes.

BINARY_DOUBLE

64-bit floating -point number. BINARY_DOUBLE is a double-precision native floating point number. Supports +Inf, -Inf and Nan values. BINARY_DOUBLE is an approximate numeric value consisting of an exponent and mantissa. You can use exponential or E-notation. BINARY_DOUBLE has binary precision 53.

Minimum positive finite value: 2.22507485850720E-308

Maximum positive finite value: 1.79769313486231E+308

Alternatively, specify DOUBLE [PRECISION] or FLOAT [(53)].

BINARY_FLOAT

32-bit floating-point number. BINARY_FLOAT is a single-precision native floating-point type. Supports +Inf, -Inf and NaN values. BINARY_FLOAT is an approximate numeric value consisting of an exponent and mantissa. You can use exponential or E-notation. BINARY_FLOAT has binary precision 24.

Minimum positive finite value: 1.17549E-38F

Maximum positive finite value: 3.40282E+38F

Alternatively, specify REAL or FLOAT (24).

CHAR[ACTER] [(n [BYTE|CHAR])]

Fixed-length character string of length n bytes or characters. Default is 1 byte.

BYTE indicates that the column has byte length semantics. Legal values for n range from a minimum of 1 byte to a maximum 8300 bytes.

CHAR indicates that the column has character length semantics. The minimum CHAR length is 1 character. The maximum CHAR length depends on how many characters fit in 8300 bytes. This is determined by the database character set in use. For character set AL32UTF8, up to four bytes per character may be needed, so the CHAR length limit ranges from 2075 to 8300 depending on the character set.

A zero-length string is a valid non-NULL value. CHAR data is padded to the maximum column size with trailing blanks. Blank-padded comparison semantics are used. For information on blank-padded and nonpadded semantics, see "Blank-padded and nonpadded comparison semantics".

Alternatively, specify TT_CHAR [(n [BYTE|CHAR])].

DATE

Stores date information: century, year, month, date. The format is YYYY-MM-DD. MM is expressed as an integer. For example: 2006-10-28.

Storage size is 4 bytes.

Valid dates are between 1753-01-01 (January 1,1753) and 9999-12-31 (December 31, 9999).

Alternatively, specify TT_DATE.

DEC[IMAL][(p[,s])] or

NUMERIC[(p[,s])]

An exact numeric value with a fixed maximum precision (total number of digits) and scale (number of digits to the right of the decimal point). The precision p must be between 1 and 40. The scale must be between 0 and p. The default precision is 40 and the default scale is 0.

INTERVAL [+/-] IntervalQualifier

TimesTen partially supports INTERVAL types, expressed with the type INTERVAL and an IntervalQualifier. An IntervalQualifier can only specify a single field type with no precision. The default leading precision is 8 digits for all INTERVAL types. The single field type can be one of: YEAR, MONTH, DAY, HOUR, MINUTE or SECOND. Currently, INTERVAL type can be specified only with a constant.

NCHAR[(n)]

Fixed-length string of n two-byte Unicode characters.

The number of bytes required is 2* n where n is the specified number of characters. NCHAR character limits are 1/2 the byte limits so the maximum size is 4150. Default and minimum bytes of storage is 2n (2).

A zero-length string is a valid non-NULL value. NCHAR data is padded to the maximum column size with U+0020 SPACE. Blank-padded comparison semantics are used. For information on blank-padded and nonpadded semantics, see "Blank-padded and nonpadded comparison semantics".

Alternatively, specify TT_NCHAR[(n)].

NATIONAL CHARACTER and NATIONAL CHAR are synonyms for NCHAR.

SMALLINT

A native signed 16 bit integer in the range –32,768 –(215) to 32,767 (215–1).

Alternatively, specify TT_SMALLINT.

TIME

A time of day between 00:00:00 (12 midnight) and 23:59:59 (11:59:59 pm), inclusive. The format is: HH:MI:SS. Storage size is 8 bytes.

TIMESTAMP

A data and time between 1753-01-01 00:00:00 (January 1, 1753 midnight) 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 "Data Truncated" error. The format is YYYY-MM-DD HH:MI:SS [.FFFFFF].

Storage size is 8 bytes.

Alternatively, specify TT_TIMESTAMP or [TT_]TIMESTAMP (6).

TINYINT

Unsigned integer ranging from 0 to 255 (28-1).

Since TINYINT is unsigned, the negation of a TINYINT is SMALLINT.

Alternatively, specify TT_TINYINT.

INT[EGER]

A signed integer in the range –2,147,483,648 –(231) to 2,147,483,647 (231–1).

Alternatively, specify TT_INTEGER.

NVARCHAR(n)

Variable-length string of n two-byte Unicode characters.

The number of bytes required is 2* n where n is the specified number of characters. NVARCHAR character limits are 1/2 the byte limits so the maximum size is 2,097,152 (221). You must specify n.

A zero-length string is a valid non-NULL value.

Blank-padded comparison semantics are used. For information on blank-padded and nonpadded semantics, see "Blank-padded and nonpadded comparison semantics".

Alternatively, specify TT_NVARCHAR(n).

NATIONAL CHARACTER VARYING, NATIONAL CHAR VARYING, and NCHAR VARYING are synonyms for NVARCHAR.

VARCHAR (n [BYTE|CHAR])

Variable-length character string having maximum length n bytes or characters. You must specify n.

BYTE indicates that the column has byte length semantics. Legal values for n range from a minimum of 1 byte to a maximum 4194304 (222) bytes.

CHAR indicates that the column has character length semantics.

A zero-length string is a valid non-NULL value.

Blank-padded comparison semantics are used. For information on blank-padded and nonpadded semantics, see "Blank-padded and nonpadded comparison semantics".

Alternatively, specify TT_VARCHAR (n [BYTE|CHAR]).

VARBINARY (n)

Variable-length binary value having maximum length n bytes. Legal values for n range from 1 to 4194304 (222).


Oracle data types supported in TimesTen type mode

Table 1-7 Oracle data types supported in TimesTen type mode

Data type Description

NUMBER [(precision [,scale])]

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, then maximum precision of 38 and flexible scale are assumed.

NUMBER supports scale > precision and negative scale.

NUMBER stores zero as well as positive and negative fixed numbers with absolute values from 1.0 x 10-130 to (but not including) 1.0 x 10126. If you specify an arithmetic expression whose value has an absolute value greater than or equal to 1.0 x 10126, then TimesTen returns an error.

In TimesTen type mode, the NUMBER data type stores 10e-89 as its smallest (closest to zero) value.

ORA_CHAR [(n [BYTE|CHAR])]

Fixed-length character string of length n bytes or characters. Default is 1 byte.

BYTE indicates that the column has byte length semantics. Legal values for n range from a minimum of 1 byte to a maximum 8300 bytes.

CHAR indicates that the column has character length semantics. The minimum CHAR length is 1 character. The maximum CHAR length depends on how many characters fit in 8300 bytes. This is determined by the database character set in use. For character set AL32UTF8, up to four bytes per character may be needed, so the CHAR length limit ranges from 2075 to 8300 depending on the character set.

A zero-length string is interpreted as NULL.

ORA_CHAR data is padded to the maximum column size with trailing blanks. Blank-padded comparison semantics are used. For information on blank-padded and nonpadded semantics, see "Blank-padded and nonpadded comparison semantics".

ORA_DATE

Stores date and time information: century, year, month, date, hour, minute and second. Format is YYYY-MM-DD HHMMSS.

Valid date range is from January 1, 4712 BC to December 31, 9999 AD.

The storage size is 7 bytes. There are no fractional seconds.

ORA_NCHAR[(n)]

Fixed-length string of length n two-byte Unicode characters.

The number of bytes required is 2* n where n is the specified number of characters. NCHAR character limits are 1/2 the byte limits so the maximum size is 4150. Default and minimum bytes of storage is 2n (2).

A zero-length string is interpreted as NULL.

ORA_NCHAR data is padded to the maximum column size with U+0020 SPACE. Blank-padded comparison semantics are used. For information on blank-padded and nonpadded semantics, see "Blank-padded and nonpadded comparison semantics".

ORA_NVARCHAR2(n)

Variable-length string of n two-byte Unicode characters.

The number of bytes required is 2* n where n is the specified number of characters. ORA_NVARCHAR2 character limits are one half the byte limits so the maximum size is 2,097,152 (221). You must specify n.

A zero-length string is interpreted as NULL.

Nonpadded comparison semantics are used.

For information on blank-padded and nonpadded semantics, see "Blank-padded and nonpadded comparison semantics".

ORA_VARCHAR2 (n [BYTE|CHAR])

Variable-length character string having maximum length n bytes or characters.

BYTE indicates that the column has byte length semantics. Legal values for n range from a minimum of 1 byte to a maximum 4194304 (222) bytes. You must specify n.

CHAR indicates that the column has character length semantics.

A zero-length string is interpreted as NULL.

Nonpadded comparison semantics are used. For information on blank-padded and nonpadded semantics, see "Blank-padded and nonpadded comparison semantics".

ORA_TIMESTAMP

[(fractional_seconds_precision)]

Stores year, month, and day values of the date data type plus hour, minute, and second values of time. Fractional_seconds_precision is the number of digits in the fractional part of the seconds field. Valid date range is from January 1, 4712 BC to December 31, 9999 AD.

The fractional seconds precision range is 0 to 9. The default is 6. Format is:

YYYY-MM-DD HH:MI:SS [.FFFFFFFFF]

Storage size 12 bytes.