OLAP DML Commands, Functions, and Programs by Category

The OLAP DML provided the following categories of commands, functions, and programs:

Session Statements
Workspace Object Definition Statements
Statements for Managing Analytic Workspaces
Statements for Managing Objects When in Multiwriter Mode
Data Type Conversion
Assignment Statements
Text Functions
Date and Time Functions
Numeric Functions
Forecast and Regression Statements
Aggregation Statements
Allocation Statements
Workspace Object Operation Statements
Dimension and Composite Operation Statements
Formula Statements
Modeling Statements
Programming Statements
File Reading and Writing Statements
Reporting Statements
Statements Related to Using OLAP _TABLE

Session Statements

Table A-19, "General System Statements" lists the OLAP DML functions and commands that you use to find out information about your session.

Table A-19 General System Statements

Statement	Description
CDA	Identifies or changes the current directory object for your session.
EVERSION	Returns a text value that specifies the internal Oracle OLAP build number.
LOG command	Starts or stops the recording of a session to a disk file.
RECAP	Sends statements that were previously entered during the current session to the current outfile or to a file that you specify.
REDO	Re-executes a statement that you entered earlier in your session.
REEDIT	Enables you to edit a statement that you entered earlier in your session.
RESERVED	Returns a list of reserved words in the OLAP DML, or indicates whether or not a word that you specify is reserved in the OLAP DML.
SYSDATE	Returns the current date and time in the format specified by the NLS_DATE_FORMAT option.
SYSINFO	Provides information about the Oracle user for the current session.
SYSTEM	Identifies the platform on which Oracle OLAP is running.

Workspace Object Definition Statements

Table A-20, "Workspace Object Data Definition Statements" lists the OLAP DML statements that you use to create basic definitions of analytic workspace objects and to change and view analytic workspace object definitions. Table A-21, "OLAP DML Statements for Extending Object Definitions" lists the OLAP DML statements that you use to extend the basic definitions created using a DEFINE statement.

Table A-20 Workspace Object Data Definition Statements

Statement	Description
CHGDFN	Changes certain aspects of the definitions of certain objects.
CONSIDER	Identifies a definition as the current definition. This enables you to add a description, property, calculation specification, or trigger (event) to an object.
COPYDFN	Defines a new object in the analytical workspace and uses the same definition as a specified object in the current workspace or in an attached workspace.
DEFINE	Adds a new object to the analytic workspace.
DELETE	Deletes one or more objects from a workspace.
MOVE	Moves an object name to a new position in the NAME dimension of a workspace.
PERMITRESET	Causes the values of permission conditions to be reevaluated. Permission conditions consist of one or more Boolean expressions that designate the criteria used by PERMIT commands associated with an object.
RENAME	Changes the name of an object in an analytical workspace and updates associated objects.
VALSPERPAGE	Calculates the maximum number of values for a variable of a given width that will fit on one page. Pages are units of storage in the workspace.

Table A-21 OLAP DML Statements for Extending Object Definitions

Statement	Attribute Added	Object Extended
AGGMAP	aggregation specification	aggmap object
ALLOCMAP	allocation specification	aggmap object
EQ	calculation specification	formula
LD	long description	any object
MODEL	calculation specification	model
PERMIT	permissions	any object
PROGRAM	calculation specification	program
PROPERTY	property	any object
RELATION command	default relation	any dimensioned object
TRIGGER command	triggers (also called events)	any object
VNF	a template that controls the input and display format for values of the dimension	dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR

Statements for Managing Analytic Workspaces

Table A-22, "Statements for Creating and Managing Analytic Workspaces" lists the OLAP DML statements that you use to create and manage analytic workspaces.

Table A-22 Statements for Creating and Managing Analytic Workspaces

Statement	Description
AW command	Creates a new workspace; attaches a workspace to a session; deletes a workspace; detaches a workspace from a session; sets up a workspace for multiple segments; or sends to the current outfile a list of the active workspaces, along with their update status.
COMMIT	Executes a SQL COMMIT statement.
UPDATE	Moves analytic workspace changes from a temporary area to the database table in which the workspace is stored. The table is not saved until you execute a COMMIT command, either from Oracle OLAP or from SQL.

Statements for Managing Objects When in Multiwriter Mode

Table A-23, "Statements for Managing Objects When Attached in Multiwriter Mode" lists the OLAP DML statements that you use to manage objects when an analytic workspace is attached in multiwriter mode.

Table A-23 Statements for Managing Objects When Attached in Multiwriter Mode

Statement	Description
ACQUIRE	When attached in multiwriter mode, acquires and (optionally) resynchronizes the specified objects so that their changes can be updated and committed.
RELEASE	When attached in multiwriter mode, changes the access mode of the specified variables, relations, valuesets, or dimensions from read/write (acquired) access to read-only access.
RESYNC	When an analytic workspace is attached in multiwriter mode, drops private changes for the specified read-only objects and retrieves the data from the latest visible generations.
REVERT	When attached in multiwriter mode, drops all changes made to the specified objects since they were last updated, resynchronized, or acquired, or since the analytic workspace was attached.
WRITABLE	Returns `TRUE` when the user has WRITE permission for the object and `FALSE` when the user does not

Data Type Conversion

Table A-24, "Data Type Conversion Functions" lists the OLAP DML functions that you to populate variables and relations and to convert data from one data type to another.

Table A-24 Data Type Conversion Functions

Statement	Description
CHR	Converts an integer value (or any value that can be implicitly converted to an integer value) into a character.
CONVERT	Converts values from one type of data to another.
TCONVERT	Converts data from one dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR to another dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR. You can specify an aggregation method or an allocation method to use in the conversion.
TO_CHAR	Converts a date, number, or NTEXT expression to a TEXT expression in a specified format.
TO_DATE	Converts a formatted TEXT or NTEXT expression to a DATETIME value.
TO_NCHAR	Converts a TEXT expression, date, or number to NTEXT in a specified format.
TO_NUMBER	Converts a formatted TEXT or NTEXT expression to a number.

