6 OLAP DML Programs

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-45, "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 an analytic workspace object 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 which 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 10-18, "Using Ampersand Substitution with LIMIT". For an example of using ampersand substitution to pass the text of an expression, see Example 9-47, "Passing the Text of an Expression". For an example of using ampersand substitution to pass object names and keywords, see Example 9-48, "Passing Workspace Object Names and Keywords".

Changing the Program Environment

To perform a task within a program, you often must 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 are set in the program. After the program executes, 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 is 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 and when the program ends normally. Therefore, 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 multiple PUSH statements 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 must only 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-85, "Creating Level Markers". Example 10-86, "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, 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 10-123, "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-121, "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 as illustrated in Example 10-157, "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 as illustrated inExample 10-158, "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.

Permission Programs

Permission programs are programs that you write that give permission to users to access workspace data. When a user first attaches an analytic workspace, Oracle OLAP checks to see if a permission program that is appropriate for the attachment mode exists.

The permission program for each attachment mode must have a particular name as outlined in Table 6-2, "Names of Permission Programs for Different Attachment Modes".

When an appropriate permission program exists, Oracle OLAP executes the program. When a user specifies a password when attaching the analytic workspace, then the password is passed as an argument to the permission program for processing.

Permission programs allow you to control two levels of access to the analytic workspace in which they reside.

• Access at the analytic workspace level—Depending on the return value of the permission program, the user is or is not granted access to the entire analytic workspace. You can use the return value to indicate to Oracle OLAP whether or not the user has the right to attach the workspace.

• Access at the object level—Within a permission program for read-only or read/write attachment, you can specify PERMIT statements that grant or restrict access to individual workspace objects. PERMIT programs must be in the same workspace as the objects for which they issue PERMIT statements.

  Note:

  All of the objects referred to in a given permission program must exist in the same analytic workspace.

To create a permission program, define a user-defined function (as described in "Creating User-Defined Functions") with a recognized name, then define the contents for the program as described in "Specifying Program Contents".

OnAttach Programs

An OnAttach program can have any name or it can explicitly be named ONATTACH.

How you specify the execution of an OnAttach program varies depending on its name:

• When a program named ONATTACH. exists in an analytic workspace, each time you attach the workspace, that program executes automatically unless you include a NOOTTACH keyword in the AW ATTACH statement, or unless you include an ONATTACH clause that specifies a different program name.

• To execute an OnAttach program that is not named ONATTACH, specify the name of the program within the ONATTACH clause of AW ATTACH statement.

Autogo Programs

An Autogo program can have any name or it can explicitly be named AUTOGO.

How you specify the execution of an Autogo program varies depending on its name:

• When a program named AUTOGO exists in an analytic workspace, each time you attach the workspace, that program executes automatically unless you include a NOAUTOGO keyword in the AW ATTACH statement, or unless you include an AUTOGO clause that specifies a different program name.

• To execute an Autogo program that is not named AUTOGO, specify the name of the program within the AUTOGO clause of AW ATTACH statement.

Importing Data to and Exporting Data from Relational Tables

You can embed SQL statements in OLAP DML programs using the OLAP DML SQL statement. Using the OLAP DML SQL statement you can import data from relational tables into analytic workspace objects and export data from analytic workspace objects to relational tables.

Importing Data to and Exporting Data from Flat Files

Oracle OLAP provides several statements that you can use to read data from flat files or to write data to flat files. These statements are frequently used together in a special program.

Importing Data to and Exporting Data from Spreadsheets

Within an OLAP DML program you can use an IMPORT statement to import data from a spreadsheet into analytic workspace objects. You can use an EXPORT statement to export data from analytic workspace objects into a spreadsheet.

Creating an Object Trigger Program

Once an object is defined in an analytic workspace, you can create a trigger program for that object by following the following procedure:

1. Define the program as described in DEFINE PROGRAM.

2. Determine what to name the program and whether the program can be a user-defined program. (See Table 6-3, "Object Trigger Program Characteristics".) If the program can be a user-defined program, decide whether or not you want to define the trigger program as a user-defined function.

3. Code the actual program as described in"Specifying Program Contents".

4. Keep the following points in mind when coding trigger programs:

   • Use Table 6-3, "Object Trigger Program Characteristics" to determine if Oracle OLAP passes values to the program. If it does, use an ARGUMENT statement to declare these arguments in your program and the VARIABLE statement to define program variables for the values. (See Table 6-4, "Arguments Passed to Trigger Programs" for specific information about the arguments.)

   • A program that is triggered by an Assign event is executed each time Oracle OLAP assigns a value to the object for which the event was defined. Thus, a program triggered by an Assign event is often executed over and over again as the assignment statements loops through a object assigning values. You can use TRIGGERASSIGN to assign a value that is different from the value specified by the assignment statement that triggered the execution of the program.

   • In some cases, Oracle OLAP changes the status of the dimension being maintained when a Maintain event triggers the execution of a program. See Table 6-5, "How Programs Triggered by Maintain Events Effect Dimension Status" for details

   • Use the CALLTYPE function within a program to identify that the program was invoked as a trigger.

