17.10 - Accessing the Value of Array Elements - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL External Routine Programming

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

Teradata provides the following library functions for retrieving the element values of an ARRAY argument or to retrieve UDT handles for ARRAY arguments whose element type is UDT.

  • FNC_GetArrayElements
  • FNC_GetUDTHandles

To access the value of one or more ARRAY elements, follow these steps:

  1. Call FNC_GetArrayTypeInfo to find out the number of elements in the ARRAY argument. Alternatively, you can call FNC_GetArrayElementCount to find out the total number of elements that are currently present in the ARRAY argument.
  2. Allocate and initialize a new NullBitVector array. For details, see Checking and Setting the NullBitVector.
  3. Allocate the result buffer which FNC_GetArrayElements will use to return the element values.
  4. Allocate the structure that provides the index to the set of ARRAY elements that you want to retrieve. Set the values of the structure to the range of elements.
  5. Call FNC_GetArrayElements to retrieve the values of the elements in the specified range, passing in the result buffer and the NullBitVector to the function. FNC_GetArrayElements returns the values of the requested elements in the result buffer. Any elements that are NULL will not have their value returned, but will instead have their presence bit set to 0 in the NullBitVector.
  6. Call FNC_CheckNullBitVector or FNC_CheckNullBitVectorByElemIndex with the modified NullBitVector to interpret the results returned by FNC_GetArrayElements.
  7. Call FNC_free to release allocated resources once you have processed the data.

For details, see FNC_GetArrayElements.

If the element type of the ARRAY argument is UDT, you can call FNC_GetUDTHandles instead of FNC_GetArrayElements to access the element values. FNC_GetUDTHandles returns one or more UDT handles which you can pass to the appropriate UDT interface library functions to access or set the value (or attribute values) of a UDT.

For details, see FNC_GetUDTHandles and UDT Interface.