Assignment Statements

Table A-25, "Assignment Statements" lists the OLAP DML statements that you use to assign values to objects.

Table A-25 Assignment Statements

Statement	Description
SET	Assigns one or more values to a variable, relation, dimension surrogate, worksheet, valueset, or option. When an object has one or more dimensions, the SET command loops over the values in status for each dimension of the target object and assigns a data value to the corresponding cell of the target object
SET1	Assigns a single value to a variable, option, relation, or dimension surrogate. When an object has one or more dimensions, the SET1 command assigns the value to the object cell that is in current status.
MAINTAIN ADD	Adds new `TEXT`, `ID`, and `INTEGER` values to a non-concat dimension or a composite; or adds a new temporary calculated member to a dimension.
UNRAVEL	When used in conjunction with SET, copies the values of an expression into the cells of a variable when the dimensions of the expression are not the same as the dimensions of the variable.

Statements for Working with NA Values

Table A-26, "Statements for Working with NA Values" lists the OLAP DML statements that you use to work with NA values.

Table A-26 Statements for Working with NA Values

Statement	Description
CACHE	Within an aggregation specification, tells Oracle OLAP whether to cache or store `NA` values when a summary value calculates to `NA`
COALESCE	Returns the first non-`NA` expression in a list of expressions, or `NA` when all of the expressions evaluate to `NA`.
NAFILL	Returns the values of the source expression with any `NA` values appearing as the specified fill expression.
NVL	Replaces a `NA` value with a string.
NVL2	Returns one value when the value of a specified expression is not `NA`, or another value when the value of the specified expression is `NA`.

Text Functions

Within the general category of text functions, the OLAP DML statements can be grouped into the following subcategories:

General Character Functions
Byte Functions
Multiline Text Functions

General Character Functions

Table A-27, "General Character Functions" lists the OLAP DML statements that you use to manipulate text based on characters.

Table A-27 General Character Functions

Statement	Description
ASCII	Returns the decimal representation of the first character of an expression.
BLANKSTRIP	Removes leading or trailing blank spaces from text values.
CHANGECHARS	Changes one or more occurrences of a specified string in a text expression to another string.
EXTCHARS	Extracts a portion of a text expression using characters.
FINDCHARS	Returns the character position of the beginning of a specified group of characters within a text expression.
GREATEST	Returns the largest expression in a list of expressions.
INITCAP	Returns a specified text expression, with the first letter of each word in uppercase and all other letters in lowercase.
INSCHARS	Inserts one or more characters into a text expression.
INSTR	Searches a string for a substring using characters and returns the position in the string that is the first character of a specified occurrence of the substring.
JOINCHARS	Joins two or more text values, as characters, as a single line.
LEAST	Returns the smallest expression in a list of expressions.
LIKECASE	Controls whether the LIKE operator is case sensitive.
LIKEESCAPE	An escape character for the LIKE operator.
LOWCASE	Converts all alphabetic characters in a text expression into lowercase.
LPAD	Returns an expression, left-padded to a specified length with the specified characters; or, when the expression to be padded is longer than the length specified after padding, only that portion of the expression that fits into the specified length.
LTRIM	Removes characters from the left of a text expression, with all the leftmost characters that appear in another text expression removed.
MAXCHARS	The number of characters in the longest line of a multiline text expression. The result returned by MAXCHARS has the same dimensions as the specified expression.
NULLIF	Compares one expression with another and returns NA when the expressions are equal, or the base expression when they are not.
NUMCHARS	The number of characters in a text expression.
OBSCURE	Provides two mechanisms for encrypting a single-line text expression. Depending on the mechanism you use, OBSCURE can also restore the encrypted value to its original form.
REMCHARS	Removes one or more characters from a text expression and returns the value that remains.
REPLCHARS	Replaces one or more characters in a text expression.
RPAD	Returns an expression, right-padded to a specified length with the specified characters; or, when the expression to be padded is longer than the length specified after padding, only that portion of the expression that fits into the specified length.
RTRIM	Removes characters from the right of a text expression, with all the rightmost characters that appear in another text expression removed.
SUBSTR	Returns a portion of string, beginning at a specified character position, and a specified number of characters long.
TEXTFILL	Reformats a text value to fit compactly into lines of a specified width, regardless of its current format.
TRIM	Removes leading or trailing characters (or both) from a character string.
UPCASE	Converts all alphabetic characters in a text expression into uppercase.

Byte Functions

Table A-28, "Byte Functions" lists the OLAP DML statements that you use to manipulate text based on bytes.

Table A-28 Byte Functions

Statement	Description
CHANGEBYTES	Changes one or more occurrences of a specified string in a text expression to another string.
EXTBYTES	Extracts a portion of a text expression using bytes.
FINDBYTES	Returns the byte position of the beginning of a specified group of bytes within a text expression.
INSBYTES	Inserts one or more bytes into a text expression.
INSTRB	Searches a string for a substring using bytes and returns the position in the string that is the first byte of a specified occurrence of the substring.
JOINBYTES	Joins two or more text values, as bytes, as a single line.
MAXBYTES	The number of bytes in the longest line of a multiline text expression.
NULLIF	The number of bytes in a text expression.
REMBYTES	Removes one or more bytes from a text expression and returns the value that remains.
REPLBYTES	Replaces one or more bytes in a text expression.
SUBSTRB	Returns a portion of string, beginning at a specified byte position, and a specified number of bytes long.

Multiline Text Functions

Table A-29, "MultiLine Text Functions" lists the OLAP DML statements that you use to manipulate multiline text.

Table A-29 MultiLine Text Functions

