LOB datatypes in Oracle

Large objects (also called LOBs) come in two flavors: internal lobs and external lobs.

There are three types of internal lobs:

• blob:
  Binary LOB
• clob:
  Character LOB (single byte)
• nclob:
  Character LOB (multi byte)

Internal LOBS are subject to transactions, that is, their value can be rolled back.

Each (internal) lob column within a table has an associated lobindex and lobsegment. The name of these can be viewed through xxx_lobs.

A lob can either be stored along the row to which it belongs (called in row storage) or in the lobsegment (called out of row storage). The maximum size for in row lobs is 3964 bytes.

A lob's chunk size determines the granularty of allocation for out of row lobs.

An external lob is stored in a file within the filesystem (not in a tablespace) on which the Oracle server runs. They are also called BFILEs.

A BFILE is not subject to transactions.

Every LOB (that is, the lob's value) has a locator associated with it. This locator is used to actually find the LOB's value. A select statement that contains a LOB in the select list returns the locator rather than the lob's value.

A locator is initializes with either empty_blob() or empty_clob.

In order to manipulate the lob's value, dbms_lob is used.

See also On reading blobs from and writing blobs to a file.

Other datatypes