Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-02 |
|
|
View PDF |
OLAP DML options fall into the following general categories:
Attaching analytic workspaces and specifying segment size. See Table A-3.
Multi-language support options. See Table A-4.
Aggregating data. See Table A-5.
Allocating data. See Table A-6.
Modeling data. See Table A-7.
Compiling programs, models and aggregation specifications. See Table A-8.
Handling errors. See Table A-9.
Debugging programs and models. See Table A-10.
Embedding SQL in OLAP DML programs. See Table A-11.
File reading and writing. See Table A-12.
Importing and exporting analytic workspaces. See Table A-13, "EIF Options".
OLAP DML reports. See Table A-14, "Report Options".
Working with empty cells and nonexistent values. See Table A-15, "NA and Nonexistent Value Options".
Using date and time values. See Table A-16, "Date and Time Options".
Calculating data. See Table A-17, "Numeric Options".
Oracle Database options. See Table 20-2, "OLAP DML NLS Options".
Table A-3, "Options Related to Working with Analytic Workspaces" lists the options that you might want to reset before you either create or attach an analytic workspace.
Table A-3 Options Related to Working with Analytic Workspaces
Option | Description |
---|---|
|
An option that contains the number of seconds that AW ATTACH with the if the WAIT keyword waits for an analytic workspace to become available for access. |
|
An option that specifies the default maximum segment size for an analytic workspace created in your database session. |
You use the options outlined in Table A-4, "Multi-language Support Options", along with the $DEFAULT_LANGUAGE property, to create multi-language programs and applications.
Table A-4 Multi-language Support Options
Option | Description |
---|---|
|
An option that specifies whether or not the value of the SESSION_NLS_LANGUAGE option can be changed. |
|
An option that is the name of the language used by Oracle OLAP throughout the session. |
|
An option that controls whether or not Oracle OLAP keeps the value of the SESSION_NLS_LANGUAGE option synchronized with the value of the NLS_LANGUAGE option. |
A number of options can impact aggregation as outlined in Table A-5, "Aggregation Options".
Table A-5 Aggregation Options
Option | Description |
---|---|
|
An option that specifies that a given cell that contains detail data can have more than one path into a cell that contains aggregated data. |
|
An option that identifies a destination for status information about an aggregation operation. |
|
An option that controls whether Oracle OLAP uses the session cache to store variable data that has been aggregated on the fly. |
|
An option that specifies if and where variable data that has been aggregated on the fly should be stored. (The VARCACHE option is only one factor that can determine this decision.) |
A number of options effect allocation. These options are listed in Table A-6, "Allocation Options".
Table A-6 Allocation Options
Option | Description |
---|---|
|
An option that determines the contents and the formatting of the error log that you specify with the ERRORLOG argument to the ALLOCATE command. |
|
An option that determines the column headings for the error log that you specify with the ERRORLOG argument to the ALLOCATE command. |
An option that identifies a destination for status information about an allocation or aggregation operation. A file unit uniquely identifies the destination file. |
A number of options effect how a model solves simultaneous blocks. These options are listed in Table A-7, "Model Options".
Table A-7 Model Options
Option | Purpose |
---|---|
|
For the Gauss-Seidel solution method, specifies a weighting factor that damps out oscillations between iterations. |
|
Specifies the action to be taken when a model equation diverges or a block fails to converge. The possible values are STOP, CONTINUE, and DEBUG. |
|
A comparison factor that is used in testing for convergence and divergence. It controls the degree to which the tests compare the absolute amount of change between iterations versus the proportional change. This option is useful in models that test very small values. |
|
Specifies whether equations in a simultaneous block are executed in the order in which you place them in the model or in an order determined by the model compiler. |
|
The maximum number of iterations to perform in seeking a solution for a block. |
|
A value that is used in testing for divergence. It controls how large the change in the results must be between iterations for an equation to be considered to have diverged. |
|
The solution method to use. The possible values are AITKENS (for the Aitkens delta-squared method) and GAUSS (for the Gauss-Seidel method). |
|
A value that is used in testing for convergence. It controls how closely the results must match between iterations for an equation to be considered to have converged. |
A number of options effect compilation. These options are listed in Table A-8.
Table A-8 Compilation Options
Statement | Description |
---|---|
|
An option that specifies whether you want Oracle OLAP to send to the current outfile non-fatal messages during execution of the COMPILE command. |
|
An option that controls whether Oracle OLAP records a warning message in the current outfile when a compilable object, such as an OLAP DML program or a model, is being compiled automatically. |
|
A read-only option that is the value of the workspace name that Oracle OLAP uses when it replaces occurrences of the THIS_AW keyword to create a qualified object name. |
A number of options determine how errors are handled and what happens during debugging. These options are listed in Table A-9, "Error Handling Options" and Table A-10, "Debugging Options".
Table A-9 Error Handling Options
Statement | Description |
---|---|
|
An option that controls whether the value of the ERRORTEXT option contains the name of the error (that is, the value of the ERRORNAME option) as well as the text of the error message. |
|
An option that contains the name of the first error that occurs when you execute a program or when you execute an OLAP DML statement. |
|
An option that contains the text of the first error message that occurs when you execute a program or a statement. |
|
An option that determines whether or not an error is signaled on attempted access of a variable for which read or write permission is denied by a PERMIT command. |
|
Specifies the action to be taken when a model equation diverges or a block fails to converge. The possible values are STOP, CONTINUE, and DEBUG. |
|
When a program, model, or input file is executing, an option that controls whether Oracle OLAP records, in the current outfile, the line that caused an error. |
|
An option that specifies the behavior of Oracle OLAP when an error is reached when reading from a file using the INFILE command |
|
An option that determines if Oracle OLAP displays long error messages as multiple lines with each line being 72 characters in length. |
Table A-10 Debugging Options
Statement | Description |
---|---|
|
An option that controls whether OLAP DML programs provided as OLAP DML statements are traced when the PRGTRACE option is set to YES. |
|
An option that determines whether each line of a program is recorded in the current outfile or in a debugging file during execution of the program. |
|
An option that controls whether each equation in a model is recorded in a file during execution of the model. |
|
Specifies the action to be taken when a model equation diverges or a block fails to converge. The possible values are STOP, CONTINUE, and DEBUG. |
A number of options are available to you when embedding SQL. These options are listed in Table A-11.
Table A-11 Embedded SQL Options
Option | Description |
---|---|
|
An option that contains the maximum number of records retrieved from an Oracle relational database at one time. |
|
(Read-only) An option that contains the value returned by the Oracle RDBMS after the most recently attempted SQL operation. |
|
(Read-only) After the Database reports an error and SQLCODE has a nonzero value, an option that contains the text that explains the problem. |
|
An option that determines whether error messages are sent to the current output file. |
A number of options are important during file read and write operations. These options are listed in Table A-12.
Table A-12 File Reading and Writing Options
Statement | Description |
---|---|
|
An option that determines whether or not input lines and error messages should be echoed to the current outfile. |
|
An option that specifies the behavior of Oracle OLAP when an error is reached when reading from a file using the INFILE command |
|
An option that contains the type of escape that is produced by the INFILE keyword of the CONVERT function. |
|
(Read-only) An option that contains the file unit number of the current OUTFILE destination, set by the last OUTFILE command. |
A number of options determine how EIF files are imported and exported. These options are listed in Table A-13, "EIF Options".
Table A-13 EIF Options
Statements | Description |
---|---|
|
An option that contains a list of directory objects that identify the locations where EIF extension files should be created. |
|
An option that contains a list of the names of all the objects imported by the most recent IMPORT (from EIF) command. |
|
An option that controls the structure of the extension of EIF overflow (extension) file names. |
|
An option that contains a list of the types of objects that are contained in the list produced by the EIFNAMES option. |
|
An option that controls the frequency of updates when you are using the IMPORT (from EIF) command with its UPDATE keyword. |
|
Used with the EXPORT (to EIF) and IMPORT (from EIF) commands, an option that specifies the EIF version when copying data between different versions of Express Server or Oracle OLAP. |
A number of options effect reports created using the OLAP DML. These options are listed in Table A-14, "Report Options".
Table A-14 Report Options
Statement | Description |
---|---|
|
An option that specifies the number of blank lines for the bottom margin of output pages. |
|
An option that controls the default width of data columns in report output. |
|
An option that controls the use of a character to separate thousands and millions in numeric output. |
|
(Read-only) An option that records the character that is used as the decimal marker in output. |
|
An option that controls the number of decimal places that are shown in numeric output. |
|
An option that controls the default width of the label column in reports. |
|
An option that contains the current line number of the output. |
|
(Read-only) An option that contains the number of lines left on the current page. |
|
An option that specifies the line size within which the STDHDR program centers the standard header. |
|
An option that controls the spelling that is used for NA values in output. |
|
(Read-only) An option that contains the text that is used for FALSE Boolean values in the output of OLAP DML statements. |
|
An option that contains the current page number of output. |
|
An option that contains the name of a program or the text of a statement to be executed at the beginning of each page of output. |
|
An option that contains the size of a page of output. |
|
An option that controls the production of paged output in Oracle OLAP. |
|
An option that controls whether negative numbers are represented in output with parentheses or a minus sign. |
|
(Read-only) An option that contains the character that is used as the thousands group marker in output. |
|
An option that defines the number of blank lines for the top margin of output pages, above the running page heading when PAGING is set to YES. |
|
(Read-only) An option that specifies the text that is used for TRUE Boolean values in the output of OLAP DML statements. |
|
An option that controls suppresses report rows with numeric values that are all NAs or all zeros or would be represented as zeros. |
|
An option that specifies the default text that is used for representing numeric zero values in output produced by the HEADING, REPORT, and ROW commands. |
Table A-15, "NA and Nonexistent Value Options" lists the options that control how NA
values are treated.
Table A-15 NA and Nonexistent Value Options
Statement | Description |
---|---|
|
An option that controls whether NA values are considered as input to aggregation functions. |
|
An option that controls how NA values are treated in arithmetic operations with the + (plus) and - (minus) operators. |
|
An option that controls the spelling that is used for NA values in output. |
|
An option that determines how Oracle OLAP behaves when a list of values in a LIMIT command, a LIMIT function, or a QDR contains a nonexistent value. |
|
An option that controls the ability of a formula or $NATRIGGER expression to call itself. |
|
An option that specifies the maximum number of $NATRIGGER property expressions that Oracle OLAP can execute simultaneously. |
|
An option that determines whether Oracle OLAP permanently replaces NA values in the cells of a variable with the value of the $NATRIGGER property expression that is set for the variable. |
A number of options determine how date and time values are handled. These options are listed in Table A-16, "Date and Time Options".
Table A-16 Date and Time Options
Statement | Description |
---|---|
|
Determines whether weeks should be aligned with the actual calendar year. |
|
Specifies the template used for displaying |
|
Contains three characters that indicate the intended order of the month, day, and year components of the |
|
Specifies the number of characters to use for abbreviations of day names that are stored in the DAYNAMES option. |
|
A list of valid names for the days of the week. The names are used to display values of type |
|
(Read-only) The number of seconds since January 1, 1970. |
|
The number of characters to use for abbreviations of month names that are stored in the MONTHNAMES option. |
|
The list of valid names for months that is used in handling values with a |
|
(Read-only) The number of seconds since January 1, 1970. |
|
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. |
|
The specific 100-year period associated with years that are read or displayed using a two-digit abbreviation. |
A number of options determine how Oracle OLAP handles numeric expressions. These options are listed in Table A-17, "Numeric Options".
Table A-17 Numeric Options
Option | Description |
---|---|
|
Controls the result of arithmetic operations that produce out-of-range numbers. Decimal numbers are stored as a mantissa and an exponent. Decimal overflow occurs when the result of a calculation is very large and can no longer be represented by the exponent portion of the decimal representation. |
|
Controls the result of division by zero. |
(Set only) Options that specify values used by RANDOM when computing random numbers. Typically, you only set values for these options when you are developing and debugging your application programs. |
|
|
A flag that allows or disallows any attempt to obtain a root of a negative number. |
Table A-18, "Options for Monitoring RANK" lists the read-only options that you can use to monitor the behavior of the RANK function.
Table A-18 Options for Monitoring RANK
Option | Description |
---|---|
|
Holds the number of calls that Oracle OLAP has made to the RANK function. |
|
Holds the number of values that Oracle OLAP has computed when executing the RANK function. |
|
Holds the number of sorts that have been triggered by the executing of the RANK function. |