Statement	Description
CHARLIST	Transforms an expression into a multiline text value with a separate line for each value of the original expression.
EXTCOLS	Extracts specified columns from each line of a multiline text value.
EXTLINES	Extracts lines from a multiline text expression.
FILTERLINES	Applies a filter expression that you create to each line of a multiline text expression.
FINDLINES	Determines the position of one or more lines in a multiline text expression.
INLIST	Determines whether every line of a text value is a line in a second text value.
INSCOLS	Inserts into the columns of a multiline TEXT value all the columns of another TEXT value.
INSLINES	Inserts one or more lines into a multiline text expression.
JOINCOLS	Joins the corresponding lines of two or more multiline text values.
JOINLINES	Joins the values of two or more text expressions into a single multiline value.
MAXBYTES	The number of bytes in the longest line of a multiline text expression.
NUMLINES	The number of lines in each value of a text expression. The result returned by NUMLINES has the same dimensions as the specified expression.
REMCOLS	Removes specified columns from every line of a multiline TEXT value.
REMLINES	Removes one or more lines from a multiline TEXT expression and returns the value that remains.
REPLCOLS	Replaces some or all of the character columns in one multiline TEXT value with the columns of another.
REPLLINES	Replaces one or more lines in a multiline text expression.
SORTLINES	Sorts the lines in a multiline TEXT value.
UNIQUELINES	Removes duplicate lines in a multiline TEXT value and sorts the lines in ascending order.

Date and Time Functions

Table A-30, "Date and Time Functions" describes the OLAP DML date and time functions.

Table A-30 Date and Time Functions

Statement	Description
ADD_MONTHS	Returns the date that is the specified number of months after the specified date.
BEGINDATE	Returns the beginning date of the first time period for which an expression has a non-NA value.
DAYOF	Returns an `INTEGER` in the range of 1 through 7, giving the day of the week on which a specified date falls.
DDOF	Returns an `INTEGER` in the range of 1 through 31, giving the day of the month on which a specified date falls.
ENDDATE	Returns the ending date of the last time period for which an expression has a non-NA value.
ENDOF	Returns the last date of a time period in dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR.
LAST_DAY	Returns the last day of the month in which a particular date falls.
MAKEDATE	Returns the DATE value that corresponds to specified `INTEGER` values for a year, month, and day.
MMOF	Returns an `INTEGER` in the range of 1 to 12, giving the month in which a specified date falls. The result returned by MMOF has the same dimensions as the specified DATE expression.
MONTHS_BETWEEN	Calculates the number of months between two dates.
NEW_TIME	Converts a date and time from one time zone to another.
NEXT_DAY	Returns the date of the first instance of a particular day of the week that follows the specified date.
ROUND (for dates and time)	Returns a date and time value rounded to a specified date format; or, when you do not specify a format, the date and time value rounded to the nearest day.
STARTOF	Returns the starting date of a time period in a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR.
SYSDATE	Returns the current date and time in the format specified by the NLS_DATE_FORMAT option.
TCONVERT	Converts data from one dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR to another dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR.
TOD	Returns the current time of day in the form hh:mm:ss using a 24-hour format.
TODAY	Returns the current date as a DATE value.
TRIM	Returns the date and time value truncated to a specified date format; or, when you do not specify a format, returns the date and time value truncated to the nearest day.
VNF	Assigns a value name format (VNF) to the definition of a dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR.
WEEKOF	Returns an `INTEGER` in the range of 1 to 53, which gives the week of the year in which a specified date falls.
YYOF	Returns an `INTEGER` in the range of 1000 to 9999, giving the year in which a specified date falls.

Numeric Functions

Oracle OLAP offers the following types of numeric functions:

General numeric functions for typical mathematical processing (for example, ranking and finding logs and tangets). For listing. see Table A-31, "General Numeric Functions".
Financial functions. For listing. see Table A-32, "Financial Functions".
Statistical functions. For listing. see Table A-33, "Statistical Functions".
Time-series functions such as LAG and MOVINGMIN. For listing. see Table A-34, "Time-Series Functions".
Aggregation functions, such as COUNT and TOTAL. For listing. see Table A-35, "Aggregation Functions"

General Numeric Functions

Table A-31, "General Numeric Functions" lists the OLAP DML functions for calculation.

Table A-31 General Numeric Functions

Function	Description
ABS	Calculates the absolute value of an expression.
ANTILOG	Calculates the value of e (the base of natural logarithms) raised to a specific power.
ANTILOG10	Calculates the value of 10 raised to a specified power.
ARCCOS	Calculates the angle value (in radians) of a specified cosine.
ARCSIN	Calculates the angle value (in radians) of a specified sine.
ARCTAN	Calculates the angle value (in radians) of a specified tangent.
ARCTAN2	Returns a full-range (0 - 2 pi) numeric value indicating the arc tangent of a given ratio.
BITAND	Computes an AND operation on the bits of two `INTEGER` values.
CEIL	Returns the smallest whole number greater than or equal to a specified number.
COS	Calculates the cosine of an angle expression.
COSH	Calculates the hyperbolic cosine of an angle expression.
DECODE	Compares one expression to one or more other expressions and, when the base expression is equal to a search expression, returns the corresponding result expression; or, when no match is found, returns the default expression when it is specified, or NA when it is not.
EXP	Returns e raised to the nth power, where e equals 2.71828183....
FLOOR	Returns the largest whole number equal to or less than a specified number.
GREATEST	Returns the largest expression in a list of expressions. All expressions after the first are implicitly converted to the data type of the first expression before the comparison.
INSTRB	Calculates the integer part of a decimal number by truncating its decimal fraction.
LEAST	Returns the smallest expression in a list of expressions. All expressions after the first are implicitly converted to the data type of the first expression before the comparison.
LOG function	Computes the natural logarithm of an expression.
LOG10	Computes the logarithm base 10 of an expression.
MAX	Calculates the larger value of two expressions.
MIN	Calculates the smaller value of two expressions.
NULLIF	Compares one expression with another and returns NA when the expressions are equal, or the base expression when they are not.
REM	Returns the remainder after one numeric expression is divided by another.
ROUND (for numbers)	Returns the number rounded to the nearest multiple of a second number you specify or to the number of decimal places indicated by the second number.
SIGN	Returns a value that indicates if a specified number is less than, equal to, or greater than 0 (zero).
SIN	Calculates the sine of an angle expression. The result returned by SIN is a decimal value with the same dimensions as the specified expression.
SINH	Calculates the hyperbolic sine of an angle expression.
SQRT	Computes the square root of an expression.
TAN	Calculates the tangent of an angle expression.
TANH	Calculates the hyperbolic tangent of an angle expression.
TRUNC (for numbers)	Truncates a number to a specified number of decimal places.
WIDTH_BUCKET	Returns the bucket number into which the value of an expression would fall after being evaluated.

