Restrictions on SQL functions

Be aware of the restrictions on SQL functions before creating them or when troubleshooting problems related to their implementation and use.

The following restrictions apply to SQL functions:
  • SQL table functions cannot contain compiled compound statements.
  • A compiled SQL scalar function that is defined as READS SQL can be invoked in a partitioned database environment, but only if it is certain to be executed in the coordinator agent.
  • A compiled SQL scalar function that is defined as CONTAINS SQL and is invoked in a partitioned database environment cannot prepare SQL statements, cannot execute CALL statement, and cannot use any construct that the SQL compiler translates to a full SQL statement before processing.
  • By definition, SQL functions cannot contain cursors defined with the WITH RETURN clause.
  • The following data types are not supported within compiled SQL functions: structured data types, LONG VARCHAR data type, and LONG VARGRAPHIC data type. The XML data type is not supported in version 10.1. The support for XML data type starts in version 10.1 Fix Pack 1.
  • In this version, use of the DECLARE TYPE statement within compiled SQL functions is not supported.
  • Compiled SQL functions (including PL/SQL functions) must not contain references to federated objects.
  • Compiled SQL functions (including PL/SQL functions) that modify SQL data can only be used as the only element on the right side of an assignment statement that is within a compound SQL (compiled) statement.
  • If a table contains a generated column expression in which the user-defined function is a compiled compound SQL, then you cannot use the LOAD utility to insert data into the table.