PROGRAM

The PROGRAM command enters completely new contents into a new or existing program. When the program already has lines of code, Oracle OLAP overwrites them.

PROGRAM assigns contents to the most recently defined or considered program object (see the DEFINE PROGRAM and CONSIDER commands).

See also:

For a discussion of writing, compiling, and debugging OLAP DML programs, see Chapter 5, " OLAP DML Programs".

Syntax

PROGRAM [contents]

Arguments

contents

A text expression that is the OLAP DML statements that are the lines of the program. You can use most OLAP DML statements within a program. For brief descriptions of statements that manage program flow-of-control and other traditional programming tasks, see Table A-23, "Statements Used Only in OLAP DML Programs" and Table A-24, "Statements Used Primarily in OLAP DML Programs". For a discussion of writing, compiling, and debugging OLAP DML programs, see Chapter 5, " OLAP DML Programs".

The maximum number of lines you can have in a program is 4,000. Separate program lines with newline delimiters (\n), or use the JOINLINES function as shown in "Program On the Fly".

Examples

Example 19-39 User-Defined Function with Arguments

Suppose your analytic workspace contains a variable called units.plan, which is dimensioned by the product, district, and month dimensions. The variable holds INTEGER data that indicates the number of product units that are expected to be sold.

Suppose also that you define a program namedunits_goals_met. This program is a user-defined function. It accepts three dimension-value arguments that specify a given cell of the units.plan variable, and it accepts a fourth argument that specifies the number of units that were actually sold for that cell. The program returns a Boolean value to the calling program. It returns YES when the actual figure comes up to within 10 percent of the planned figure; it returns NO when the actual figure does not.

The definition of the units_goals_met program is follows.

DEFINE units_goal_met PROGRAM BOOLEAN
LD Tests whether actual units met the planned estimate
"Program Initialization
ARGUMENT userprod  TEXT
ARGUMENT userdist  TEXT
ARGUMENT usermonth TEXT
ARGUMENT userunits INTEGER
VARIABLE answer boolean
TRAP ON errorlabel
PUSH product district month
"Program Body
LIMIT product TO userprod
LIMIT district TO userdist
LIMIT month TO usermonth
IF (units.plan - userunits) / units.plan GT .10
   THEN answer = NO
   ELSE answer = YES
"Normal Exit
POP product district month
RETURN answer
"Abnormal Exit
errorlabel:
POP product district month
SIGNAL ERRORNAME ERRORTEXT
END

To execute the units_goal_met program and store the return value in a variable called success, you can use an assignment statement (SET).

success = units_goal_met('TENTS' 'BOSTON' 'JUN96' 2000)

Example 19-40 Program On the Fly

This example creates a flexible report program "on the fly" to avoid the inefficiencies of a more conventional program using ampersand substitution. The conventional program would contain the following loop.

FOR &dimname
   ROW &dimname &varname

To avoid ampersand substitution, define a program, for example, STANDARDREP, and leave it without any code in it, or with code that can be discarded. Then in your report program, insert lines such as the following.

DEFINE myreport PROGRAM
LD Program to produce my report
PROGRAM
ARGUMENT dimname TEXT
ARGUMENT varname TEXT
...
CONSIDER standardrep
PROGRAM JOINLINES(JOINCHARS('FOR ', dimname) - 
   JOINCHARS('   ROW ', dimname, ' ', varname) )
COMPILE standardrep
standardrep
...

Example 19-41 Program from an Input File

This example presents the text of a simple program that is in an ASCII disk file called salesrep.inf. The first line in the file defines the program, the second line contains the PROGRAM command, and the subsequent lines provide the lines of the program.

DEFINE salesrep PROGRAM
PROGRAM
PUSH month product district
TRAP ON haderror
LIMIT month TO FIRST 3
LIMIT product TO FIRST 3
LIMIT district TO ALL
REPORT grandtotals sales
haderror:
POP month product district
END

To include the salesrep program in your analytic workspace, you can execute the following statement.

INFILE 'salesrep.inf'

You can create an input file from an existing program using the OUTFILE command

Example 19-42 Using OLAP Worksheet Instead of the PROGRAM Command

When you use OLAP Worksheet to create a new program, you can use the EDIT command to display an Edit window where you can enter the contents. For example, use the following statements to define a new program named salesrep and display it in an Edit window.

DEFINE salesrep PROGRAM
EDIT salesrep