Financial Functions

Table A-32, "Financial Functions" lists the OLAP DML functions for financial calculation.

Table A-32 Financial Functions

Function	Description
DEPRDECL	Calculates the depreciation expenses for a series of assets. DEPRDECL uses the declining balance method to depreciate the assets over the specified lifetime of the assets.
DEPRDECLSW	Calculates the depreciation expenses for a series of assets. DEPRDECLSW uses a variation on the declining balance method to depreciate assets over the specified lifetime of the assets.
DEPRSL	Calculates the depreciation expenses for a series of assets. DEPRSL uses the straight-line method to depreciate the assets over the specified lifetime of the assets.
DEPRSOYD	Calculates the depreciation expenses for a series of assets. DEPRSOYD uses the sum-of-years'-digits method to depreciate the assets over the specified lifetime of the assets.
FINTSCHED	Calculates the interest portion of the payments on a series of fixed-rate installment loans that are paid off over a specified number of time periods.
FPMTSCHED	Calculates a payment schedule (principal plus interest) for paying off a series of fixed-rate installment loans over a specified number of time periods.
GROWRATE	Calculates the growth rate of a time-series expression, based on the first and last values of the series.
IRR	Computes the internal rate of return associated with a series of cash flow values. Each value of the result is calculated to be the discount rate for a period that makes the net present value of the corresponding cash flows equal to zero.
NPV	Computes the net present value of a series of cash flow values.
VINTSCHED	Calculates the interest portion of the payments on a series of variable-rate installment loans that are paid off over a specified number of time periods.
VPMTSCHED	Calculates a payment schedule (principal plus interest) for paying off a series of variable-rate installment loans over a specified number of time periods.

Statistical Functions

Table A-33, "Statistical Functions" lists the OLAP DML functions for statistical calculation.

Table A-33 Statistical Functions

Statement	Description
CATEGORIZE	Groups the values of a numeric expression into categories.
CORRELATION	Returns the correlation coefficients for the pairs of data values in two expressions.
NORMAL	Returns a random value from a normal distribution with a specified mean and standard deviation. The result returned by NORMAL is dimensioned by all the dimensions of the mean and standard deviation expressions.
RANDOM	Produces a number that is randomly distributed between specified low and high boundaries.
STDDEV	Calculates the standard deviation of the values of an expression.

Time-Series Functions

Table A-34, "Time-Series Functions" lists the OLAP DML time-series functions.

Table A-34 Time-Series Functions

Function	Description
CUMSUM	Computes cumulative totals over a dimension.
LAG	Returns the values of a dimensioned variable or expression at a specified offset of a dimension prior to the current value of that dimension.
LAGABSPCT	Returns the percentage difference between the value of a dimensioned variable or expression at a specified offset of a dimension prior to the current value of that dimension and the current value of the dimensioned variable or expression.
LAGDIF	Returns the difference between the value of a dimensioned variable or expression at a specified offset of a dimension prior to the current value of that dimension and the current value of the dimensioned variable or expression.
LAGPCT	Returns the percentage difference between the value of a dimensioned variable or expression at a specified offset of a dimension prior to the current value of that dimension and the current value of the dimensioned variable or expression.
LEAD	Returns the values of a dimensioned variable or expression at a specified offset of a dimension subsequent to the current value of that dimension.
MOVINGAVERAGE	Computes a series of averages for the values of a dimensioned variable or expression over a specified dimension. For each dimension value in status, MOVINGAVERAGE computes the average of the data in the range specified, relative to the current dimension value.
MOVINGMAX	Returns a series of maximum values of a dimensioned variable or expression over a specified dimension. For each dimension value in status, MOVINGMAX searches the data for the maximum value in the range specified, relative to the current dimension value.
MOVINGMIN	Returns a series of minimum values for the values of a dimensioned variable or expression over a specified dimension. For each dimension value in status, MOVINGMIN searches the data for the minimum value in the range specified, relative to the current dimension value.
MOVINGTOTAL	Computes a series of totals for the values of a dimensioned variable or expression over a specified dimension. For each dimension value in status, MOVINGTOTAL computes the total of the data in the range specified, relative to the current dimension value.

Aggregation Functions

Table A-35, "Aggregation Functions" lists the OLAP DML aggregation functions. The OLAP DML also provides an aggmap object that you can use to aggregate data, see "Aggregation Statements" for a list of related OLAP DML statements.

Table A-35 Aggregation Functions

Statements	Description
ANY	Returns YES when any values of a Boolean expression are TRUE, or NO when none of the values are TRUE.
AVERAGE	Calculates the average of the values of an expression.
COUNT	Retrieves the number of TRUE values of a Boolean expression, or 0 (zero) if no values of the expression are TRUE.
EVERY	Returns YES when every value of a Boolean expression is TRUE, or NO if any value of the expression is FALSE.
LARGEST	Returns the largest value of an expression. You can use this function to compare numeric values or date values.
MEDIAN	Calculates the median of the values of an expression.
MODE	Returns the mode (the most frequently occurring value) of a numeric expression; or NA when there are no duplicate values in the data.
NONE	Returns YES when no values of a Boolean expression are TRUE; or NO when any value of the expression is true.
PERCENTAGE	Computes the percent of total for each value in a numeric expression.
SMALLEST	Returns the smallest value of an expression. You can use this function to compare numeric values or date values.
TCONVERT	Converts data from one dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR to another dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR. You can specify an aggregation method or an allocation method to use in the conversion.
TOTAL	Calculates the total of the values of an expression.

