LOB values as UDF parameters

The user-defined functions (UDFs) can be defined with LOB type parameters and return LOB results. The LOB types can be BLOB, CLOB, or DBCLOB.

About this task

When you pass a LOB value as an argument to a function, the entire LOB value is stored by the database manager before the function is invoked, even if the source of the value is a LOB locator host variable. In the following C language application example, either host variable :clob150K or :clob_locator1 is valid as an argument for a function whose corresponding parameter is defined as CLOB(500K):
     EXEC SQL BEGIN DECLARE SECTION; 
       SQL TYPE IS CLOB(150K) clob150K ;        /* LOB host var */ 
       SQL TYPE IS CLOB_LOCATOR clob_locator1;  /* LOB locator host var */ 
       char                   string[40];       /* string host var */ 
     EXEC SQL END DECLARE SECTION;
The following CREATE FUNCTION statement example takes a CLOB(500K) parameter:
     CREATE FUNCTION FINDSTRING (CLOB(500K, VARCHAR(200)))
       ...
Both of the following invocations of the FINDSTRING function are valid in the program:
     ... SELECT FINDSTRING (:clob150K, :string) FROM ... 
     ... SELECT FINDSTRING (:clob_locator1, :string) FROM ...

UDF parameters or results, which have one of the LOB types can be created with the AS LOCATOR modifier. In this case, the entire LOB value is not materialized prior to invocation. Instead, a LOB LOCATOR is passed to the UDF, which can then use SQL to manipulate the actual bytes of the LOB value.

You can also use this capability on UDF parameters or results, which have distinct types that are based on a LOB. Note that the argument to such a function can be any LOB value of the defined type; it does not have to be a host variable that is defined as one of the LOCATOR types. The use of host variable locators as arguments is completely orthogonal to the use of AS LOCATOR in UDF parameters and result definitions.