LOB data types

OceanBase Database supports character large object (CLOB) and binary large object (BLOB) data types.

A large object (LOB) consists of data and a locator. The locator contains a table ID and a row ID so that you can locate the row in the table on which the data corresponding to the LOB object is stored. Similar to the VARCHAR type, only the data part of a LOB object is stored, and the locator part is generated by the upper-layer interface during queries.

LOB objects can be assigned to any objects of the same type. Generally, you can use LOB objects in your applications regardless of the locator semantics. In some scenarios, the locator is required. In other scenarios, redundant locator information is allowed.

You can read and modify a LOB object by using or without using its locator.

Not using the locator

In many operations, LOB objects are treated like the VARCHAR2 and RAW types. These operations use only the data of LOB objects while ignoring their locators. Such operations include:

• SQL and procedural language (PL) built-in functions

• API operations that allow you to query data in a LOB column or insert data into a LOB column:

  • Insert character data into a CLOB column by binding variables of a LOB column or insert RAW data into a BLOB column. For example, you can insert a VARCHAR2 buffer into a CLOB column in PL.

  • Define the output in your application as character data queried from a CLOB column or RAW data queried from a BLOB column. For example, you can store queried CLOB data to a VARCHAR2 buffer in PL.

Using the locator

Use LOB API operations provided by OceanBase Database and enter LOB objects as parameters. Most of the API operations are contained in the DBMS_LOB package, including DBMS_LOB.READ, DBMS_LOB.WRITE, and DBMS_LOB.UPDATE.