Forecast and Regression Statements

Within the general category of forecast and regression statements, the OLAP DML statements can be grouped in the following subcategories:

Simple forecasts and regressions
Forecasts and regressions using a forecasting context

Simple Forecasts and Regressions

Table A-36, "Statements for Simple Forecasts and Regressions" lists the OLAP DML that you use to calculate simple forecasts and regressions.

Table A-36 Statements for Simple Forecasts and Regressions

Statement	Description
FORECAST	Forecasts data by one of three methods: straight-line trend, exponential growth, or Holt-Winters extrapolation.
FORECAST.REPORT	A program that produces a standard report of a forecast generated using the FORECAST command.
INFO	Obtains information that has been produced by the FORECAST command or the REGRESS command.
REGRESS	Calculates a simple multiple linear regression or a weighted regression.
REGRESS.REPORT	A program that produces a standard report of a regression created using the REGRESS command.
SMOOTH	Computes a single or a double exponential smoothing of a numeric expression.

Statements for Forecasting Using a Forecasting Context

Table A-37, "Statements for Forecasting Using a Forecasting Context" lists the OLAP DML that you use to calculate a sophisticated forecast using a forecasting context. Typically, you use these statements in an OLAP DML program in the order in which they are listed.

Table A-37 Statements for Forecasting Using a Forecasting Context

Statement	Description
FCOPEN	Creates a forecasting context and returns a handle to this context.
FCSET	Sets the values of various parameters that determine the characteristics of the forecast.
FCEXEC	Executes a forecast based on the parameters options specified by the FCSET command for the forecast.
FCQUERY	Returns the results of a forecast created when the FCEXEC command executed.
FCCLOSE	Closes a forecasting context.

Aggregation Statements

Table A-38, "General Aggregation Statements" lists the OLAP DML statements that support data aggregation. The OLAP DML also provides the aggregation functions listed in Table A-35, "Aggregation Functions".

Table A-38 General Aggregation Statements

Statement	Description
AGGCOUNT	Retrieves the values of the Aggcount variable associated with the specified variable. Oracle OLAP use the Aggcount variable to store the non-`NA` counts of the number of leaf nodes that contributed to aggregate values calculated using one of the average operators.
AGGMAP	Marks the aggmap as an aggregation specification and enters or changes the aggregation specification.
AGGMAP ADD or REMOVE model	Adds or removes a model from a previously defined aggmap object of type AGGMAP.
AGGMAP SET	Specifies the default aggmap for a variable.
AGGMAPINFO	Returns information about the specification for an aggmap object in your analytic workspace.
AGGREGATE command	Calculates in the variable that is specified as PRECOMPUTE in the specified aggmapt.
AGGREGATE function	Calculates the data in the variable that is not specified as PRECOMPUTE in the specified aggmapt. Often used as the expression of a $NATRIGGER property.
AGGREGATION	Within a model, creates a custom aggregation.
AGGROPS	Returns the keywords for all of the aggregation operators that you can specifies in a RELATION (for aggregation) statement
ALLCOMPILE	A program that compiles every compilable object in your current analytic workspace, one at a time.
COMPILE	Generates compiled code for a compilable object, such as an OLAP DML program, formula, model, or aggmap without running it and saves the compiled code in the analytic workspace.
DEFINE AGGMAP	Creates a new aggmap object.
MAINTAIN ADD	Adds a new temporary calculated member as a custom aggregation to a dimension or adds new values to a non-concat dimension or a composite.
PARTITIONCHECK	Identifies whether an aggmap object is compatible with the partitioning specified by a partition template object.
TCONVERT	Converts data from one dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR to another dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR. You can specify an aggregation method or an allocation method to use in the conversion.

Allocation Statements

Table A-39, "General Allocation Statements" lists the OLAP DML statements that you use to allocate data.

Table A-39 General Allocation Statements

Statement	Description
DEFINE AGGMAP	Creates a new aggmap object.
ALLOCMAP	Marks an aggmap as an allocation specification and enters or changes an allocation specification.
AGGMAPINFO	Returns information about the specification for an aggmap object in your analytic workspace.
ALLOCATE	Allocates values into a variable based on the specification provided by an aggmap object.
ALLOCOPS	Returns the keywords for all of the allocation operators that you can specify in a RELATION (for allocation) statement, listed one name on each line in a multiline text value
TCONVERT	Converts data from one dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR to another dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR. You can specify an aggregation method or an allocation method to use in the conversion.

Workspace Object Operation Statements

Table A-40, "Workspace Object Operation Statements" lists the OLAP DML statements that you use for common workspace object operations.

Table A-40 Workspace Object Operation Statements

Statement	Description
LOAD	Loads the definition of an OLAP DML program, formula, or model into memory.
CLEAR	Deletes the data that you specify for one or more variables.
GROUPINGID	Populates a previously-defined variable with the grouping ids for the values of a hierarchical dimension.
HIERHEIGHT command	Populates a previously-defined relation with the values of a specified hierarchical dimension by level.
HIERHEIGHT function	Returns the value of a node at a specified level for the first value in the current status list of a hierarchical dimension.
LOAD	Loads the definition of an OLAP DML program, formula, or model into memory.
PERMIT	Controls access to analytic workspace objects by granting or denying read-only and read/write access permission for workspace objects and for specific values of dimensions and dimensioned objects; and by granting or denying permission to maintain dimensions and to change permission for workspace objects.
PERMITRESET	Causes the values of permission conditions to be reevaluated. Permission conditions consist of one or more Boolean expressions that designate the criteria used by PERMIT commands associated with an object.
VALSPERPAGE	Calculates the maximum number of values for a variable of a given width that will fit on one page. Pages are units of storage in the workspace.
WRITABLE	Returns `TRUE` when the user has WRITE permission for the object and `FALSE` when the user does not.

