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.

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 as described in "NLS Options".

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.

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.

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

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.

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.

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

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.

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

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.

To determine whether a text expression (such as an expression with a data type of TEXT or ID) represents a valid DATE value, use the ISDATE program.

DATETIME Values

The format and language of DATETIME values are controlled by the settings of the NLS_DATE_FORMAT and NLS_DATE_LANGUAGE options described in "NLS 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.

Valid DATE Values

A valid DATE value must fall between January 1, 1000, and December 31, 9999. It must conform to one of three styles: numeric, packed numeric, or month name. You can mix these styles throughout a session.

Numeric style

Specify the day, month, and year as three INTEGER values with one or more separators between them, using these rules:

The day and month components can have one digit or two digits.
For any year, the year component can have four digits (for example, 1997). For years in the range 1950 to 2049, the year component can, alternatively, have two digits (50 represents 1950, and so on).
To separate the components, you can use a space, dash (-), slash (/), colon (:), or comma (,).

Examples: '24/4/97' or '24-04-1997'

Packed numeric style

Specify the day, month, and year as three INTEGER values with no separators between them, using these rules:

The day and month components must have two digits. When the day or month is less than 10, it must be preceded by a zero.
For any year, the year component can have four digits (for example, 1997). For years in the range 1950 to 2049, the year component can, alternatively, have two digits (50 represents 1950, and so on).
You cannot use any separators between the date components.

Examples: '240497' or '04241997'

Month name style

Specify the day and year as INTEGER values and the month as text, using these rules:

The month component must match one of the names listed in the MONTHNAMES option. You can abbreviate the month name to one letter or more, when you supply enough letters to uniquely match the beginning of a name in MONTHNAMES. The case of the letters in the month component (uppercase or lowercase) does not need to match the case in MONTHNAMES.
The day component can have one digit or two digits.
For any year, the year component can have four digits (for example, 1997). For years in the range 1950 to 2049, the year component can, alternatively, have two digits (50 represents 1950, and so on).
When the day and year components are adjacent, they must have at least one separator between them. As separators, you can use a space, dash (-), slash (/), colon (:), or comma (,). When you want, you can place one or more separators between the day and month or between the year and month.

Examples: '24APR97' or '24 ap 97' or 'April 24, 1997'

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.

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-24, "Data Type Conversion Functions" for a list of these functions.