1. Administering Fast Formulas
  2. Array Variables

Array Variables

You can use arrays for input, output, and local formula variables. These array variables can store date, number, or text values. Arrays are similar to PL/SQL index-by tables.

Array Indexes

Here are some aspects of array indexes that you should be aware of:

  • The index types are either text or number.

  • Text indexes are upper case unique.

  • Gaps in index value sequences are permitted.

  • Number indexes are truncated to remove any fractional part.

  • You may iterate an array in an index either forwards or backward.

Array Data Types

You specify array types as DATA_TYPE_INDEX_TYPE. Arrays are of these data types:

  • NUMBER_NUMBER

  • NUMBER_TEXT

  • DATE_NUMBER

  • DATE_TEXT

  • TEXT_NUMBER

  • TEXT_TEXT

Rules for Using Arrays

Formula functions can't return arrays or take array parameters. Contexts can't be array types. If you try to reference an array value at a nonexistent index, the application returns a formula execution error. However, you can delete an array value at a nonexistent index.

Array Methods

Array methods enable you to get the first and last indexes, and to get the next or prior index. These methods return the index data type. You can specify a default value for these methods, if the required indexes don't exist. You can use array methods to determine whether an index exists or not.

Note: The array method syntax doesn't work directly with the array literal values. For example, you can't use a construct such as EMPTY_DATE_NUMBER.COUNT.

Here's a list of the array methods, including their descriptions and usage examples:

Array Method

Description

Usage Example

<name> [ <index value> ]

Get the value for an index.

V = A[1]

<name> . FIRST( <default value> )

Get the first index for an array. The default value is returned if the array is empty.

I = A.FIRST(-1)

<name> . LAST( <default value> )

Get the last index for an array.

L = B.LAST(' ')

<name> . EXISTS( <index value> )

Conditional checking if a value exists at an index. The default value is returned if the array is empty.

IF A.EXISTS(1) THEN

<name> . NEXT( <index value> , <default index value> )

Get the next index given an index position. The default value is returned if there is no next index.

N = A.NEXT(1)

<name> . PRIOR( <index value> , <default index value> )

Get the prior index given the index position. The default value is returned if there is no prior index.

P = B.PRIOR('Two')

<name> , COUNT

Numeric method to count the array elements.

C = A.COUNT

<name , DELETE( <index value> )

Delete the element at an index position.

B.DELETE('three')

<name> , DELETE()

Delete all elements.

B.DELETE()

Iterating Through an Array

In this example, A is an array variable with a NUMBER index. -1234 is known to be an invalid index for A, so it's used as a default value when the FIRST and NEXT calls can't find an index.

/* -1234 is not a valid index for A in this instance, so use as default. */
NI = A.FIRST(-1234)
WHILE A.EXISTS(NI) LOOP
(
  VA = A[NI] /* Do some processing with element at index NI. */
  NI = A.NEXT(NI,-1234) /* Go to next index. */
)

In this example, B is an array variable with a TEXT index. -1234 is known to be an invalid index for B, so it's used as a default value when the FIRST and NEXT calls can't find an index.

/* 'No Index' is not a valid index for A in this instance, so use as default. */
TI = B.FIRST('No Index')
WHILE B.EXISTS(TI) LOOP
(
  VB = B[TI] /* Do some processing with element at index TI. */
  TI = B.NEXT(TI, 'No Index') /* Go to next index. */
)
The following example iterates backwards from through an array C with a NUMBER inde.
/* -1234 is not a valid index for C in this instance, so use as default. */
NI = C.LAST(-1234)
WHILE C.EXISTS(NI) LOOP
(
  VC = C[NI] /* Do some processing with element at index NI. */
  NI = C.PRIOR(NI,-1234) /* Go to prior index. */)

Related Topics