Dimension and Composite Operation Statements

Table A-41, "Dimension and Composite Operation Statements" lists the OLAP DML statements that you use to define the contents of dimensions and composites and to manipulate dimension status.

Table A-41 Dimension and Composite Operation Statements

Statement	Description
ALLSTAT	Sets the status of all dimensions in the current analytic workspace to all their values.
BASEDIM	Returns the name of the dimension from which the current value of a concat dimension comes.
BASEVAL	Returns the values of the base dimensions of a concat dimension. If a base dimension is a concat dimension, then the values of its base dimensions are returned, also.
HIERCHECK	Checks the parent relation of a hierarchical dimension to make sure it has no loops (that is, that no value is specified as its own ancestor or descendant in the parent relation).
INSTAT	Checks whether a dimension or dimension surrogate value is in the current status list or whether a dimension value is in a valueset.
ISVALUE	Tests whether a dimension or a composite has a specified value.
KEY	Returns the value of the specified base dimension for a value of a conjoint dimension or a composite.
LIMIT command	Sets the current status list of a dimension and its dimension surrogates, or assigns values to a valueset.
LIMIT function	Returns the dimension or dimension surrogate values that are currently in status.
LIMIT BASEDIMS	Sets the current status list of one or more base dimension of a composite, conjoint dimension, concat dimension, or a partition template; or assigns a value to one or more valuesets for a base dimensions
MAINTAIN	Adds non-concat dimension values (including temporary calculated members) and composite values; deletes non-concat dimension values and composite values; moves non-concat and concat dimension values; and rename and merges non-concat dimension values.
QUAL	Specifies a qualified data reference (QDR).
SORT command	Arranges the order of values in the current status list of a dimension or a dimension surrogate, or in a valueset.
SORT function	Returns the dimension or dimension surrogate values that result from a specified SORT command.
STATALL	Returns YES when default status is currently in effect for a given dimension (that is, when STATLIST would return ALL); or NO when default status is not currently in effect for a given dimension
STATDEPTH	Returns the number of status lists that Oracle OLAP has saved for a specified dimension.
STATEQUAL	Compares the status lists of a dimension.
STATFIRST	Returns the first value in the current status list of a dimension or a dimension surrogate, or in a valueset.
STATLAST	Returns the last value in the current status list of a dimension or a dimension surrogate, or in a valueset.
STATLEN	Returns the number of values in the current status list of a dimension or a dimension surrogate, or in a valueset.
STATLIST	Returns a list of all values in the current status list of a dimension or dimension surrogate, or in a valueset.
STATMAX	Returns the latest value in the current status list of a dimension or a dimension surrogate, or in a valueset.
STATMIN	Returns the earliest value in the current status list of a dimension or a dimension surrogate, or in a valueset.
STATRANK	Returns the position of a dimension or dimension surrogate value in the current status list or in a valueset.
STATUS	Sends to the current outfile the status of one or more dimensions, dimension surrogates, or valuesets, or the status of all dimensions in an analytic workspace.
STATVAL	Returns the dimension value that corresponds to a specified position in the current status list of a dimension or a dimension surrogate, or in a valueset.
TALLY	The number of values of a dimension that correspond to each value of one or more related dimensions.
VALUES	Returns the default status list or the current status list of a dimension or dimension surrogate, or it returns the values in a valueset.

Formula Statements

Table A-42, "Statements for Formulas" lists the OLAP DML statements that you use when working with formula objects.

Table A-42 Statements for Formulas

Statement	Description
DEFINE FORMULA	Creates a new formula object.
EQ	Specifies the expression to be calculated for a formula that has already been defined. Be sure to distinguish between the EQ statement and the EQ operator used to compare values of the same type.

Modeling Statements

Table A-43, "General Modeling Statements" lists the OLAP DML statements that you use to create and manipulate model objects.

Table A-43 General Modeling Statements

Statement	Description
DEFINE MODEL	Creates a new model object.
INFO	Obtains information that has been produced for a model in your analytic workspace.
MODEL	At the command level, adds contents to a model object. Within an aggmap, executes a predefined model.
MODEL.COMPRPT	Produces a report that shows how model equations are grouped into blocks.
MODEL.DEPRT	Produces a report that lists the variables and dimension values on which each model equation depends.
MODEL.XEQRPT	Produces a report about the execution of the model.

Programming Statements

Within the general category of programming, the OLAP DML statements can be grouped into the following subcategories:

Handling programs
Statements that are only used in programs
Statements that are primarily used in programs
Debugging programs
Creating and managing trigger programs

Additionally, you often use statements for forecasts, regression, reporting, importing and exporting data, embedding SQL within an OLAP DML program, and triggering the execution of programs when a particular OLAP DML program executes. For tables outlining these statements see "Forecast and Regression Statements" and "File Reading and Writing Statements", "Statements for Importing and Exporting Data", "Reporting Statements", and "Statements for Working with Startup and Trigger Programs".

Statements for Handling Programs

Table A-44, "Statements for Handling Programs" lists the OLAP DML statements that you use to hide, compile, and call programs.

Table A-44 Statements for Handling Programs

Statement	Description
ALLCOMPILE	Compiles every compilable object in your current analytic workspace, one at a time.
CALL	Invokes an OLAP DML program, and, when the program has arguments, passes these arguments to the called program.
COMPILE	Generates compiled code for a compilable object, such as an OLAP DML program, formula, model, or aggmap without running it and saves the compiled code in the analytic workspace.
DEFINE PROGRAM	Creates a new program object.
PROGRAM	Assigns contents to the most recently defined or considered OLAP DML program.
HIDE	Hides the text of a program, so that you cannot display it using the DESCRIBE command, the EDIT command, or the OBJ function. You can perform all other actions on the program, including executing, compiling, renaming, or exporting.
UNHIDE	Unhides the text of a program that has been made invisible by using the HIDE command.

