OLAP DML Options by Category

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

Options Related to Analytic Workspace Management

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
AWWAITTIME	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.
DEFAULTAWSEGSIZE	An option that specifies the default maximum segment size for an analytic workspace created in your database session.

Multi-Language Support Options

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
LOCK_LANGUAGE_DIMS	An option that specifies whether or not the value of the SESSION_NLS_LANGUAGE option can be changed.
SESSION_NLS_LANGUAGE	An option that is the name of the language used by Oracle OLAP throughout the session.
STATIC_SESSION_LANGUAGE	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.

Aggregation Options

A number of options can impact aggregation as outlined in Table A-5, "Aggregation Options".

Table A-5 Aggregation Options

Option	Description
MULTIPATHHIER	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.
POUTFILEUNIT	An option that identifies a destination for status information about an aggregation operation.
SESSCACHE	An option that controls whether Oracle OLAP uses the session cache to store variable data that has been aggregated on the fly.
VARCACHE	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.)

Allocation Options

A number of options effect allocation. These options are listed in Table A-6, "Allocation Options".

Table A-6 Allocation Options

Option	Description
ALLOCERRLOGFORMAT	An option that determines the contents and the formatting of the error log that you specify with the ERRORLOG argument to the ALLOCATE command.
ALLOCERRLOGHEADER	An option that determines the column headings for the error log that you specify with the ERRORLOG argument to the ALLOCATE command.
POUTFILEUNIT	An option that identifies a destination for status information about an allocation or aggregation operation. A file unit uniquely identifies the destination file.

Model Options

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
MODDAMP	For the Gauss-Seidel solution method, specifies a weighting factor that damps out oscillations between iterations.
MODERROR	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.
MODGAMMA	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.
MODINPUTORDER	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.
MODMAXITERS	The maximum number of iterations to perform in seeking a solution for a block.
MODOVERFLOW	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.
MODSIMULTYPE	The solution method to use. The possible values are AITKENS (for the Aitkens delta-squared method) and GAUSS (for the Gauss-Seidel method).
MODTOLERANCE	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.

Compilation Options

A number of options effect compilation. These options are listed in Table A-8.

Table A-8 Compilation Options

Statement	Description
COMPILEMESSAGE	An option that specifies whether you want Oracle OLAP to send to the current outfile non-fatal messages during execution of the COMPILE command.
COMPILEWARN	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.
THIS_AW	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.

Error and Debugging Options

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
ERRNAMES	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.
ERRORNAME	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.
ERRORTEXT	An option that contains the text of the first error message that occurs when you execute a program or a statement.
PERMITERROR	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.
MODERROR	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.
BADLINE	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.
INF_STOP_ON_ERROR	An option that specifies the behavior of Oracle OLAP when an error is reached when reading from a file using the INFILE command
WRAPERRORS	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
EXPTRACE	An option that controls whether OLAP DML programs provided as OLAP DML statements are traced when the PRGTRACE option is set to YES.
PRGTRACE	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.
MODTRACE	An option that controls whether each equation in a model is recorded in a file during execution of the model.
MODERROR	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.

SQL Embed Options

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
SQLBLOCKMAX	An option that contains the maximum number of records retrieved from an Oracle relational database at one time.
SQLCODE	(Read-only) An option that contains the value returned by the Oracle RDBMS after the most recently attempted SQL operation.
SQLERRM	(Read-only) After the Database reports an error and SQLCODE has a nonzero value, an option that contains the text that explains the problem.
SQLMESSAGES	An option that determines whether error messages are sent to the current output file.

File Reading and Writing Options

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
ECHOPROMPT	An option that determines whether or not input lines and error messages should be echoed to the current outfile.
INF_STOP_ON_ERROR	An option that specifies the behavior of Oracle OLAP when an error is reached when reading from a file using the INFILE command
ESCAPEBASE	An option that contains the type of escape that is produced by the INFILE keyword of the CONVERT function.
OUTFILEUNIT	(Read-only) An option that contains the file unit number of the current OUTFILE destination, set by the last OUTFILE command.

EIF Options

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
EIFEXTENSIONPATH	An option that contains a list of directory objects that identify the locations where EIF extension files should be created.
EIFNAMES	An option that contains a list of the names of all the objects imported by the most recent IMPORT (from EIF) command.
EIFSHORTNAMES	An option that controls the structure of the extension of EIF overflow (extension) file names.
EIFTYPES	An option that contains a list of the types of objects that are contained in the list produced by the EIFNAMES option.
EIFUPDBYTES	An option that controls the frequency of updates when you are using the IMPORT (from EIF) command with its UPDATE keyword.
EIFVERSION	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.

Report Options

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
BMARGIN	An option that specifies the number of blank lines for the bottom margin of output pages.
COLWIDTH	An option that controls the default width of data columns in report output.
COMMAS	An option that controls the use of a character to separate thousands and millions in numeric output.
DECIMALCHAR	(Read-only) An option that records the character that is used as the decimal marker in output.
DECIMALS	An option that controls the number of decimal places that are shown in numeric output.
LCOLWIDTH	An option that controls the default width of the label column in reports.
LINENUM	An option that contains the current line number of the output.
LINESLEFT	(Read-only) An option that contains the number of lines left on the current page.
LSIZE	An option that specifies the line size within which the STDHDR program centers the standard header.
NASPELL	An option that controls the spelling that is used for NA values in output.
NOSPELL	(Read-only) An option that contains the text that is used for FALSE Boolean values in the output of OLAP DML statements.
PAGENUM	An option that contains the current page number of output.
PAGEPRG	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.
PAGESIZE	An option that contains the size of a page of output.
PAGING	An option that controls the production of paged output in Oracle OLAP.
PARENS	An option that controls whether negative numbers are represented in output with parentheses or a minus sign.
THOUSANDSCHAR	(Read-only) An option that contains the character that is used as the thousands group marker in output.
TMARGIN	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.
YESSPELL	(Read-only) An option that specifies the text that is used for TRUE Boolean values in the output of OLAP DML statements.
ZEROROW	An option that controls suppresses report rows with numeric values that are all NAs or all zeros or would be represented as zeros.
ZSPELL	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.

Options for Working with Empty Cells and Nonexistent Values

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
NASKIP	An option that controls whether NA values are considered as input to aggregation functions.
NASKIP2	An option that controls how NA values are treated in arithmetic operations with the + (plus) and - (minus) operators.
NASPELL	An option that controls the spelling that is used for NA values in output.
LIMITSTRICT	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.
RECURSIVE	An option that controls the ability of a formula or $NATRIGGER expression to call itself.
TRIGGERMAXDEPTH	An option that specifies the maximum number of $NATRIGGER property expressions that Oracle OLAP can execute simultaneously.
TRIGGERSTOREOK	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.

Date and Time Options

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

Numeric Options

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
DECIMALOVERFLOW	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.
DIVIDEBYZERO	Controls the result of division by zero.
RANDOM.SEED.1 and RANDOM.SEED.2	(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.
ROOTOFNEGATIVE	A flag that allows or disallows any attempt to obtain a root of a negative number.

Options for Monitoring the Behavior of RANK

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
RANK_CALLS	Holds the number of calls that Oracle OLAP has made to the RANK function.
RANK_CELLS	Holds the number of values that Oracle OLAP has computed when executing the RANK function.
RANK_SORTS	Holds the number of sorts that have been triggered by the executing of the RANK function.