Within an OLAP DML program, the ARGFR function lets you reference the arguments that are passed to a program. The function returns a group of one or more arguments, beginning with the specified argument number, as a single text value. You can use ARGFR only within a program that is invoked as a command, not as a user-defined function or with a CALL statement.


Typically, users use an ARGUMENT statement to define arguments in a program, thereby negating the need for using the ARGFR function to reference arguments passed to the program. For more information on how to use the ARGUMENT to define arguments that are passed to a program, see "Declaring Arguments that Will be Passed Into a Program" .

Return Value






The number by position of the first argument in the group of arguments you want to reference. ARGFR(1) returns the first argument and all subsequent arguments, ARGFR(2) returns the second argument and all subsequent arguments, and so forth. When there are fewer than n arguments, ARGFR returns a null value. ARGFR also returns a null value when n is 0 (zero) or negative.


Example 10-5 Passing Arguments Using ARG and ARGFR

Suppose you have a program that produces a sales report. You want to be able to produce this report for any product and any period of months, so you do not want to limit the product and month dimensions to specific values in the program. Instead, you can use the LIMIT command using ARG for the product argument and an ARGFR function for the month argument. This way, these items can be specified when the program is run.

When ARGFR is included in the LIMIT command preceded by an ampersand (&), Oracle OLAP substitutes the values of &ARGFR before the command is executed and, as a result, treats the whole argument as a phrase of the LIMIT command. The salesrprt program has a LIMIT command that includes &ARGFR.

PUSH product month district
TRAP ON cleanup
LIMIT month TO &ARGFR(2)
LIMIT district TO ALL
REPORT grandtotals DOWN district sales
POP product month district

The command line for the salesrpt program must include two or more arguments. The first argument is the product for the report, and the second and subsequent arguments are the months. In the LIMIT month statement, the &ARGFR(2) function returns the months that were specified as arguments on the command line.

The following statement executes the salesrpt program, specifying Jan96, Feb96, Mar96, and Apr96 for the values of month.

salesrpt 'Canoes' 'Jan96' TO 'Apr96'

The statement produces the following output.

DISTRICT    Jan96     Feb96      Mar96     Apr96   
------- ---------- ---------- ---------- --------- 
Boston  70,489.44  82,237.68  97,622.28 134,265.60 
Atlanta 56,271.40  61,828.33  77,217.62 109,253.38 
Chicago 48,661.74  54,424.94  68,815.71  93,045.46 
Dallas  35,244.72  40,218.43  46,810.68  64,031.28 
Denver  44,456.41  50,623.19  57,013.01  78,038.13 
Seattle 67,085.12  74,834.29  87,820.04 119,858.56 
       ---------- ---------- ---------- ---------- 
       322,208.83 364,166.86 435,299.34 598,492.41 
       ========== ========== ========== ========== 

The following statement specifies the first three months of 1996.

salesrpt 'Tents' quarter 'Q1.96'

The statement produces the following output.

DISTRICT          Jan96     Feb96      Mar96
-------------- ---------- ---------- ---------
Boston         50,808.96  34,641.59  45,742.21
Atlanta        46,174.92  50,553.52  58,787.82
Chicago        31,279.78  31,492.35  42,439.52
Dallas         50,974.46  53,702.75  71,998.57
Denver         35,582.82  32,984.10  44,421.14
Seattle        45,678.41  43,094.80  54,164.06
              ----------  ---------- ---------
              260,499.35 246,469.11 317,553.32
              ========== ========== ==========