2.1 OLAP DML Data Types

Workspace data types fall into categories, which are referred to as basic data types. They are listed in Table 2-1, "OLAP DML Data Types".

Table 2-1 OLAP DML Data Types

Basic Type	Specific Types
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 values, including variable values, all of the data types are supported.
For dimension values, only the INTEGER, NUMBER, TEXT, ID, and NTEXT data types are supported.

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

2.1.1 Numeric Data Types

The numeric data types described in Table 2-2, "OLAP DML Numeric Data Types" are supported.

Table 2-2 OLAP DML Numeric Data Types

Data Type	Data Value
`INTEGER`	A whole number in the range of (-231) to (231)-1.
`SHORTINTEGER`	A whole number in the range of (-215) to (215)-1.
`LONGINTEGER`	A whole number in the range of (-263) to (263)-1.
`DECIMAL`	A decimal number with up to 15 significant digits in the range of -(10308) to +(10308).
`SHORTDECIMAL`	A decimal number with up to 7 significant digits in the range of -(1038) to +(1038).
`NUMBER`	A decimal number with up to 38 significant digits in the range of -(10125) to +(10125).

For data entry, a value for any of these data types can begin with a plus (+) or minus (-) sign; it cannot contain commas. Note, however, that a comma is required before a negative number that follows another numeric expression, or the minus sign is interpreted as a subtraction operator. Additionally, a decimal value can contain a decimal point. For data display, thousands and decimal markers are controlled by the NLS_NUMERIC_CHARACTERS option.

2.1.1.1 Using LONGINTEGER Values

Most of the numerical data types return NA when a value is outside its range. However, the LONGINTEGER data type does not have overflow protection and will return an incorrect value when, for example, a calculation produces a number that exceeds its range. Use the NUMBER data type instead of LONGINTEGER when this is likely to be a problem.

2.1.1.2 Using NUMBER Values

When you define a NUMBER variable, you can specify its precision (p) and scale (s) so that it is sufficiently, but not unnecessarily, large. Precision is the number of significant digits. Scale can be positive or negative: Positive scale identifies the number of digits to the right of the decimal point; negative scale identifies the number of digits to the left of the decimal point that can be rounded up or down.

The NUMBER data type is supported by Oracle Database standard libraries and operates the same way as it does in SQL. 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 other numerical data types because NUMBER values are calculated in software (for accuracy) rather than in hardware (for speed).
When data is fetched from an analytic workspace to a relational column that has the NUMBER data type, performance is best when the data already has the NUMBER data type in the analytic workspace because a conversion step is not required.

2.1.2 Text Data Types

The text data types described in Table 2-3, "OLAP DML Text Data Types" are supported by Oracle OLAP.

Table 2-3 OLAP DML Text Data Types

Data Type	Data Value
`TEXT`	Up to 4000 bytes for each 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 for each 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 single-byte characters for each line in the database character set. (`ID` is valid only for values of simple dimensions, see DEFINE DIMENSION (simple).)

2.1.2.1 Literals

Enclose text literals in single quotes. Oracle OLAP recognizes unquoted alpha-numeric values as object names and double quotes as the beginning of a comment.

2.1.2.2 Escape Sequences

Table 2-4, "Recognized Escape Sequences" shows escape sequences that are recognized by Oracle OLAP.

Table 2-4 Recognized Escape Sequences

Sequence	Meaning
`\b`	Backspace
`\f`	Form feed
`\n`	Line feed
`\r`	Carriage return
`\t`	Horizontal tab
`\"`	Double quote
`\'`	Single quote
`\\`	Backslash
`\d`nnn	Character with ASCII code nnn decimal, where \`d` indicates a decimal escape and nnn is the decimal value for the character
`\x`nn	Character with ASCII code nn hexadecimal, where `\x` indicates a hexadecimal escape and nn is the hexadecimal value for the character
`\U`nnnn	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.

2.1.3 Boolean Data Type

