Previous
Previous
 
Next
Next

OLAP DML Data Types

In the OLAP DML, as in other languages, a data type is a collection of values and the definition of one or more operations on those values.

The Oracle OLAP DML supports the data types outlined in Table: Summary of OLAP DML Data Types.

Summary of OLAP DML Data Types

Data Type Abbreviation Description

BOOLEAN

BOOL

Represents the logical TRUE and FALSE values.

DATE

None

Does not correspond to the SQL data type of the same name; but, instead, is an older data type that is unique to the OLAP DML.

Day, month, and year data (but not hour and minute data) between January 1, 1000 A.D. and December 31, 9999 A.D.

DATETIME

None

Corresponds to the SQL DATE data type.

Valid date range from January 1, 4712 BC to December 31, 9999 AD. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 7 bytes. This data type contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have fractional seconds or a time zone.

TIMESTAMP

None

Corresponds to the SQL TIMESTAMP data type.

Year, month, and day values of date, and hour, minute, and second values of time up to a precision of 9 places for the fractional part of the SECOND datetime field. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is 11 bytes. This data type contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It contains fractional seconds but does not have a time zone.

TIMESTAMP_TZ

None

Corresponds to the SQL TIMESTAMP WITH TIME ZONE data type.

All values of TIMESTAMP as well as time zone displacement value, with a precision of 9 places for the fractional part of the SECOND datetime field. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 13 bytes. This data type contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, and TIMEZONE_MINUTE. It has fractional seconds and an explicit time zone.

TIMESTAMP_LTZ


Corresponds to the SQL TIMESTAMP WITH LOCAL TIME ZONE data type.

All values of TIMESTAMP_TZ, with the following exceptions:

  • Data is normalized to the Database time zone when it is stored in the Database.

  • When the data is retrieved, users see the data in the session time zone.

The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is 11 bytes.

DSINTERVAL

None

Corresponds to the SQL INTERVAL DAY TO SECOND data type.

Stores a period of time in days, hours, minutes, and seconds.

YMINTERVAL

None

Corresponds to the SQL INTERVAL YEAR TO MONTH data type.

Stores a period of time in years and months.

INTEGER

INT

A whole number in the range of (-2**31) to (2**31)-1.

SHORTINTEGER

SHORTINT

A whole number in the range of (-2**15) to (2**15)-1.

LONGINTEGER

LONGINT

A whole number in the range of (-2**63) to (2**63)-1.

DECIMAL

DEC

A decimal number with up to 15 significant digits in the range of -(10**308) to +(10**308).

SHORTDECIMAL

SHORT

A decimal number with up to 7 significant digits in the range of -(10**38) to +(10**38).

NUMBER [(p,[s])]

None

A decimal number with up to 38 significant digits in the range of -(10**125) to +(10**125).

TEXT

None

Up to 32,767 bytes for each line in the Database character set. This data type is equivalent to the CHAR and VARCHAR2 data types in the Database. (Note that when defining a variable of this data type you specify the RANSPACE64 keyword in the DEFINE VARIABLE statement to increase the maximum number of characters for the values of the variable from nearly 2**32 to nearly 2**64.)

NTEXT

None

Up to 32,767 bytes for each line in UTF-8 character encoding. This data type is equivalent to the NCHAR and NVARCHAR2 data types in the Database. (Note that when defining a variable of this data type you specify the RANSPACE64 keyword in the DEFINE VARIABLE statement to increase the maximum number of characters for the values of the variable from nearly 2**32 to nearly 2**64.)

ID

None

Up to 8 single-byte characters for each line in the database character set. (ID is valid only for values of simple dimensions, see DEFINE DIMENSION (simple).)

RAW (size)

None

Raw binary data of length size bytes. Maximum size is 2000 bytes. You must specify size for a RAW value. (Note that when defining a variable of this data type you specify the RANSPACE64 keyword in the DEFINE VARIABLE statement to increase the maximum number of characters for the values of the variable from nearly 2**32 to nearly 2**64.)

ROWID

None.

Base 64 string representing the unique address of a row in its table. This data type is primarily for values returned by the ROWID pseudocolumn.

UROWID

None

Base 64 string representing the logical address of a row of an index-organized table. The optional size is the size of a column of type UROWID. The maximum size and default is 4,000 bytes.

WORKSHEET


Specified for arguments and temporary variables in an OLAP DML program when you want to handle arguments without converting values to a specific data type. Use the WKSDATA function to retrieve the data type of an argument with a WORKSHEET data type.


Categories of Data Types

Frequently, these data types are thought of as belonging to the following categories:

Which OLAP DML Data Objects Can Have Which Data Type?

Different objects support the use of different data types for their values: