---

VARIABLE

Within an OLAP DML program, the VARIABLE command declares a local variable or valueset for use within that program. A local variable cannot have any dimensions and exists only while the program is running.

Syntax

VARIABLE name {datatype|dimension|VALUESET dim}

Arguments

name

The name for the local variable or valueset. When you use the same name as an existing analytic workspace object, the local variable or valueset takes precedence over the analytic workspace object. After you assign a value to the variable or valueset, its value will be available within the program where the VARIABLE command occurs. You name a variable or valueset according to the rules for naming analytic workspace objects (see the DEFINE command).

datatype

The data type of the variable, which indicates the kind of data to be stored. You can specify any of the data types that are listed and described in the DEFINE VARIABLE entry. Also, when you want to the program to be able to receive an argument without converting it to a specific data type, you can also specify WORKSHEET for the data type.

dimension

Indicates that name is a relation variable, which holds a single value of the specified dimension. The variable can hold a value of the dimension or a position (integer) of the specified dimension. Assigning a value that does not currently exist in the dimension causes an error.

VALUESET dim

Indicates that name is a valueset. Dim specifies the dimension for which the valueset holds values.

Notes

Persistence of a Local Variable

A local variable or valueset exists only while the program that specified it is running. When the program terminates, the variable or valueset ceases to exist and its value is lost. A program can terminate when a RETURN statement, SIGNAL command, or the last line of the program executes. When the program calls another program, the original program is temporarily suspended and the variable or valueset does exist when the called program ends and control returns to the original program. A program that calls itself recursively has separate local variable or valuesets for each running copy of the program.

Declarations at the Start Of A Program

You must specify all your local variables or valuesets at the beginning of a program, before any executable statements.

Initial Value

The value of a local variable or valueset is initially NA.

Duplicate Names

When you give a local variable or valueset the same name as an analytic workspace object, Oracle OLAP assumes you are referring to the local variable or valueset within the program. The analytic workspace object has priority only when the statement requires an analytic workspace object as an argument.

Although the OBJ and EXISTS functions expect an analytic workspace object as an argument, you can use a local text variable or valueset to specify the name of an object.

Formulas and Models

You cannot use local variables or valuesets in a formula or model.

EXPORT and IMPORT Commands

In a program, you can use the EXPORT (to EIF) command to store the value of a local variable or valueset in an EIF file. You must use the AS keyword to give the variable or valueset an analytic workspace object name. The name can be the same as the name of the local variable or valueset. When you use IMPORT (from EIF) to retrieve the value, it is stored as an analytic workspace object. You cannot import the value into a local variable or valueset.

Examples

Example 24-33 Saving a File Unit Number

Suppose you want to write a program to read data from an input file with Data Reader statements. First you need to open the file and save the value of the file unit number assigned to it. At the beginning of the program you can specify a local variable called unit to hold the file unit number.

DEFINE read.file PROGRAM
LD Read monthly sales data into the analytic workspace
PROGRAM
VARIABLE unit INTEGER
TRAP ON error
unit = FILEOPEN('sales.data' READ)
...

Example 24-34 Returning a Dimension Value from a Program

Suppose you want to write a program that analyzes sales for various districts and returns the name of the district in which sales were highest. For the purpose of analysis, the program defines a local variable to hold the district name. When the program ends, it returns the value of the local variable.

DEFINE highsales PROGRAM
PROGRAM
VARIABLE districtname district
... "(statements that find the highest district)
RETURN districtname
END