Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

Part Number E17122-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

CALL

The CALL command invokes a program. When the program has arguments, which are always enclosed in parentheses, it passes these arguments to the called program.

Syntax

CALL program-name [(arg ...)]

Parameters

program-name

The name of the program to be called.

arg

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.

Usage Notes

Dimension Arguments

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 NA.

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.

  1. 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 (TEXT or ID), numeric (INTEGER, DECIMAL, and so on), or RELATION value. An error in one argument expression stops the process.

  2. 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 TEXT or 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 NA values.

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".

Examples

Example 9-59 Calling a Program or Function

This example illustrates how two programs, roundup.p and roundup.f, are used in different ways to evaluate data and produce output.

The 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 INTEGER. The 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 INTEGER.

The 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.

The 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 roundup.f.

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 

(Compare the roundup.f program with the roundup.p program. roundup.f returns a value; it does not produce a report.)