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
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.
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.
- Performing an
UPDATEoperation with a
- Performing a
UPDATEoperation. The following code snippet shows how to select a LOB value to perform a write operation using
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
lockfunction in OCI programs.