Setting BLOB and CLOB variables

For compiling a PL/I application for OCI, declare BLOB and CLOB variables as shown:

    DCL TEST_BLOB SQL TYPE IS BLOB(4K);

    DCL TEST_CLOB SQL TYPE IS CLOB(2M);

You can specify BLOB or CLOB host variable sizes in kilobytes (K), megabytes (M), Gigabytes (G), or as an integer value. For a Unicode CLOB, use the type DBCLOB. A large object (LOB) host variable is equivalent to this declaration:

DCL 1 name,
     5 name_length      fixed bin(31),
     5 name_data        char(<size>);

where name_data can be an array of CHARs as appropriate if the LOB is greater than 32K.

These examples show how to declare LOB locator host variables.

DCL TEST_BLOB_LOC SQL TYPE IS BLOB_LOCATOR;

DCL TEST_CLOB_LOC SQL TYPE IS CLOB_LOCATOR;

A locator host variable is effectively a pointer to a LOB value, and can generally be used wherever a LOB host variable can be used. The advantage of using locators is that the whole LOB value is not transferred between the application and the server. For example, an application could duplicate a BLOB value from one row into another row.

exec sql
    select lob_col into :test_blob_loc from mytable where key = :k1;
exec sql 
    update table2 set lob2_col = :test_blob_loc
    where key2 = :k2;