Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

Part Number E17122-05
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF


The UNRAVEL function is used with an assignment statement to copy the values of an expression into the cells of a variable when the dimensions of the expression are different from the dimensions of the variable.

An assignment statement created using an assignment statement assigns the values obtained from UNRAVEL by looping over the status of the dimensions of the target variable. The first dimension listed in the variable's definition varies the fastest. UNRAVEL obtains the values of the expression in the same way, looping over the status of the dimensions of the expression with the first dimension varying the fastest. You can alter the order in which UNRAVEL obtains its values by specifying the dimensions over which to loop.

Return Value

The data type returned by UNRAVEL is the data type of the values specified by the expression.


UNRAVEL(expression [dimension1...])



The expression whose values are to be copied.


Specifies one or more dimensions over which to loop; the dimension specified first varies fastest as the data is unraveled.

Specifying dimensions in UNRAVEL overrides the default looping order and the extent to which the expression is unraveled. By default, unraveling extends through all the dimensions of the expression. However, when you specify some but not all the dimensions of the expression, any dimensions you have not specified do not unravel. Instead, the unraveled values include only the first value of each of the omitted dimensions.

Usage Notes

Performance Tip for Unraveling Variables Dimensioned by Composites

By default, when UNRAVEL loops over a composite, it sorts the composite values according to the current order of the values in the composite's base dimensions. The task of sorting requires some processing time, so when variables are large, performance can be affected. When your variable is very large, and you are more concerned about performance than about the order in which UNRAVEL output is produced, you can set the SORTCOMPOSITE option to NO.

Moving Worksheet Data

One common use of UNRAVEL is to move data from a worksheet to a variable, because the worksheet usually does not have the same dimensions as the variable. See Example 8-157, "Copying Data from a Worksheet to a Variable".

Filling Extra Target Cells

When there are still more cells in the target for the assignment statement (created using an assignment statement) to fill after it has used the last value from the expression, UNRAVEL starts over at the first value again.

Setting Status

Since the order in which unraveled values are assigned depends on the current status of the dimensions of both the variable and the expression, ensure that the appropriate LIMIT commands have been given so that the cells match up correctly.

Assigning Data Values

See SET for information on how data values are assigned.


Example 8-157 Copying Data from a Worksheet to a Variable

In an analytic workspace, you have imported some product price data from a spreadsheet into a worksheet. You now want to transfer that data to a variable called newprice. You can produce a report of a worksheet, called pricedata, with these statements.

LIMIT wksrow TO 1 TO 6
LIMIT wkscol TO 1 2 3
REPORT pricedata

This is the report.

WKSROW             1          2          3
-------------- ---------- ---------- ----------
             1            Jan95      Jan96
             2 Tents          191.39     194.00
             3 Canoes         279.92     300.00
             4 Racquets        83.34      85.00
             5 Sportswear     107.90     110.00
             6 Footwear       183.18     195.00

As you can see, row 1 contains month labels, while column 1 contains product labels. The variable newprice is dimensioned by month and product, as shown in its definition.

DEFINE newprice VARIABLE DECIMAL <month product>
LD Wholesale Unit Selling Price

Even though the worksheet has different dimensions (wkscol and wksrow) than newprice, the data contained in it is well organized for transferring to the variable.

However, you do not want to take data from all the rows and columns in the worksheet, so limit wkscol and wksrow to the rows and columns that contain the price data itself.

LIMIT wkscol TO 2 3
LIMIT wksrow TO 2 TO 6

Also, you only want to set values into the variable newprice for January 1995 and January 1996. So first limit month to these values, then type an assignment statement using UNRAVEL to move the values from the worksheet to the variable.

LIMIT month TO 'Jan95' 'Jan96'
newprice = UNRAVEL(pricedata)

You do not have to specify dimensions in the UNRAVEL function because wkscol is the fastest varying dimension. Consequently, both months unravel for the first product, then both months for the second product. Since the fastest-varying dimension of the variable is month, SET assigns values to the variable in the same order.

A report of newprice looks like this.

PRODUCT          Jan95      Jan96
-------------- ---------- ----------
Tents              191.39     194.00
Canoes             279.92     300.00
Racquets            83.34      85.00
Sportswear         107.90     110.00
Footwear           183.18     195.00