|Oracle9i OLAP Developer's Guide to the OLAP DML
Release 2 (9.2)
Part Number A95298-01
Developing Programs, 5 of 12
The OLAP DML provides two ways for you to accept arguments in a program:
ARGUMENTcommand. You can use the
ARGUMENTcommand to declare arguments in a program.
ARGUMENTcommand allows you to use both simple and complex arguments (such as expressions). The
ARGUMENTcommand also makes it convenient to pass arguments from one program to another, or to create your own user-defined functions.
ARGfunctions. You can use the
ARGFRfunctions in any program to retrieve arguments from a command. These functions are primarily useful for simple text arguments.
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 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.
In this example, literal text enclosed in single quotes provides the value of the text argument. However, any other type of text expression works equally well, as shown in the next example.
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
To specify eight-character columns, you could run the
product.rpt program with the following command.
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 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.
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.
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. The ampersand causes
LIMIT to interpret
'first 3' as a keyword expression rather than as a dimension value.
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.
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.
custom.rpt program, you could use the following commands to produce a report of this expression.
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.
sales.rpt program, you can use the following commands.
After substituting the arguments, these commands are executed in the
"Substitution Expressions" for more information about ampersand substitution.