Statement Used Only in Programs

Table A-45, "Statements Used Only in OLAP DML Programs" lists the OLAP DML statements that you can use only within the contents of an OLAP DML program.

Table A-45 Statements Used Only in OLAP DML Programs

Statement	Description
ARG	Lets you reference arguments passed to a program by returning one argument as a text value.
ARGCOUNT	Returns the number of arguments that were specified when the current program was invoked.
ARGFR	Lets you reference the arguments that are passed to a program by returning a group of one or more arguments, beginning with the specified argument number, as a single text value.
ARGS	Lets you reference the arguments that are passed to a program by returning all the arguments as a single text value.
ARGUMENT	Declares an argument that is expected by a program.
BREAK	Transfers program control from within a SWITCH, FOR, or WHILE statement to the statement immediately following the DOEND associated with SWITCH, FOR, or WHILE.
CALLTYPE	Returns a value that Indicates whether a program was invoked as a function, as a command, or by using the CALL command.
CONTINUE	Transfers program control to the end of a FOR or WHILE loop (just before the DO/DOEND statement), allowing the loop to repeat. You can use CONTINUE only within programs and only with FOR or WHILE.
DO ... DOENDs	Brackets a group of one or more statements. DO and DOEND are normally used to bracket a group of statements that are to be executed under a condition specified by an IF statement, a group of statements in a repeating loop introduced by FOR or WHILE, or the CASE labels for a SWITCH statement.
FOR	Specifies one or more dimensions whose status will control the repetition of one or more statements.
GOTO	Alters the sequence of statement execution within the program by indicating the next program statement to execute.
IF...THEN...ELSE Command	Executes one or more statements in a program if a specified condition is met. Optionally, it also executes an alternative statement or group of statements when the condition is not met.
RETURN	Terminates execution of a program prior to its last line. You can optionally specify a value that the program will return.
SIGNAL	Produces an error message and halts normal execution of the program. When the program contains an active trap label, execution branches to the label. Without a trap label, execution of the program terminates and, if the program was called by another program, execution control returns to the calling program.
SWITCH command	Provides a multipath branch in a program. The specific path taken during program execution depends on the value of the control expression that is specified with SWITCH.
TEMPSTAT	Limits the dimension you are looping over, inside a FOR loop or inside a loop that is generated by the REPORT command. Status is restored after the statement following TEMPSTAT. If a DO ... DOEND phrase follows TEMPSTAT, status is restored when the matched DOEND or a BREAK or GOTO statement is encountered.
TRAP	Causes program execution to branch to a label when an error occurs in a program or when the user interrupts the program. When execution branches to the trap label, that label is deactivated.
VARIABLE	Declares a local variable or valueset for use within a program. A local variable cannot have any dimensions and exists only while the program is running.
WHILE	Repeatedly executes a statement while the value of a Boolean expression remains TRUE.
END	Marks the end of the program contents.

Statements Used Primarily in Programs

Table A-46, "Statements Used Primarily in OLAP DML Programs" lists the OLAP DML statements that are used primarily in OLAP DML programs.

Table A-46 Statements Used Primarily in OLAP DML Programs

Statement	Description
ACROSS	Specifies a text expression that contains one or more statements to be executed in a loop.
CONTEXT command	Lets you create and use a context during your Oracle OLAP session. A context is a means of preserving object values. After you create a context, you can save the current status of dimensions and the values of options, single-cell variables, valuesets, and single-cell relations in the context. You can then restore some or all of the object values from the context.
CONTEXT function	Obtains information about object values that are saved in a context. You must first create the context with the CONTEXT command.
INFO (PARSE)	Obtains information that has been produced by the PARSE command.
PARSE	Parses a specified group of expressions.
POP	Restores the status of a dimension, the status of a valueset, or the value of an option or single-cell variable that was saved with a previous PUSH command.
POPLEVEL	Restores all values saved with PUSH commands that were executed since the last POPLEVEL command specifying the same marker.
PUSH	Saves the current status of a dimension, the status of a valueset, or the value of an option or single-cell variable.
PUSHLEVEL	Marks the start of a series of PUSH commands.
SLEEP	Suspends the operation of Oracle OLAP for at least the specified number of seconds.

Statements for Program Debugging

Table A-47, "OLAP DML Program Debugging Statements" lists the OLAP DML statements that you use to debug OLAP DML programs.

Table A-47 OLAP DML Program Debugging Statements

Statement	Description
BACK	Returns the names of all currently executing programs, listed one a line in a multiline text value.
DBGOUTFILE	Sends debugging information to a file.
MONITOR	Records data on the performance cost of each line in a specified OLAP DML program.
TRACKPRG	Tracks the performance cost of every OLAP DML program that runs while you have tracking turned on.

Statements for Working with Startup and Trigger Programs

Trigger programs and startup programs are programs that Oracle OLAP automatically executes when a particular OLAP DML statement executes. Table A-48, "Statements for Working with Startup and Trigger Programs" lists the OLAP DML statements that you can use to create and manage trigger programs.

Table A-48 Statements for Working with Startup and Trigger Programs

