Oracle - LOB Datatype

> Database > Oracle Database

1 - About

The term large object (LOB) refers to a data item that is too large to be stored directly in a database table. Instead, a (locator|pointer) is stored in the database table, which points to the location of the actual data. External files are managed similarly.

Maximum size: 16 TB

The use of the old LONG data type is deprecated.

A LOB is simply a pointer. It points to an index that is stored in a LOBINDEX segment. The index points to the chunks that make up the LOB that are stored in LOBSEGMENT segment.

See Oracle Database - DBMS_LOB Package

Advertising

3 - Type

3.1 - BLOB

Binary large object (BLOB): For unstructured binary data

SQL queries restrictions :

  • It is not allowed to use 'DISTINCT' clause in the select statement.
--EMPTY_BLOB and EMPTY_CLOB return an empty LOB locator that can be used to initialize a LOB variable
blob := EMPTY_BLOB();
-- From Varchar
blob := TO_BLOB(UTL_RAW.CAST_TO_RAW('abc'));

3.2 - CLOB

  • Character large object (CLOB): For character data
-- EMPTY_BLOB and EMPTY_CLOB return an empty LOB locator that can be used to initialize a LOB variable
clob := EMPTY_CLOB();
-- To_clob
clob := TO_CLOB('abc');

3.3 - BFILE

  • Binary file (BFILE): For external files