Retrieving the number of array elements

Retrieving the number of array elements in a simple array can most easily be done by using the CARDINALITY function and retrieving the maximum allowed size of an array can be done using the MAX_CARDINALITY function.

Before you begin

About this task

You would perform this task within SQL PL code in order to access a count value of the number of elements in an array. You might access the array element value as part of an assignment (SET) statement or access the value directly within an expression.

Procedure

  1. Define a SET statement.
    1. Declare and specify a variable name of type integer that will hold the cardinality value.
    2. Specify the assignment symbol, '='.
    3. Specify the name of the CARDINALITY or MAX_CARDINALTIY function and within the required brackets, the name of the array.
  2. Execute the SET statement.

Results

If the SET statement executes successfully, the local variable should contain the count value of the number of elements in the array.

Example

The following is an example of two SET statements that demonstrate these assignments:
SET card = CARDINALITY(arrayName);
	
SET maxcard = MAX_CARDINALITY(arrayName);

What to do next

If the SET statement failed to execute successfully:
  • Verify the SQL statement syntax of the SET statement and execute the statement again.
  • Verify that the local variable is of the integer data type.
  • Verify that the array was created successfully and currently exists.