16.20 - FNC_GetUDTHandles - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

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


Returns one or more UDT handles that can be used to operate on the elements of an ARRAY data type whose element type is UDT.


FNC_GetUDTHandles ( ARRAY_HANDLE  aryHandle,
                    int           numHandles,
                    void         *returnValue,
                    long          bufSize )
the handle to an ARRAY type that is defined to be an input parameter to an external routine.
int numHandles
the number of UDT handles that you want to retrieve.
void *returnValue
a pointer to a buffer that FNC_GetUDTHandles uses to return the UDT handles. You must allocate 4 bytes for each UDT handle you want to retrieve.
long bufSize
the size in bytes that was allocated for the returnValue buffer.

Usage Notes

FNC_GetUDTHandles takes aryHandle, numHandles, and bufSize as input arguments and returns the UDT handles requested in the returnValue buffer.

The ARRAY specified by aryHandle must have an element type of UDT. Before calling FNC_GetUDTHandles, allocate the returnValue buffer with enough space to hold the desired number of UDT handles. You must allocate 4 bytes for each UDT handle you want to retrieve, that is 4 * numHandles. Be sure to release allocated resources after you process the data.

To access and set the value of a distinct UDT or attribute values of a structured UDT, see UDT Interface.

You can call FNC_GetUDTHandles to retrieve one or more UDT handles, operate on the set of UDTs, then call FNC_SetArrayElements or FNC_SetArrayElementsWithMultiValues with these UDTs, without being required to call FNC_GetArrayElements.


No more than 2000 UDT handles may be allocated during the execution of any UDF, UDM, or external stored procedure. Therefore, no more than 2000 UDT handles may be retrieved in any call to FNC_GetUDTHandles, and if other FNC calls in your routine also allocate UDT handles, this limit will be even lower.

Example Using FNC_GetUDTHandles

This example is based on the following ARRAY definition:

/*Oracle-compatible and Teradata syntax respectively: */
CREATE TYPE phonenumbers_ary AS VARRAY(5) OF CHAR(10);
CREATE TYPE phonenumbers_ary AS CHAR(10) ARRAY[5];

/* This function sets the values of a set of consecutive elements */
/* which are UDTs by first obtaining a set of UDT handles, */
/* performing operations on them, and then calling */
/* FNC_SetArrayElementsWithMultiValues. */

void setMultiElement( ARRAY_HANDLE *ary,
                      INTEGER      *result,
                      char          sqlstate[6])
    int numHandles;
    void *newValues;
    long newValuesBufSize;
    bounds_t *arrayInterval;
    NullBitVecType *NullBitVector;
    long NullBitVectorBufSize;

    numHandles = 3;
    newValuesBufSize = sizeof(UDT_HANDLE)*numHandles;
    newValues = (void*)FNC_malloc(newValuesBufSize);
    FNC_GetUDTHandles(*ary, numHandles, newValues, newValuesBufSize);

    /* Perform some operations on the UDTs using existing FNC routines.*/


    /* Set the values of arrayInterval to correspond to the range [1:3]*/
    arrayInterval = (bounds_t*)FNC_malloc(sizeof(bounds_t));
    arrayInterval[0].lowerBound = 1;
    arrayInterval[0].upperBound = 3;

    /* Allocate space for NullBitVector and set all bits to Present. */
    numHandles % 8 == 0 ? NullBitVectorBufSize = numHandles / 8 :
       NullBitVectorBufSize = numHandles / 8 + 1;

    NullBitVector = (NullBitVecType*)FNC_malloc(NullBitVectorBufSize);
    FNC_SetNullBitVector(NullBitVector, -1, 1, NullBitVectorBufSize);

    /* Set the values of the UDTs operated on above. */
    FNC_SetArrayElementsWithMultiValues(ary, arrayInterval, newValues,
       newValuesBufSize, NullBitVector, NullBitVectorBufSize);