A BOOLEAN data type enables you to represent logical values. In code, BOOLEAN values are represented by values for "no" and yes" (in any combination of uppercase and lowercase characters). The actual values that are recognized in your version of Oracle OLAP 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 represent BOOLEAN values. In English language code, you can represent BOOLEAN values, using:

YES, TRUE, ON
NO, FALSE, OFF

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

2.1.4 Date Data Types

The date data types that are supported are listed in Table 2-5, "OLAP DML Date Data Types".

Table 2-5 OLAP DML Date Data Types

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.

2.1.4.1 Date and Time Options

A number of options determine how date and time values are handled. These options are listed in Table 2-6, "Date and Time Options".

Table 2-6 Date and Time Options

Statement	Description
CALENDARWEEK	Determines whether weeks should be aligned with the actual calendar year.
DATEFORMAT	Specifies the template used for displaying `DATE` values and converting `DATE` values to `TEXT` values.
DATEORDER	Contains three characters that indicate the intended order of the month, day, and year components of the `DATE` values in a workspace for those cases in which their interpretation is ambiguous.
DAYABBRLEN	Specifies the number of characters to use for abbreviations of day names that are stored in the DAYNAMES option.
DAYNAMES	A list of valid names for the days of the week. The names are used to display values of type `DATE` or to convert `DATE` values to text.
DSECONDS	(Read-only) The number of seconds since January 1, 1970.
MONTHABBRLEN	The number of characters to use for abbreviations of month names that are stored in the MONTHNAMES option.
MONTHNAMES	The list of valid names for months that is used in handling values with a `DATE` data type and values of dimensions of type DAY, WEEK, MONTH, QUARTER, and YEAR.
SECONDS	(Read-only) The number of seconds since January 1, 1970.
WEEKDAYSNEWYEAR	For a dimension of type WEEK, determines how many days of the new year there must be for a week to be identified as week 1 of the new year.
YRABSTART	The specific 100-year period associated with years that are read or displayed using a two-digit abbreviation.

2.1.4.2 DATE Values

DATE values have independent input and output formats. You can enter date values in one style and report them in a different style. To change the order of the month, day, and year components, see the DATEORDER option. When you show a date value in output, the format depends on the DATEFORMAT option. The default format is a 2-digit day, a 3-letter month, and a 2-digit year; for example, 03MAR97. The text for the month names depends on the MONTHNAMES option.

2.1.4.3 DATETIME Values

The format and language of DATETIME values are controlled by the settings of the NLS_DATE_FORMAT and NLS_DATE_LANGUAGE options. The DATETIME data type is supported by Oracle Database standard libraries and operates the same way in the OLAP DML as it does in SQL. 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 statements.

2.1.4.4 Calculating Dates

You can add numbers to a DATE or DATETIME value, or subtract numbers from them. Whole numbers are calculated as days, and decimal values are calculated as fractions of a day. For example, SYSDATE+1.5 adds 1 day and 12 hours to the current date and time. You cannot divide or multiply DATE or DATETIME values, and you cannot subtract them from numbers. For example, 1-SYSDATE and 1*SYSDATE return errors.

2.1.5 Converting from One Data Type to Another

In many cases, Oracle OLAP performs automatic data type conversion for you.

Oracle OLAP automatically converts NTEXT values to TEXT when they are specified as arguments to OLAP DML statements. This can result in data loss when the NTEXT values cannot be represented in the database character set.
Oracle OLAP automatically converts SHORTINTEGER variables, as well as INTEGER variables with a fixed width of 1 byte, to INTEGER (with a width of 4 bytes) for calculations. When you calculate a total of SHORTINTEGER variables, then you can obtain and report a result greater than 32,767 or less than -32,768. When you calculate a total of 1-byte INTEGER variables, then you can obtain and report a result greater than 127 or less than -128. However, when you try to assign the result to a SHORTINTEGER variable or a 1-byte INTEGER variable respectively, then the variable is set to NA.

There are a number of OLAP DML functions that you can use to convert values from one data type to another. See Table A-2, "Data Type Conversion Functions" for a list of these functions.