Oracle PL/SQL Tutorial/Large Objects/Introduction

Handling Large Objects in the Database

To address performance concerns, Oracle provides two options:

You can store the large objects internally, within the database itself (CLOB, BLOB).

You can keep the objects in the file system and just store the filenames in the database (BFILE).

CLOB (character large object): to store large amounts of character (text) information.

BLOB (binary large object): to store binary (mostly video/audio) information in the database.

34. 1. Introduction 34. 1. 1. Handling Large Objects in the Database 34. 1. 2. <A href="/Tutorial/Oracle/0660__Large-Objects/LargeObjects.htm">Large Objects</a> 34. 1. 3. <A href="/Tutorial/Oracle/0660__Large-Objects/UsingCLOBsandBLOBs.htm">Using CLOBs and BLOBs</a>

Large Objects

Large objects (LOBs) may be used to store binary data, character data, and references to external files.

LOBs are widely used to store documents such as Word and PDF documents.

LOBs can store a maximum of 128 terabytes of data depending on the block size of your database.

There are four LOB types:

CLOB is the character LOB type.

CLOB is used to store character data.

NCLOB is the national language character LOB type.

NCLOB is used to store multiple byte character data (typically used for non-English characters).

BLOB is the binary LOB type.

BLOB is used to store binary data.

BFILE is the binary FILE type.

BFILE is used to store pointers to files located in the file system.

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.

A LONG and LONG RAW column 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.

Quote from:

Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback)

# Paperback: 608 pages

# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004)

# Language: English

# ISBN-10: 0072229810

# ISBN-13: 978-0072229813

34. 1. Introduction 34. 1. 1. <A href="/Tutorial/Oracle/0660__Large-Objects/HandlingLargeObjectsintheDatabase.htm">Handling Large Objects in the Database</a> 34. 1. 2. Large Objects 34. 1. 3. <A href="/Tutorial/Oracle/0660__Large-Objects/UsingCLOBsandBLOBs.htm">Using CLOBs and BLOBs</a>

Using CLOBs and BLOBs

LOB columns store a locator that points to the LOB contents.

Initializing a CLOB and BLOB

Before you can actually write content to a LOB, you must first initialize the LOB column.

You do this by calling an Oracle database function that generates and returns a value for the locator.

To initialize a CLOB or NCLOB column, you use the EMPTY_CLOB() function.

A BLOB column must be initialized using the EMPTY_BLOB() function.

34. 1. Introduction 34. 1. 1. <A href="/Tutorial/Oracle/0660__Large-Objects/HandlingLargeObjectsintheDatabase.htm">Handling Large Objects in the Database</a> 34. 1. 2. <A href="/Tutorial/Oracle/0660__Large-Objects/LargeObjects.htm">Large Objects</a> 34. 1. 3. Using CLOBs and BLOBs