1 Introduction to Large Objects

This chapter introduces Large Objects (LOBs) and discusses how LOB datatypes are used in application development. This chapter contains these topics:

What Are Large Objects?


Why Use Large Objects?



  • Simple structured data.

    This data can be organized into simple tables that are structured based on business rules.

  • Complex structured data

    This kind of data is complex in nature and is suited for the object-relational features of the Oracle database such as collections, references, and user-defined types.

  • Semi-structured data

    This kind of data has a logical structure that is not typically interpreted by the database. For example, an XML document that is processed by your application or an external service, can be thought of as semi-structured data. The database provides technologies such as Oracle XML DB, Advanced Queueing, and Messages to help your application work with semi-structured data.

  • Unstructured data

    This kind of data is not broken down into smaller logical structures and is not typically interpreted by the database or your application. A photographic image stored as a binary file is an example of unstructured data.



  • Can store unstructured and semi-structured data in an efficient manner.

  • Is optimized for large amounts of data.

  • Provides a uniform way of accessing data stored within the database or outside the database.

Using LOBs for Semi-structured Data




Using LOBs for Unstructured Data




Why Not Use LONGs?




  • LOB Capacity: LOBs can store much larger amounts of data. LOBs can store 4GB of data or more depending on you system configuration. LONG and LONG RAW types are limited to 2GB of data.

  • Number of LOB columns in a table: A table can have multiple LOB columns. LOB columns in a table can be of any LOB type. In Oracle Database 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.

  • LOBs can also be object attributes.

Different Kinds of LOBs




Internal LOBs


Persistent and Temporary LOBs




External LOBs and the BFILE Datatype




You typically use BFILEs to hold:

  • Binary data that does not change while your application is running, such as graphics.

  • Data that is loaded into other large object types, such as a BLOB or CLOB where the data can then be manipulated.

  • Data that is appropriate for byte-stream access, such as multimedia.

  • Read-only data that is relatively large in size, to avoid taking up large amounts database tablespace.




Introducing LOB Locators




Database Semantics for Internal and External LOBs


  • Internal LOBs use copy semantics.

    With copy semantics, both the LOB locator and LOB value are logically copied during insert, update, or assignment operations. This ensures that each table cell or each variable containing a LOB, holds a unique LOB instance.

  • External LOBs use reference semantics.

    With reference semantics, only the LOB locator is copied during insert operations. (Note that update operations do not apply to external LOBs as external LOBs are read-only. This is explained in more detail later in this section.)

Large Object Datatypes


Table 1-1 Large Object Datatypes

SQL Datatype Description
BLOB Binary Large Object


CLOB Character Large Object


NCLOB National Character Set Large Object

Stores string data in National Character Set format. Used for large strings or documents in the National Character Set. Supports characters of varying width format.

BFILE External Binary File



Object Datatypes and LOBs

You can declare LOB datatypes as fields, or members, of object datatypes. For example, you can have an attribute of type CLOB on an object type. In general, there is no difference in the usage of a LOB instance in a LOB column and the usage of a LOB instance that is a member or of an object datatype. Any difference in usage is called out when it applies. When used in this guide, the term LOB attribute refers to a LOB instance that is a member of an object datatype. Unless otherwise specified, discussions that apply to LOB columns also apply to LOB attributes.

Storing and Creating Other Datatypes with LOBs


VARRAYs Stored as LOBs


  • If the VARRAY storage clause— VARRAY varray_item STORE AS —is not specified, and the declared size of varray data is more than 4000 bytes.

  • If the varray column properties are specified using the STORE AS LOB clause— VARRAY varray_item STORE AS LOB ...

XMLType Columns Stored as CLOBs


See Also:

LOBs Used in Oracle interMedia


See Also: