Within an OLAP DML program, the ARGUMENT statement declares an argument that is expected by the program. Within the program, the argument is stored in a structure similar to a variable or valueset. The argument is initialized with the value that was passed when the program was invoked. An argument exists only while the program is running.
The ARGUMENT statement is used only in programs, and it must precede the first executable line in the program. Be careful to distinguish the ARG abbreviation of the ARGUMENT statement from the ARG function.
The name by which the argument is referenced in the program. An argument cannot have the same name as a local variable or valueset. You name an argument according to the rules for naming analytic workspace objects (see the DEFINE command).
The data type of the argument, which indicates the kind of data to be stored. You can specify any of the data types that are listed and described in the DEFINE VARIABLE entry. Also, when you want to the program to be able to receive an argument without converting it to a specific data type, you can also specify WORKSHEET for the data type.
Important:
When you declare an argument to be of type NTEXT, and a TEXT value is passed into the program, Oracle OLAP converts the TEXT value to NTEXT. Similarly, when you declare an argument to be of type TEXT, and an NTEXT value is passed into the program, Oracle OLAP converts the NTEXT value to TEXT. Data can be lost when NTEXT is converted to TEXT.The name of a dimension, whose value is contained in the argument. The argument holds a single value of the dimension. Assigning a value that does not currently exist in the dimension causes an error.
Indicates that name is a valueset. The keyword dim specifies the dimension for which the valueset holds values. Argument valuesets can be used within the program in the same way you would use a valueset in the analytic workspace.
An argument exists only while the program in which it is declared is running. When the program terminates, the argument ceases to exist and its value is lost. Therefore, an argument is not an analytic workspace object.
A program can terminate when a RETURN or SIGNAL statement, or at the last line of the program executes. When the program calls a subprogram, the original program is temporarily suspended and the argument still exists when the subprogram ends and control returns to the original program. A program that calls itself recursively has separate arguments for each running copy of the program.
Declaring Arguments that Are Passed Into a Program
When declaring arguments that are passed into a program special considerations apply.
Arguments Passed by Value Arguments are passed into a program by value. Consequently, the called program is given only the value of an argument, without access to any analytic workspace object to which it might be related. Therefore, you can change an argument value within the called program without affecting any value outside the program. You can think of an argument variable or valueset as a conveniently initialized local variable or local valueset.
Argument Processing for a Function When a program is invoked either with a CALL statement or as a function, the following two-step process occurs:
The specified data types are established. Argument expressions specified by the calling program are evaluated left to right, and their data types are identified. An expression representing a dimension value can be a text (TEXT or ID), numeric (INTEGER, DECIMAL, and so on), or RELATION value. An error in one argument expression stops the process.
Each specified data type is matched with the declared data type. Argument expressions are matched positionally with the declared arguments. The first argument expression is matched with the first declared argument, the second argument expression with the second argument, and so on. Each expression is converted in turn to the declared data type of the declared argument.
When an argument is declared as a dimension value, the matching value passed from the calling program can be TEXT or ID (representing a value of the specified dimension), numeric (representing a logical dimension position), or RELATION (representing a physical dimension position). The RELATION method is the way Oracle OLAP passes along dimension values that are the result of evaluating a dimension name or relation name used as the matching value. When the matching value is a noninteger numeric value (for example, DECIMAL), it is rounded to the nearest INTEGER value to represent a logical dimension position.
When an argument is declared as something other than a dimension value, and the matching value from the calling program is a RELATION value, an error occurs. When you want to pass a RELATION value and receive it as a TEXT argument, use CONVERT to convert the value in the program's argument list.
When an argument is declared as a valueset of a dimension, only the name of a valueset of that dimension is accepted as an argument.
When an error occurs in either the first or second step, the program is not executed.
Argument Processing for a Command When a program is invoked as a standalone command with its arguments not enclosed by parentheses, the arguments are matched positionally with the declared arguments. The called program can reference the specified arguments either as declared arguments or through the ARG (n), ARGS, and ARGFR (n) functions. In this situation, the arguments are passed as text strings, not by value.
Extra Arguments When the calling program specifies more arguments than there are declarations in the called program, the extra arguments are ignored. When the calling program specifies fewer arguments than there are declarations in the called program, the extra arguments are given NA values.
Argument Name that Duplicate the Names of Analytic Workspace Objects
Ordinarily, when you give an argument the same name as an analytic workspace object, the argument (not the analytic workspace object) is referenced within the program. Exceptions to this rule occur only when the statement in which the reference is made requires an analytic workspace object as an argument.
Example 9-44 Passing an Argument to a User-Defined Function
Sometimes verifying user input to the GET function can become complicated. The usual method involves a line of code such as the following one.
SHOW GET(INT VERIFY VALUE GT 0 AND VALUE LT 100 - IFNOT 'The value must be between 1 and 100')
You can create a user-defined function to make the GET expression simpler. For example, the following program can be used as a function to check for values between 0 and 100.
DEFINE verit PROGRAM BOOLEAN
PROGRAM
ARGUMENT uservalue INT
TRAP ON haderror NOPRINT
IF uservalue GT 100
THEN SIGNAL toobig 'The value must be 100 or smaller.'
ELSE IF uservalue LT 0
THEN SIGNAL toosmall 'The value must be 0 or greater.'
RETURN TRUE
haderror:
RETURN FALSE
END
The following GET expression uses the verit function.
SHOW GET(INT VERIFY VERIT(VALUE) IFNOT ERRORTEXT)
Example 9-45 Passing Multiple 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 statement to declare the INTEGER argument to be used in setting the value of the COLWIDTH option.
ARGUMENT natext TEXT ARGUMENT widthamt INTEGER NASPELL = natext COLWIDTH = widthamt
To specify eight-character columns, you could run the product.rpt program with the following statement.
CALL product.rpt ('Missing' 8)
When 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 statement 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 statement.
CALL product.rpt ('Missing' 8 'TENTS')
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.
Example 9-46 Using the ARGUMENT Statement
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 ARGUMENT statement to set the NASPELL option to the value provided as an argument.
ARGUMENT natext TEXT NASPELL = natext
To specify Missing as the text for NA values, you can execute the following statement.
CALL product.rpt ('Missing')
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.
DEFINE natemp VARIABLE TEXT TEMP natemp = 'Missing' CALL product.rpt (natemp)
Example 9-47 Passing the Text of an Expression
Suppose you have a program named custom.rpt that includes a REPORT statement, but you want to be able to use the program to present the values of an expression, such as sales - expense, and individual variables.
custom.rpt 'sales - expense'
Note that 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 statements to produce a report of this expression.
ARGUMENT rptexp TEXT REPORT &rptexp
For an example of using ampersand substitution to pass multiple dimension values, see Example 10-18, "Using Ampersand Substitution with LIMIT".
Example 9-48 Passing Workspace Object Names and Keywords
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 statement like the following one.
sales.rpt units d
In the sales.rpt program, you can use the following statements.
ARGUMENT varname TEXT ARGUMENT sortkey TEXT SORT product &sortkey &varname REPORT &varname
After substituting the arguments, these statements are executed in the sales.rpt program.
SORT product D units REPORT units