Data Types

Workspace data types fall into categories, which are referred to as basic data types. They are listed in the following table.

Basic Type	Specific Type
Numeric	INTEGER, SHORTINTEGER, LONGINTEGER, DECIMAL, SHORTDECIMAL, NUMBER
Text	TEXT, NTEXT, ID
Boolean	BOOLEAN
Date	DATETIME, DATE

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

• For most data values, such as those stored in variables, the INTEGER, SHORTINTEGER, DECIMAL, SHORTDECIMAL, NUMBER, TEXT, ID, NTEXT, BOOLEAN, DATETIME, and DATE data types are supported.
• For dimension values, the INTEGER, NUMBER, TEXT, ID, and NTEXT data types are supported.

Numeric Data Types

The following numeric data types are supported.

Data Type	Data Value
INTEGER	A whole number in the range of (-2**31) to (2**31)-1.
SHORTINTEGER	A whole number in the range of (-2**15) to (2**15)-1.
LONGINTEGER	A whole number in the range of (-2**63) to (2**63)-1.
DECIMAL	A decimal number with up to 15 significant digits.
SHORTDECIMAL	A decimal number with up to 7 significant digits.
NUMBER	A decimal number with up to 38 significant digits.

For data entry, a value for any of these data types can begin with a plus (+) or minus (-) sign; it cannot contain commas. Additionally, a decimal value can contain a decimal point. For data display, thousands and decimal markers are controlled by the NLS_NUMERIC_CHARACTERS option.

The workspace NUMBER data type is fully compatible with the database NUMBER data type. It is used for dimensions and surrogates when a text or integer data type is not appropriate. It is typically assigned to variables that are not used for calculations (like forecasts and aggregations), and it is used for variables that must match the rounding behavior of the database or require a high degree of precision. When deciding whether to assign the NUMBER data type to a variable, keep the following facts in mind in order to maximize performance:

• Analytic workspace calculations on NUMBER variables is slower than calculations on other numeric types such as DECIMAL.
• When data is fetched from an analytic workspace to a relational column that has the NUMBER data type, performance is best if the data already has the NUMBER data type in the analytic workspace because a conversion step is not required.

Examples of Literal Numeric Values

Examples of literal numeric values are:

-1
256000
+2147483647
10000000000.0009

Text Data Types

The following text data types are supported.

Data Type	Data Value
TEXT	Up to 4000 bytes per line in the database character set. This data type is equivalent to the CHAR and VARCHAR2 data types in the database.
NTEXT	Up to 4000 bytes per line in UTF-8 character encoding. This data type is equivalent to the NCHAR and NVARCHAR2 data types in the database.
ID	Up to 8 characters per line in the database character set

For data entry, text literals must be enclosed in single quotes. Otherwise, the OLAP DML command processor will look for a workspace object by that name.

Escape Sequences

In some cases, text data includes values that are not printable. Escape sequences are provided to allow such values to be input and displayed. An escape sequence is a series of alphanumeric characters that begins with a backslash.

The following table shows escape sequences that are recognized.

Escape Sequence	Meaning
\b	Backspace
\f	Form feed
\n	Line feed
\r	Carriage return
\t	Horizontal tab
\"	Double quote
\'	Single quote
\\	Backslash
\dnnn	Character with ASCII code nnn decimal, where \d indicates a decimal escape and nnn is the decimal value for the character
\xnn	Character with ASCII code nn hexadecimal, where \x indicates a hexadecimal escape and nn is the hexadecimal value for the character
\Unnnn	Character with Unicode nnnn, where \U indicates a Unicode escape and nnnn is a four-digit hexadecimal integer that represents the Unicode codepoint with the value U+nnnn. The U must be a capital letter.

Examples of Literal Text Values

Examples of literal text values are:

'Raoul D\'Allesandro'
'NONE'
'January 2002'

Boolean Data Type

A Boolean data type is provided that you can use to represent logical values. In code, you can use any of the following values (in any combination of uppercase and lowercase characters) to represent Boolean values:

• YES, TRUE, ON
• NO, FALSE, OFF

The values that are used in your installation are determined by the language identified by the NLS_LANGUAGE option. You can use the read-only NOSPELL and YESSPELL options to obtain the values.

Working with Boolean expressions is discussed in "Boolean Expressions".

Date Data Types

The following date data types are supported.

Data Type	Data Value
DATETIME	Dates between January 1, 4712 B.C. and December 31, 9999 A.D., and times in hours, minutes and seconds.
DATE	Dates between January 1, 1000 A.D. and December 31, 9999 A.D.

The format and language of DATETIME values are controlled by the NLS_DATE_FORMAT and NLS_DATE_LANGUAGE options. The DATETIME data type is supported by Oracle standard libraries and operates the same way in the database, and thus is preferable to the DATE data type. The DATEORDER, DATEFORMAT, and MONTHNAMES options, which control the formatting of DATE values, have no effect on DATETIME values. However, DATETIME and DATE values can be used interchangeably in most DML commands