17.10 - Passing LOB Data in the Intermediate Aggregate Storage Area - 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)

A locator is valid only during the execution of the UDF to which it is passed as an argument. Because of this limitation, a locator cannot be passed in the intermediate aggregate storage area that aggregate functions use to accumulate summary information.

Instead, an aggregate function must convert a locator to a persistent object reference, a reference that is valid over the execution of the SQL request.

The sqltypes_td.h header file defines a persistent object reference as a LOB_REF type:

typedef struct LOB_REF{
    unsigned char data[50];

A LOB_REF is not valid outside of the request in which it is created. If you attempt to pass a LOB_REF out of the request, perhaps by storing it in a table, and later attempt to convert it back to a locator, the function will fail.

To convert a locator to or from a persistent object reference that can be passed in the intermediate aggregate storage area, Teradata provides the following library functions:
  • FNC_LobLoc2Ref
  • FNC_LobRef2Loc
detailed information on FNC_LobLoc2Ref and FNC_LobRef2Loc, including examples of how to use the functions C Library Functions
a complete example of an aggregate function using LOBs C Aggregate Function Using LOBs