| Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B10339-02 |
|
|
View PDF |
| Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B10339-02 |
|
|
View PDF |
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".
| 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.
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 (-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 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.
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.
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.
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).) |
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.
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 |
\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. |
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".
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 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. |
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.
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.
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.
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.
|
 Copyright © 2003 Oracle Corporation All Rights Reserved. |
|
