| Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B10339-02 |
|
|
View PDF |
| Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B10339-02 |
|
|
View PDF |
An OLAP DML program is written in the OLAP DML. It acts on data in the analytic workspace and helps you accomplish some workspace management or analysis task. You can write OLAP DML programs to perform tasks that you must do repeatedly in the analytic workspace, or you can write them as part of an application that you are developing.
To create an OLAP DML program, take the following steps:
Issue a DEFINE PROGRAM statement to define the program object. When the program that you are defining will be used is a function, include the datatype and dimension arguments.
Add contents to the program that specify the processing that you want performed as described in "Specifying Program Contents".
Compile the program as described in "Compiling Programs".
Test and debug the program as described in "Testing and Debugging Programs".
Execute the program as described in "Executing Programs".
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. For brief descriptions, see "Programming Statements" .
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 command 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 command on a single line, separate the commands 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 commands.
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.)
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 8-7, "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 command), then the function returns results with the same dimensions as its argument.
You must declare the arguments using the ARGUMENT command within the program, and you must specify the arguments in parentheses following the name of the program.
Use ARGUMENT statements to declare both simple and complex arguments (such as expressions). The ARGUMENT command also makes it convenient to pass arguments from one program to another, or to create your own user-defined functions. The ARGUMENT command lets you declare an argument of any data type, dimension, or valueset. Any ARGUMENT commands 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.
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".
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.
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
Command keywords, such as COMMA or NOCOMMA in the REPORT command, 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 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".
Like most programming languages, the OLAP DML has a number of commands 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 5-1, "Statements For Determining Flow-of-Control" lists OLAP DML flow-of-control commands. The looping characteristic of OLAP DML commands is discussed in "Looping Nature of OLAP DML Commands and Functions".
Unlike SQL statements that operate against a single row in a table, OLAP DML commands and functions usually operate against the entire array of data represented by an analytic workspace 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 cells 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 values 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 command—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 command.
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.
Table 5-1 Statements For Determining Flow-of-Control
| Statement | Description |
|---|---|
| BREAK statement | 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 statement | 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 statement | 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 statement | 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 the REPORT command. 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. |
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.
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 commands 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.
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 the PUSHLEVEL and PUSH commands. You can restore the current status values using the POPLEVEL and POP commands.
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 the PUSH and POP commands within a program to make changes that apply only during the execution of the program.
The PUSH command 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 command 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 the POP command.
POP DECIMALS
You must make sure the POP command is executed when errors cause abnormal termination of the program, as well as when the program ends normally. Therefore, you should place the POP command in the normal and abnormal exit sections of the program.
You can save the status of one or more dimensions and the values of any number of options and variables in a single PUSH command, and you can restore the values with a single POP command, as shown in the following example.
PUSH month DECIMALS ZSPELL
...
POP month DECIMALS ZSPELL
When you are saving the values of several dimensions and options, then the PUSHLEVEL and POPLEVEL commands provide a convenient way to save and restore the session environment.
You first use the PUSHLEVEL command to establish a level marker. Once the level marker is established, you use the PUSH command to save the status of dimensions and the values of options or single-cell variables.
When you place more than one PUSH command between the PUSHLEVEL and POPLEVEL commands, then all the objects that are specified in those PUSH commands are restored with a single POPLEVEL command.
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 commands.
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.
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.
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 command, you can capture the error messages in a file. See Example 19-17, "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.
To make sure the program works correctly, you should anticipate errors and set up a system for handling them. You can use the TRAP command 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 command
Executes the commands following the label
When you do not want to produce the error message that is normally provided for a given error, then you can use the NOPRINT keyword with the TRAP command.
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 commands 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. The SHOW command 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 command after producing the message.
All errors that occur when a command or command 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 the SIGNAL command to signal an error.
You can give the error any name. When the SIGNAL command 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 the SIGNAL command, then your message is produced just as an OLAP DML error message is produced. When you are using a TRAP command to trap errors, a SIGNAL command 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 the SHOW command as illustrated in Example 21-31, "Creating Error Messages Using SHOW".
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 commands 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".
The SIGNAL command is used in both methods.
To correctly handle errors that might occur while you are saving the session environment, place your PUSHLEVEL command before the TRAP command and your PUSH commands after the TRAP command.
PUSHLEVEL 'firstlevel' TRAP ON error PUSH ...
In the abnormal exit section of your program, place the error label (followed by a colon) and the commands that restore the session environment and handle errors. The abnormal exit section might look like this.
error: POPLEVEL 'firstlevel' OUTFILE EOF
These commands restore saved dimension status and option values and reroute output to the default outfile.
|
 Copyright © 2003 Oracle Corporation All Rights Reserved. |
|