5. When the trigger program is not a TRIGGER_AFTER_UPDATE, TRIGGER_BEFORE_UPDATE, or TRIGGER_DEFINE program, associate the program with the desired object and event using the TRIGGER command.

6. There is no support for recursive triggers. You must set the USETRIGGERS option to NO before you issue the same DML statement within a trigger program that triggered the program itself. For example, assume that you have written a program named TRIGGER_MAINTAIN_ADD that is triggered by MAINTAIN ADD statements. Within the TRIGGER_MAINTAIN_ADD program, you must set the USETRIGGERS option to NO before you issue a MAINTAIN statement.

Characteristics of Object Trigger Programs

Object trigger programs have certain characteristics depending on the statement that triggers them. Some trigger programs execute before the triggering statement executes; some after. Oracle OLAP passes arguments to programs triggered by some statements, but not others. Oracle OLAP does not change dimension status before most trigger programs execute, but does change dimension status before some MAINTAIN statements trigger program execution. In most cases, you can give a trigger program any name that you choose, but some events require a program with a specific name.

Table 6-3, "Object Trigger Program Characteristics" lists the OLAP DML statements that trigger programs, the required name of the program (if any), whether or not Oracle OLAP uses values returned by the program, and whether or not Oracle OLAP passes arguments to the program.

Keep the following points in mind when designing trigger programs:

• Triggers that execute before the DML statement—For trigger programs that execute before the triggering OLAP DML statement executes, you can define the trigger program as a user-defined function that returns a BOOLEAN value. The value returned by the program determines if Oracle OLAP executes the statement that triggered the execution of the trigger program. When the program returns FALSE, Oracle OLAP does not execute the triggering statement; when it returns TRUE or NA, the triggering statement executes.

• Arguments passed to trigger programs—Oracle OLAP passes arguments to some trigger programs. These programs are identified in Table 6-3, "Object Trigger Program Characteristics". Descriptions of these arguments are provided in Table 6-4, "Arguments Passed to Trigger Programs". Use the ARGUMENT statement to declare these arguments in your program. Use VARIABLE to define program variables for the values. Use the WKSDATA function to retrieve the data type of an argument with a WORKSHEET data type.

• Assign trigger programs—Oracle OLAP executes a program triggered by an Assign event each time it assigns a value to the object for which the event was defined. Thus, a program triggered by an Assign event is often executed over and over again as the assignment statements loops through a object assigning values. With each execution, the value to be assigned is passed as argument1 to the Assign trigger program. (See Table 6-4, "Arguments Passed to Trigger Programs" for more information and Example 10-163, "An ASSIGN Trigger on a Variable" for an example.) Within the Assign trigger program, you can use a TRIGGER ASSIGN statement to assign a different value than that specified by the assignment statement that triggered the execution of the Assign trigger program.

  You can only assign values to a formula when the formula has an Assign trigger defined for it. When you assign a value to a formula with an Assign event, Oracle OLAP executes the trigger program for the event for assigned value and passes the assigned value to the trigger program. The Assign trigger does not change the definition of the formula itself. See Example 10-165, "An ASSIGN Trigger on a Formula" for an example of an Assign trigger on a formula.

• Maintain trigger programs and dimension status —In some cases, Oracle OLAP changes the status of the dimension being maintained when a Maintain event triggers the execution of a program. See Table 6-5, "How Programs Triggered by Maintain Events Effect Dimension Status" for details.

• Maintain triggers and dimension surrogates—Maintain triggers for dimension surrogates are different than Maintain triggers for other objects. You can only successfully issue a MAINTAIN statement against a dimension surrogate, when the dimension surrogate has a Maintain trigger. Issuing a MAINTAIN statement for a surrogate dimension that does not have a Maintain trigger, returns an error. Also, for Maintain Add and Maintain Merge triggers, whether or not an argument is passed to the program depends on the object on which the trigger is defined:

  • For dimension surrogates with a Maintain trigger, Oracle OLAP executes the trigger program one time for each value added or merged and passes that value into the program.

  • For other objects with a Maintain trigger, Oracle OLAP executes the trigger program only once after the MAINTAIN statement executes and no values are passed into the program