Statement	Description
CALLTYPE	Within an OLAP DML program, the CALLTYPE function indicates whether a program was invoked as a function, as a command, by using the CALL command, or triggered by the execution of an OLAP DML statement.
ONATTACH	A program that you create and that Oracle OLAP checks for by name when an AW ATTACH statement executes. Depending on the value returned by the program, Oracle OLAP executes the code within the program immediately after attaching the analytic workspace.
PERMIT_READ	A program that you create and that Oracle OLAP checks for by name when an AW ATTACH read-only statement executes. Depending on the value returned by the program, Oracle OLAP executes the code within the program after attaching the analytic workspace.
PERMIT_WRITE	A program that you create and that Oracle OLAP checks for by name when an AW ATTACH read/write statement executes. Depending on the value returned by the program, Oracle OLAP executes the code within the program after attaching the analytic workspace.
TRIGGER command	Associates a previously-created program to a previously-defined object and identifies the object event that automatically executes the program; or a disassociates a trigger program from the object.
TRIGGER function	Retrieves the event, subevent, or name of the object or analytic workspace that caused the execution of a TRIGGER_DEFINE program, a TRIGGER_DEFINE program, or any programs identified as triggers using the TRIGGER command.
TRIGGER_AFTER_UPDATE	A program that you create and that Oracle OLAP checks for by name when an UPDATE statement executes. When the program exists, Oracle OLAP executes the program after the UDPATE occurs.
TRIGGER_BEFORE_UPDATE	A program that you create and that Oracle OLAP checks for by name when an UPDATE statement executes. When the program exists, Oracle OLAP executes the program and then, depending on the value returned by the program (if any), either does nor does not update the workspace.
TRIGGER_DEFINE	A program that you create and that Oracle OLAP checks for by name when a DEFINE statement executes. When the program exists, Oracle OLAP executes the program and then, depending on the value returned by the program (if any), either does nor does not define the object.
TRIGGERASSIGN	Typically used in trigger program for an Assign event, the TRIGGERASSIGN statement replaces one assigned value.

File Reading and Writing Statements

Table A-49, "File Reading and Writing Statements" lists the OLAP DML statements that you use when reading data from files or to files.

Table A-49 File Reading and Writing Statements

Statement	Description
CDA	Identifies or changes the current directory object for your session.
FILECLOSE	Closes an open file. If the file has not been opened, an error occurs.
FILECOPY	Copies the contents of one file (the source file) to another file (the target file).
FILEDELETE	Deletes a file from the operating system disk space.
FILEERROR	Returns information about the first error that occurred when you are processing a record from an input file with the data reading statements FILEREAD and FILEVIEW.
FILEGET	Returns text from a file that has been opened for reading; or NA when FILEGET reaches the end of the file.
FILEMOVE	Changes the name or location of a file that you specify. The new file name may be the same or different from the original name.
FILENEXT	Makes a record available for processing by the FILEVIEW command.
FILEOPEN	Opens a file, assigns it a fileunit number (an arbitrary `INTEGER`), and returns that number.
FILEPAGE	Forces a page break in your output when PAGING is on.
FILEPUT	Writes data that is specified in a text expression to a file that is opened in WRITE or APPEND mode.
FILEQUERY	Returns information about a file.
FILEREAD	Reads records from an input file and processes data according to action statements that you specify.
FILESET	Sets the paging attributes of a specified fileunit.
FILEVIEW	In conjunction with the FILENEXT function, reads one record at a time of an input file, processes the data, and stores the data in Oracle OLAP dimensions and variables according to the descriptions of the fields.
GET	Requests input from the current input stream.
INFILE	Reads statement input from a specified file.
LISTFILES	Lists all the open files that can be referenced by the FILEQUERY function.
LOG command	Starts or stops the recording of a session to a disk file. All lines of input and output are recorded.
OUTFILE	Redirects the text output of statements to a file.
RECNO	Reports the current record number of a file opened for reading; or NA when Oracle OLAP has reached the end of the file.

Statements for Importing and Exporting Data

Table A-50, "Statements for Importing and Exporting Data" lists the OLAP DML statements that you use to import and export data.

Table A-50 Statements for Importing and Exporting Data

Statements	Description
EXPORT	Copies both data and object definitions from your workspace to an EIF file, or copies an OLAP DML worksheet object to a spreadsheet file.
IMPORT	Copies data from an EIF file, a text file, or a spreadsheet into an analytic workspace.
WKSDATA	Returns the data type of each individual cell in a worksheet.
SQL	Typically, used in a program to copy data to and from relational tables, passes instructions written in Structured Query Language (SQL) to the relational manager from Oracle OLAP.
SQLFETCH	Retrieve one or more rows of a column in a relational text

Reporting Statements

Table A-51, "Reporting Statements" lists the OLAP DML statements that you use to create simple reports.

Table A-51 Reporting Statements

Statement	Description
BLANK	Sends one or more blank lines to the current outfile.
COLVAL	Within a ROW command, ROW function, or REPORT command, returns a numeric value from a column to the left of the current column in the same row of a report.
HEADING	Produces titles and column headings for a report.
PAGE	Forces a page break in output when PAGING is set to YES.
REPORT	Produces output for one or more data expressions.
ROW command	Produces a line of data in cells, one after another in a single row.
ROW function	Returns a line of data in cells, one after another in a single row.
RUNTOTAL	Within a ROW command, ROW function, or REPORT command, returns the running total of an expression.
SHOW	Displays a single value of an expression.
STDHDR	Generates the standard Oracle OLAP heading at the top of every page of report output.
SUBTOTAL	Within a ROW command, ROW function, or REPORT command, returns the value of one of the subtotals accumulated in a report.
ZEROTOTAL	Within a ROW command, ROW function, or REPORT command, resets one or all subtotals of specified report columns to zero.

Statements Related to Using OLAP _TABLE

Table A-52, "Statements Related to OLAP_TABLE" lists the OLAP DML statements that support the use of the OLAP_TABLE function.

Table A-52 Statements Related to OLAP_TABLE

Statement	Description
FETCH	Specifies how analytic workspace data is retrieved for use in the relational table created by the OLAP_TABLE function which you use to access analytic workspace data using SQL.
GROUPINGID	Populates a previously-defined variable with the grouping ids for the values of a hierarchical dimension.
HIERHEIGHT command	Populates a previously-defined relation with the values of a specified hierarchical dimension by level.
LIMITMAPINFO	Returns the analytic workspace expression that a specified limit map uses to map data into a specified column of a relational table.