Specifying Program Contents

The content of a program consists of the following OLAP DML statements:

A PROGRAM statement that indicates the beginning of the program contents. (Omit when coding the specification in an Edit window of the OLAP Worksheet.)
(Optional) VARIABLE statements that define any local variables.
(Optional) ARGUMENT statements that declare arguments. (See "Passing Arguments" for more information.)
Additional OLAP DML statements that specify the processing you want performed. You can use almost any of the OLAP DML statements in a program. There are also some OLAP DML statements, such as flow-of-control statements, that are only used in programs.

Use the following formatting guidelines as you add lines to your program:
- Each line of code can have a maximum of 4000 bytes.
- To continue a single statement on the next line, place a hyphen (-) at the end of the line to be broken. The hyphen is called a continuation character.
- You cannot use a continuation character in the middle of a text literal.
- To write more than one statement on a single line, separate the statements with semicolon (;).
- Enclose literal text in single quotation marks ('). To include a single quotation mark within literal text, precede it with a backslash (\). To specify escape sequences, see "Escape Sequences".
- Precede comments with double quotation marks ("). You can place a comment, preceded by double quotation marks, either at the beginning of a line or at the end of a line, after some statements.
A final END statement that indicates the end of the contents of the program. (Omit when coding the specification in an Edit window of the OLAP Worksheet.)

Creating User-Defined Functions

One type of program that is commonly written is a user-define function that you can use in OLAP DML statements in much the same way as you use an OLAP DML function. A user-defined function is simply an OLAP DML program that returns a value. For an example of a user-defined function, see Example 9-43, "Passing an Argument to a User-Defined Function".

When you create a user-defined function, you use a DEFINE PROGRAM statement that includes the datatype and dimension arguments. Within the program, you include a RETURN statement that returns a value. The return expression in the program should match the data type that is specified in its definition. When the data type of the return value does not match the data type that is specified in its definition, then the value is converted to the data type in the definition.

User-defined functions can accept arguments. A user-defined function returns only a single value. However, when you supply an argument to a user-defined function in a context that loops over a dimension (for example, in a REPORT statement), then the function returns results with the same dimensions as its argument.

You must declare the arguments using an ARGUMENT statement within the program, and you must specify the arguments in parentheses following the name of the program.

Passing Arguments

Use ARGUMENT statements to declare both simple and complex arguments (such as expressions). ARGUMENT statement also make it convenient to pass arguments from one program to another, or to create your own user-defined functions since using these statements you can declare an argument of any data type, dimension, or valueset. Any ARGUMENT statements must precede the first executable line in the program. When you run the program, these declared arguments are initialized with the values you provided as arguments to the program. The program can then use these arguments in the same way it would use local variables.

Using Multiple Arguments

A program can declare as many arguments as needed. When the program is executed with arguments specified, the arguments are matched positionally with the declared arguments in the program. When you run the program, you must separate arguments with spaces rather than with commas or other punctuation. Punctuation is treated as part of the arguments. For an example of passing multiple arguments, see Example 9-44, "Passing Multiple Arguments".

Handling Arguments Without Converting Values to a Specific Data Type

Sometimes you want your OLAP DML program to be able to handle arguments without converting values to a specific data type. In this case, you can specify a data type of WORKSHEET in the ARGUMENT and VARIABLE statements that define the arguments and temporary variables for the program. You can use WKSDATA to determine the actual data type of the argument or variable.

Passing Arguments as Text with Ampersand Substitution

It is very common to pass a simple text argument to a program. However, there are some situations in which you might want to write more general programs or pass a more complicated text argument, such as an argument that is all of the data in one of the analytic workspace objects or the results of an expression. In these cases, you can pass the argument using a substitution expression. Passing an argument in this way is called ampersand substitution.

For the following types of arguments, you must always use an ampersand to make the appropriate substitution:

Names of workspace objects, such as units or product
Statement keywords, such as COMMA or NOCOMMA in the REPORT statement, or A or D in the SORT command

When you use ampersand substitution to pass the names of workspace objects to a program (rather than their values), the program has access to the objects themselves because the names are known to the program. This is useful when the program must manipulate the objects in several operations.

Note:

You cannot compile and save any program line that contains an ampersand. Instead, the line is evaluated at run time, which can reduce the speed of your programs. Therefore, to maximize performance, avoid using ampersand substitution when another technique is available.

For an example of using ampersand substitution to pass multiple dimension values, see Example 10-18, "Using Ampersand Substitution with LIMIT". For an example of using ampersand substitution to pass the text of an expression, see Example 9-46, "Passing the Text of an Expression". For an example of using ampersand substitution to pass object names and keywords, see Example 9-47, "Passing Workspace Object Names and Keywords".

Program Flow-of-Control

Like most programming languages, the OLAP DML has a number of statements that you can use to determine the flow-of-control within a program. However, you need to code explicit loops less frequently in an OLAP DML program because of the intrinsic looping nature of many OLAP DML statements.

Table 4-1, "Statements For Determining Flow-of-Control" lists OLAP DML flow-of-control statements. The looping characteristic of OLAP DML statements is discussed in "OLAP DML Statements Apply to All of the Values of a Data Object".

The OLAP DML contains the flow-of-control statements typically found in a programming language. Table 4-1, "Statements For Determining Flow-of-Control" lists these statements.

Table 4-1 Statements For Determining Flow-of-Control

Statement	Description
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.
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 ... DOEND statements	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 statement	Specifies one or more dimensions whose status will control the repetition of one or more statements.
GOTO statement	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.
OKFORLIMIT	An option that determines whether you can limit the dimension you are looping over within an explicit FOR loop.
RETURN statement	Terminates execution of a program prior to its last line. You can optionally specify a value that the program will return.
SIGNAL statement	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 statement	Limits the dimension you are looping over, inside a FOR loop or inside a loop that is generated by a REPORT statement. 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 statement	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.
WHILE statement	Repeatedly executes a statement while the value of a Boolean expression remains TRUE.

Preserving the Environment Settings

There are two types of environments:

Session environment. The dimension status, option values, and output destination that are in effect before a program is run make up the session environment.
Program environment. The dimension status, option values, and output destination that you use in a program make up the program environment.

Changing the Program Environment

To perform a task within a program, you often need to change the output destination or some dimension and option values. For example, you might run a monthly sales report that always shows the last six months of sales data. You might want to show the data without decimal places, include the text "No Sales" where the sales figure is zero, and send the report to a file. To set up this program environment, you can use the following statements in your program.

LIMIT month TO LAST 6
DECIMALS = 0
ZSPELL = 'No Sales'
OUTFILE monsales.txt

To avoid disrupting the session environment, the initialization section of a program should save the values of the dimensions and options that will be set in the program. At the end of the program, you can restore the saved environment, so that other programs do not need to be concerned about whether any values have been changed. In addition, when you have sent output to a file, then the exit sections should return the output destination to the default outfile.

Ways to Save and Restore Environments

The following suggestions let you save the environment of a program or a session:

When you want to save the current status or value of a dimension, a valueset, an option, or a single-cell variable that will be changed in the current program, then use PUSHLEVEL and PUSH statements. You can restore the current status values using POPLEVEL and POP statements.
When you want to save, access, or update the current status or value of a dimension, a valueset, an option, a single-cell variable, or a single-cell relation for use in the current session, then use a named context. Use the CONTEXT command to define the context.

Contexts are the most sophisticated way to save object values for use during a session. With contexts, you can access, update, and commit the saved object values. In contrast, PUSH and POP simply allow you to save and restore values. Typically, you use PUSH and POP statements within a program to make changes that apply only during the execution of the program.

Saving the Status of a Dimension or the Value of an Option

A PUSH statement saves the current status of a dimension, the value of an option, or the value of a single-cell variable. For example, to save the current value of the DECIMALS option so you can set it to a different value for the duration of the program, use the following statement in the initialization section.

PUSH DECIMALS

You do not need to know the original value of the option to save it or to restore it later. You can restore the saved value with a POP statement.

POP DECIMALS

You must make sure a POP statement is executed when errors cause abnormal termination of the program, as well as when the program ends normally. Therefore, you should place the POP statement in the normal and abnormal exit sections of the program.

Saving Several Values at Once

You can save the status of one or more dimensions and the values of any number of options and variables in a single PUSH statement, and you can restore the values with a single POP statement, as shown in the following example.

PUSH month DECIMALS ZSPELL
       ...
POP month DECIMALS ZSPELL

Using Level Markers

When you are saving the values of several dimensions and options, then PUSHLEVEL and POPLEVEL statements provide a convenient way to save and restore the session environment.

You first use a PUSHLEVEL statement to establish a level marker. Once the level marker is established, you use a PUSH statement to save the status of dimensions and the values of options or single-cell variables.

When you place more than one PUSH statement between the PUSHLEVEL and POPLEVEL statements, then all the objects that are specified in those PUSH statements are restored with a single POPLEVEL statement.

By using PUSHLEVEL and POPLEVEL, you save some typing as you write your program because you only need to type the list of objects once. You also reduce the risk of omitting an object from the list or misspelling the name of an object.

For an example of creating level markers, see Example 10-86, "Creating Level Markers". Example 10-87, "Nesting PUSHLEVEL and POPLEVEL Commands" illustrates nesting PUSHLEVEL and POPLEVEL statements.

Using CONTEXT to Save Several Values at Once

As an alternative to using PUSHLEVEL and POPLEVEL, you can use the CONTEXT command. 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. The CONTEXT function returns information about objects in a context.

Handling Errors

When an error occurs anywhere in a program, Oracle OLAP performs the following actions:

Stores the name of the error in the ERRORNAME option, and the text of the error message in the ERRORTEXT option.

Note:
When the ERRNAMES option is set to the default value of YES, 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.
When ECHOPROMPT is YES, then Oracle OLAP echoes input lines, error messages, and output lines, to the current outfile. When you use the OUTFILE or DBGOUTFILE statement, you can capture the error messages in a file. See Example 10-70, "Directing Output to a File" for an example of directing output to a file.
When error trapping is off, then the execution of the program is halted. When error trapping is on, then the error is trapped.

Trapping an Error

To make sure the program works correctly, you should anticipate errors and set up a system for handling them. You can use a TRAP statement to turn on an error-trapping mechanism in a program. When error trapping is on and an error is signaled, then the execution of the program is not halted. Instead, error trapping does the following:

Turns off the error-trapping mechanism to prevent endless looping in case additional errors occur during the error-handling process
Branches to the label that is specified in the TRAP statement
Executes the statements following the label

Passing an Error to a Calling Program

To pass an error to a calling program, you can use one of two methods. The method you use depends on when you want the error message to be produced. With the first method, Oracle OLAP produces the message immediately and then the error condition is passed through the chain of programs. With the second method, Oracle OLAP passes the error through the chain of programs first and then produces the message. See "Passing an Error: Method One" and "Passing an Error: Method Two" for details.

With both methods, the appropriate error handling happens in each program in the chain, and at some point Oracle OLAP sends an error message to the current outfile.

Passing an Error: Method One

Using this method, Oracle OLAP produces the message immediately and then the error condition is passed through the chain of programs.

Use a TRAP statement with the (default) PRINT option. When an error occurs, Oracle OLAP produces an error message, and execution branches to the trap label. After the trap label, perform whatever cleanup you want, and then execute the following statement.

SIGNAL PRGERR

This creates an error condition that is passed up to the program from which the current program was run. However, PRGERR does not produce an error message. PRGERR sets the ERRORNAME option to a blank value.

When the calling program contains a trap label, execution branches to the label. When each of the programs in a sequence of nested programs uses TRAP and SIGNAL in this way, you can pass the error condition up through the entire sequence of programs.

Passing an Error: Method Two

Using this method, Oracle OLAP passes the error through the chain of programs first and then produces the message.

Use a TRAP statement with the NOPRINT option. When an error occurs, execution branches to the trap label, but the error message is suppressed. After the trap label, perform whatever cleanup you want, then execute the following statement.

SIGNAL ERRORNAME ERRORTEXT

The options ERRORNAME and ERRORTEXT contain the name and message of the original error, so this SIGNAL statement reproduces the original error. The error is then passed up to the program from which the current program was run.

When the calling program also contains a trap label, execution branches to its label. When each of the programs in a sequence of nested programs uses TRAP...NOPRINT and SIGNAL ERRORNAME ERRORTEXT in this way, you can pass the error condition up through the entire sequence of programs. Oracle OLAP produces the error message at the end of the chain.

When you reach a level where you want to handle the error and continue the application, omit the SIGNAL statement. You can display your own message with a SHOW statement.

Suppressing Error Messages

When you do not want to produce the error message that is normally provided for a given error, then you can use TRAP statement with a NOPRINT keyword.

TRAP ON error NOPRINT

When you use the NOPRINT keyword with TRAP, control branches to the error label, and an error message is not issued when an error occurs. The statements following the error label are then executed.

When you suppress the error message, you might want to produce your own message in the abnormal exit section. A SHOW statement produces the text you specify but does not signal an error.

TRAP ON error NOPRINT
        ...
error:
        ...
SHOW 'The report will not be produced.'

The program continues with the next statement after producing the message.

Creating Your Own Error Messages

All errors that occur when a statement or statement sequence does not conform to its requirements are signaled automatically. In your program, you can establish additional requirements for your own application. When a requirement is not met, you can execute a SIGNAL statement to signal an error.

You can give the error any name. When a SIGNAL statement is executed, the error name you specify is stored in the ERRORNAME option, just as an OLAP DML error name is automatically stored. When you specify your own error message in a SIGNAL statement, then your message is produced just as an OLAP DML error message is produced. When you are using a TRAP statement to trap errors, a SIGNAL statement branches to the TRAP label after the error message is produced.

For an example of signaling an error, see Example 10-122, "Signaling an Error".

When you want to produce a warning message without branching to an error label, then you can use a SHOW statement as illustrated in Example 10-120, "Creating Error Messages Using SHOW".

Handling Errors in Nested Programs

When handling errors in nested programs, the error-handling section in each program should restore the environment. It can also handle any special error conditions that are particular to that program. For example, when your program signals its own error, then you can include statements that test for that error.

Any other errors that occur in a nested program should be passed up through the chain of programs and handled in each program. To pass errors through a chain of nested programs, you can use one of two methods, depending on when you want the error message to be produced:

The error message is produced immediately, and the error condition is then passed through the chain of programs. This approach is illustrated in Example 10-156, "Producing a Program Error Message Immediately".
The error is passed through the chain of programs first, and the error message is produced at the end of the chain. This approach is illustrated inExample 10-157, "Producing a Program Error Message at the End of the Chain".

A SIGNAL statement is used in both methods.

Handling Errors While Saving the Session Environment

To correctly handle errors that might occur while you are saving the session environment, place your PUSHLEVEL statement before the TRAP statement and your PUSH statements after the TRAP statement.

PUSHLEVEL 'firstlevel'
TRAP ON error
PUSH 
 ...

In the abnormal exit section of your program, place the error label (followed by a colon) and the statements that restore the session environment and handle errors. The abnormal exit section might look like this.

error:
POPLEVEL 'firstlevel'
OUTFILE EOF

These statements restore saved dimension status and option values and reroute output to the default outfile.