|Oracle® OLAP DML Reference
11g Release 2 (11.2)
Part Number E17122-05
CALL program-name [(arg ...)]
The name of the program to be called.
One or more optional arguments expected by the called program. These arguments can be declared in the called program with ARGUMENT, or they can be referenced in the program with ARG. If the program uses the ARGUMENT statement, when you use CALL to invoke the program, specify the arguments so that they match the positions of the arguments declared in the called program.
When you pass a dimension value or dimension name as an argument, you must enclose the exact text value in single quotes, for example,
'Jan96'. When the program arguments are declared with the ARGUMENT statement, you can pass a text expression that evaluates to a text value.
Program Return Values
When you use CALL to invoke a program that returns a value, the return value is discarded. A program can use the CALLTYPE function to determine whether it was invoked as a function, as a command, or by using CALL.
ARGUMENT Command or ARG Function
The called program can process arguments using either the ARGUMENT statement or the ARG function. In a program that has been invoked with CALL or as a function, the ARGS and ARGFR functions always return
When CALL invokes a program whose arguments are not declared with the ARGUMENT statement, the arguments passed can be referenced with the ARG function. However, the ARG function is a text function and, consequently, interprets all arguments passed as text values. When you want to pass NTEXT arguments, be sure to declare them using ARGUMENT instead of using ARG. With ARG, NTEXT arguments are converted to TEXT, and this can result in data loss when the NTEXT values cannot be represented in the database character set.
ARGUMENT Statement Processing
When a program is invoked with CALL or as a function, the following two-step process occurs. When an error occurs in either step, the program is not executed.
The specified data types are established. Argument expressions specified by the calling program are evaluated left to right, and their data types are identified. Any expression representing a dimension value can be a text (
ID), numeric (
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 by position with the declared arguments in the called program. The first argument expression is matched with the first declared argument variable, the second argument expression is matched with the second declared argument variable, and so on. Each expression is converted in turn to the declared data type of the argument variable.
When an argument variable is declared as a dimension value, the matching value passed from the calling program can be
ID (representing a value of the specified dimension), numeric (representing a logical dimension position), or RELATION (representing a physical dimension position).When the matching value is a non-integer numeric value (for example,
DECIMAL), it is rounded to the nearest
INTEGER to represent a logical dimension position.
When an argument variable 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 that is received as a
TEXT argument, use the CONVERT function to convert the value in the program's argument list.
ARGUMENT Statement with Extra Arguments
When the calling program specifies more arguments than are declared in the called program, the extra arguments are ignored. When the calling program specifies fewer arguments than are declared in the called program, the extra argument variables are given
ARGUMENT Statement Passing by Value
When arguments are declared with the ARGUMENT statement, they are passed by value to a program. 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. However, when the name of an analytic workspace object is specified as an argument enclosed in single quotes, the value of the analytic workspace object is not passed. Instead, the name of the object is passed as a text string. See Example 9-59, "Calling a Program or Function".
Example 9-59 Calling a Program or Function
This example illustrates how two programs,
roundup.f, are used in different ways to evaluate data and produce output.
roundup.p program accepts the name of a decimal variable as a text string and produces a report of that variable's values rounded to the nearest
roundup.f program also accepts the name of a decimal variable. However, instead of passing the name of the variable as a text string, the variable's value is passed as an argument.
roundup.f does not produce a report. Instead, it returns each of the values of the decimal variable, rounded to the nearest
roundup.p program is invoked using CALL and includes a REPORT statement. In contrast,
roundup.f is invoked as a user-defined function whose return value is then used as an argument to a REPORT statement.
roundup.p program uses ARGUMENT to declare a text argument. When invoked,
roundup.p uses the argument as the name of a decimal variable. The calling program passes the name of the variable to give the called program access to all the values of the dimensioned variable. When the calling program passed the variable itself, instead of its name, only a single value would have been accessible to the called program. This program does not return a value; it produces a report.
DEFINE roundup.p PROGRAM INTEGER PROGRAM ARGUMENT varname TEXT Report Down Line Across Month: Heading 'VARNAME' - IF INTPART(&varname) EQ &varname - THEN &varname ELSE INTPART(&varname) + 1 END
The following statements
LIMIT division TO 1 LIMIT month TO 1 TO 4 DECIMALS = 0 CALL roundup.p('actual')
produce the following report.
DIVISION: CAMPING ----------------- Varname------------------ -------------------MONTH------------------- LINE Jan95 Feb95 Mar95 Apr95 -------------- ---------- ---------- ---------- ---------- revenue 533,363 572,797 707,198 968,858 cogs 360,811 400,902 478,982 641,716 gross.margin 172,553 171,895 228,217 327,143 marketing 37,370 38,867 51,224 69,439 selling 89,008 86,458 102,233 139,567 r.d 24,308 23,400 39,943 57,186 opr.income 21,868 23,171 34,819 60,952 taxes 15,971 16,320 23,030 27,584 net.income 5,898 6,851 11,789 33,368
Another way to produce the same report is to write a user-defined function that can be used as an argument to the REPORT statement as illustrated in the following program named
DEFINE roundup.f PROGRAM INTEGER PROGRAM ARGUMENT realval DECIMAL IF realval EQ INTPART(realval) THEN RETURN INTPART(realval) ELSE RETURN INTPART(realval) + 1 END
The following statements
LIMIT division TO 1 LIMIT month TO 1 TO 4 DECIMALS = 0 REPORT DOWN line ACROSS month: roundup.f(actual)
produce the following report.
DIVISION: CAMPING ------------ ROUNDUP.F(ACTUAL)------------- -------------------MONTH------------------- LINE Jan95 Feb95 Mar95 Apr95 -------------- ---------- ---------- ---------- ---------- revenue 533,363 572,797 707,198 968,858 cogs 360,811 400,902 478,982 641,716 gross.margin 172,553 171,895 228,217 327,143 marketing 37,370 38,867 51,224 69,439 selling 89,008 86,458 102,233 139,567 r.d 24,308 23,400 39,943 57,186 opr.income 21,868 23,171 34,819 60,952 taxes 15,971 16,320 23,030 27,584 net.income 5,898 6,851 11,789 33,368
roundup.f program with the
roundup.f returns a value; it does not produce a report.)