Why Not Use LONGs?

In Oracle7, most applications storing large amounts of unstructured data used the LONG or LONG RAW data type.

Oracle8i and Oracle9i's support for LOB data types is preferred over support for LONG and LONG RAWs in Oracle7 in the following ways:

• LOB Capacity: With Oracle8 and Oracle8i, LOBs can store up to 4GB of data. This doubles the 2GB of data that LONG and LONG RAW data types could store.

• Number of LOB columns per table: An Oracle8, Oracle8i, or Oracle9i table can have multiple LOB columns. Each LOB column in the same table can be of a different type. In Oracle7 Release 7.3 and higher, tables are limited to a single LONG or LONG RAW column.

• Random piece-wise access: LOBs support random access to data, but LONGs support only sequential access.

LOB Columns

Note: LOBs can also be object attributes.

LOB (BLOB, CLOB, NCLOB, or BFILE) column types store values or references, called locators. Locators specify the location of large objects.

LOB Columns Do Not Only Store Locators!

In LOB columns, the LOB locator is stored in-line in the row. Depending on the user-specified SQL Data Definition Language (DDL) storage parameters, Oracle9i can store small LOBs, less than approximately 4K in-line in the table. Once the LOB grows bigger than approximately 4K Oracle9i moves the LOB out of the table into a different segment and possibly even into a different tablespace. Hence, Oracle9i sometimes stores LOB data, not just LOB locators, in-line in the row.

Again note that LOBs can be object attributes.

BLOB, CLOB, and NCLOB data is stored out-of-line inside the database. BFILE data is stored in operating system files outside the database. Oracle9i provides programmatic interfaces and PL/SQL support for access to and operation on LOBs.