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.

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

SHORTDECIMAL

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

NUMBER

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

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.

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.

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:

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

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.