Oracle Database 10g SQL (Osborne ORACLE Press Series)

There are four LOB types:

• CLOB    The character LOB type, which is used to store character data.

• NCLOB    The national language character LOB type, which is used to store multiple byte character data (typically used for non-English characters ).

• BLOB    The binary LOB type, which is used to store binary data.

• BFILE    The binary FILE type, which is used to store pointers to files located in the file system ”that is, outside of the database. These external files can be on a hard disk, a CD, a DVD, or through any other device that is accessible through the computer s file system.

As mentioned earlier, prior to Oracle8 your only choice for storing large amounts of character or binary data was to use the LONG and LONG RAW types (for smaller binary files, you could also use the RAW type). Columns created using CLOB and BLOB types have three advantages over those created using the older LONG and LONG RAW types:

• LOB columns can store up to 128 terabytes of data. This is far more data than you can store in a LONG and LONG RAW column, which may only store up to 2 gigabytes of data. Note: The RAW type may store up to 4 kilobytes of data.

• A table can have multiple LOB columns, but a table can only have one LONG or LONG RAW column.

• LOB data can be accessed in random order; LONG and LONG RAW data can only be accessed in sequential order.

A LOB consists of two parts :

• The LOB locator    A pointer that specifies the location of the LOB content

• The LOB content    The actual character or byte data stored in the LOB

Depending on the size of the LOB content, the actual data will either be stored in the table or out of the table. If the LOB content is less than 4 kilobytes in size , the content is stored in the table containing the LOB column. If it s bigger, the content is stored outside the table. With BFILE columns, only the locator is stored in the database ”the locator points to the external file containing the LOB content.