Returning Values from Another Database

If an SPL function uses the Return clause to return values from another database of the local HCL OneDB™ instance, the following data types are supported as the returned data type:
  • Built-in data types that are not opaque
  • Most of the built-in opaque data types, as listed in Data Types in Cross-Database Transactions
  • DISTINCT of the built-in types that are referenced in the two lines above
  • DISTINCT of any DISTINCT data type in this list
  • Any opaque user-defined type (UDT) that is cast explicitly to one of the built-in data types in this list.

The definitions of the UDF and of the type hierarchies, casts, DISTINCT types, and UDTs must be exactly the same in each of the participating databases. The same data-type restrictions apply to a value that an external function returns from another database of the local HCL OneDB instance. For more information about data types that are supported in distributed operations across two or more databases of the same database server, see Data Types in Cross-Database Transactions.

UDRs can return only the following data types from tables in databases of other database servers:
  • Any non-opaque built-in data type
  • BOOLEAN
  • LVARCHAR
  • DISTINCT of non-opaque built-in types
  • DISTINCT of BOOLEAN
  • DISTINCT of LVARCHAR
  • DISTINCT of any DISTINCT type that appears in this list.

UDRs can return these DISTINCT types from databases of other HCL OneDB instances only if the DISTINCT types are cast explicitly to built-in types. The definitions of the DISTINCT data types, their type hierarchies, and their casts must be exactly the same in all databases that participate in the distributed operations. For queries or other DML operations in cross-server UDRs that use the data types in the preceding list as parameters or as returned data types, the UDR must be defined in each participating database, and the participating HCL OneDB instances must support the data type as a returned value in cross-server operations.

For additional information about the data types that HCL OneDB can access in distributed operations, see Data Types in Distributed Queries.