Selecting LOB Values from Tables

2.3 Selecting LOB Values from Tables

You can select a LOB into a Character Buffer, a RAW Buffer, or a LOB variable for performing read and write operations.

2.3.1 Selecting a LOB into a Character Buffer or a Raw Buffer

You can directly select a CLOB or NCLOB value into a character buffer or a BLOB value. This is called the Data Interface, and is the most efficient way for selecting from a LOB column.

2.3.2 Selecting a LOB into a LOB Variable for Read Operations

You can select a persistent or temporary LOB into a LOB variable, and then use APIs to perform various read operations on it.

Following code selects a LOB Locator into a variable:

DECLARE
    perslob CLOB;
    templob CLOB;
    amt INTEGER := 11;
    buf VARCHAR(100);
BEGIN
    SELECT ad_source, substr(ad_source, 3) INTO perslob, templob FROM Print_media WHERE product_id = 1 AND ad_id = 1;
    DBMS_LOB.READ(perslob, amt, buf);
    DBMS_LOB.READ(templob, amt, buf);
END;
/

2.3.3 Selecting a LOB into a LOB Variable for Write Operations

To perform a write operation using a LOB locator, you must lock the row in the table in order to prevent other database users from writing to the LOB during a transaction.

You can use one of the following mechanisms for this operation:

Performing an INSERT or an UPDATE operation with a RETURNING clause.

See Also:
Inserting and Updating with a NULL or Empty LOB

Performing a SELECT for an UPDATE operation. The following code snippet shows how to select a LOB value to perform a write operation using UPDATE.

DECLARE
    c CLOB;
    amt INTEGER := 9;
    buf VARCHAR(100) := 'New Value';
BEGIN
    SELECT ad_sourcetext INTO c FROM Print_media WHERE product_id = 1 AND ad_id = 1 FOR UPDATE;
    DBMS_LOB.WRITE(c, amt, 1, buf);
END;
/

Using an OCI pin or lock function in OCI programs.