|Oracle9i OLAP Services Developer's Guide to the OLAP DML
Release 1 (9.0.1)
Part Number A86720-01
Designing Programs, 6 of 13
The OLAP DML provides two ways for you to accept arguments in a program:
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.
Suppose you are writing a program, called PRODUCT.RPT. The PRODUCT.RPT program produces a report, and you want to supply an argument to the report program that specifies the text that should appear for an NA value in the report. In the PRODUCT.RPT program, you can use the declared argument NATEXT in an = command to set the NASPELL option to the value provided as an argument.
Missing as the text for NA values, you can execute the following command.
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.
Suppose, in the PRODUCT.RPT program, that you want to supply a second argument that specifies the column width for the data columns in the report. In the PRODUCT.RPT program, you would add a second ARGUMENT command to declare the integer argument to be used in setting the value of the COLWIDTH option.
To specify eight-character columns, you could run the PRODUCT.RPT program with the following command.
If the PRODUCT.RPT program also requires the name of a product as a third argument, then in the PRODUCT.RPT program you would add a third ARGUMENT command to handle the product argument, and you would set the status of the PRODUCT dimension using this argument.
argument natext text argument widthamt integer argument rptprod product naspell = natext colwidth = widthamt limit product to rptprod
You can run the PRODUCT.RPT program with the following command.
In this example, the third argument is specified in uppercase letters with the assumption that all the dimension values in the analytic workspace are in uppercase letters.
It is very common to pass a simple text argument to a program. However, there are some situations in which you might want to pass a more complicated text argument, such as an argument that is composed of more than one dimension value or is composed of the text of an expression. In these cases, you want to substitute the text you pass, exactly as you specify it, wherever the argument name appears.
To indicate that you want a text argument handled in this way, you precede the argument name with an ampersand when you use it in the command lines of your program. Specifying arguments in this way is called ampersand substitution.
When you use ampersand substitution to pass the names of OLAP DML 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.
If you want to specify exactly two products for the PRODUCT.RPT program discussed earlier, then you could declare two dimension-value arguments to handle them. But if you want to be able to specify any number of products using LIMIT keywords, then you can use a single argument with ampersand substitution.
Suppose you use the following commands in your program.
argument natext text argument widthamt integer argument rptprod text . . . limit product to &rptprod
You can run the program and specify that you want the first three products in the report.
The single quotation marks are necessary to indicate that "first 3" should be taken as a single argument, rather than two separate arguments separated by a space.
Suppose you have a program named CUSTOM.RPT that includes a REPORT command, but you want to be able to use the program to present the values of an expression, such as
sales - expense, as well as single variables.
Note: You must enclose the expression in single quotation marks. Because the expression contains punctuation (the minus sign), the quotation marks are necessary to indicate that the entire expression is a single argument.
In the CUSTOM.RPT program, you could use the following commands to produce a report of this expression.
It is not possible to 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 the following types of arguments, you must always use an ampersand to make the appropriate substitution:
Suppose you design a program called SALES.RPT that produces a report on a variable that is specified as an argument and sorts the PRODUCT dimension in the order that is specified in another argument. You would run the SALES.RPT program by executing a command like the following one.
In the SALES.RPT program, you can use the following commands.
After substituting the arguments, these commands are executed in the SALES.RPT program.
You can also pass expressions into a program by value. This means that the program receives its argument as the values that are the result of an expression rather than as the expression itself. Because this type of argument provides only the value of the expression, it does not give the program access to any OLAP DML object that is used in the original expression.
To pass an expression's value as an argument, you must execute the program with the CALL command and enclose its arguments in parentheses immediately following the program name.
By using the CALL command to execute a program from within another program, you can use expressions to construct arguments "on the fly." In this way, the arguments passed to the second program can vary according to what has already happened in the first program.
Suppose the first argument for your program PRODUCT.RPT specifies the text that you want used for NA values. You might already have that text stored in a variable (for example, TEMP1). Rather than supplying the text as an argument, you can specify the name of the variable as an argument.
In this case, the argument that is passed into the PRODUCT.RPT program is not the literal text
'temp1', but the current value of the variable TEMP1. You can still pass literal text into the program, but you must enclose the text in single quotation marks.
In the program lines below, an argument has been passed that indicates the type of report that should be produced. The report program is called with an argument that specifies the text to be used for NA values. Because that text varies with the type of report, an expression is used as the argument to supply the appropriate text.
For more information, see the following table.
|IF you want documentation about . . .||THEN see . . .|
individual OLAP DML commands,
the topic for the command in OLAP DML Reference