Specifying Program Contents

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 8-8, "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 statement

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.

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

Looping Nature of OLAP DML Statements

Unlike SQL statements that operate against a single row in a table, OLAP DML statements usually operate against the all of the array elements that are in status for the object data object:

• When you issue a statement against an object that has one or more dimensions, the statement loops over the values in status for each dimension of the object and performs the requested operation.

• When you use an OLAP assignment statement (that is, SET) to assign values to a variable, Oracle OLAP loops through all of the in status array elements assigning values in sequence.

  Assume for example, that there is a dimension named prodid that has three values, Prod01, Prod02, and Prod03, and you have a variable named quantity that is dimensioned by prodid. As the following code snippet illustrates, when you assign the value 3 to quantity, Oracle OLAP loops over the values in status for each dimension of the target and assigns the value 3 to all the cells in quantity.

  quantity = 3
  REPORT quantity
  
  PRODID          QUANTITY
  -------------- ----------
  PROD01             3.00
  PROD02             3.00
  PROD03             3.00
  
  

• Other OLAP DML statements (for example, REPORT, ROW, and FOR) also loop through all of the in status elements of a dimensioned object when they execute.

By default, looping statements loop through the values of a dimensioned object using the order in which the dimensions of the object are listed in the definition of the object. Also, when a variable is dimensioned by a composite, most looping statements loop through the variable as though it was not dimensioned by a composite, but was, instead, dimensioned by the base dimensions of the composite.

The OLAP DML provides ways for you to change the default looping behavior or to explicitly request looping:

• ACROSS phrase—Some looping statements (such as SET that you use to assign values) have an ACROSS phrase that you can use to specify nondefault looping behavior. Using the ACROSS phrase, you can specify:

  • The specific dimensions (and order) in which you want the statement to loop. In this case, the statement will loop over the dimensions in the order that you specify them in the ACROSS phrase, not in the order in which they appear in the variable's definition.

  • A composite over which you want the statement to loop. When a variable is dimensioned by a composite, specifying the name of a composite improves performance. When you specify the name of a composite in the ACROSS phrase of a looping statement, the statement only loops over the existing cells of a variable.

  For more complete documentation of the ACROSS phrase, see SET.

• ACROSS statement—When an OLAP DML statement is not a looping statement or does not include an ACROSS phrase, you can request looping behavior by coding the DML statement as an argument of the ACROSS statement.

Flow-of Control Statements

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

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 19-45, "Creating Level Markers".Example 19-46, "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.

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:

1. Turns off the error-trapping mechanism to prevent endless looping in case additional errors occur during the error-handling process

2. Branches to the label that is specified in the TRAP statement

3. 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.

SIGNAL PRGERR

SIGNAL ERRORNAME ERRORTEXT

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 21-32, "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 21-30, "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 24-2, "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 in Example 24-3, "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.