Oracle Large Object Datatypes


Internal Datatypes

LOB (large object datatypes). LOB objects can be accessed and manipulated using the DBMS_LOB package and have been improved greatly from Oracle7 to Oracle8. Also LOB objects can have their own distinct storage clauses within a table regardles of the table tablespace storage clause.

CREATE TABLE aTable (name VARCH2(32), resume CLOB, picture BLOB)
	LOB (resume) STORE AS (TABLESPACE resumes DISABLE STORAGE IN ROW STORAGE (INITIAL 3M NEXT 3M));

External Datatypes

LOB Storage Characteristics

A separate LOB segment is created for each LOB column defined which can have its own storage parameters and even be in a separate tablespace and thus can be stored on different devices in different datafiles. When a LOB is created in a table a LOB index and a LOB segment are created. Both the LOB index and the LOB segment can be placed in separate tablespace from that of the table in which the LOB column is placed.

The DISABLE STORAGE IN ROW clause of the CREATE TABLE command forces all rows of an internal LOB to be stored out of line. The PCTVERSION clause establishes the percentage of space that LOB value pages are allowed to use.

LOBs in SQL

Initialise LOBs using the EMPTY_BLOB(), EMPTY_CLOB() or BFILENAME() functions or by setting the LOB object column value to NULL. The same functions can be used for updating LOB columns and deletion is as in any other SQL.

INSERT INTO blobTable (id, blobObject) VALUES (323, EMPTY_BLOB());
INSERT INTO bfileTable (id, bfileObject) VALUES (122, BFILENAME ('directory', 'filename'));