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
: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;
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.