Querying and modifying LOBs
In this module, we will look at large objects LOBs
. Prior to Oracle8, unstructured binary information of up to 2 GB could be stored using the
data type. However, only one
, for long character data) column could be defined per table. With Oracle8, the
data type is available to store large amounts of data.
The second course in the Oracle series addresses the PL/SQL Variable known as LOB. If you need a refresher, read through the following
The latest version of Oracle still supports the LOB datatype variable. However, it is recommended to use the CLOB and NCLOB data types to store large amounts of character data. The LONG and LONG RAW data types are supported only for backward compatibility.
Here is a table comparing the different LOB data types:
|Stores a variable-length character string containing up to 2 gigabytes -1, or 231-1 bytes.
|Stores a variable-length binary string containing up to 2 gigabytes -1, or 231-1 bytes.
|Stores a binary large object (BLOB) containing up to 4 gigabytes -1, or 232-1 bytes.
|Stores a character large object (CLOB) containing up to 4 gigabytes -1, or 232-1 bytes.
|Stores a Unicode character large object (NCLOB) containing up to 4 gigabytes -1, or 232-1 bytes.
As you can see, the LONG and LONG RAW data types are limited to storing up to 2 gigabytes of data. The BLOB, CLOB, and NCLOB data types can store up to 4 gigabytes of data. Additionally, the CLOB and NCLOB data types can store Unicode characters, while the BLOB and LONG RAW data types cannot.
In general, it is recommended to use the CLOB and NCLOB data types to store large amounts of character data. The LONG and LONG RAW data types should only be used for backward compatibility.
LOB datatype variable
LOB (large objects) datatype holds value, called locator, that specifies the location of a large object.
With LOB datatypes, you can store blocks of unstructured data such as text, graphic images, video clips, audio files up to 4 gigabytes in
size. LOB datatype allows efficient, random, piecewise access to the data and can be further divided into 4 categories:
- CLOB - The CLOB (character large object) datatype is used to store large blocks of single-byte character data in the database.
- BLOB - The BLOB (binary large object) datatype is used to store large binary objects in the database in line (inside the row) or out of line (outside the row)
- BFILE - The BFILE (binary file) datatype is used to store large binary objects in operating system files outside the database.
- NCLOB - The NCLOB (national language character large object) datatype is used to store large blocks of single-byte or fixed-width
multi-byte NCHAR data in the database, in line or out of line.
Review PL/SQL Variable: LOB
LOBs can store up to 4 GB of unstructured multimedia data such as graphic images, still video clips, full-motion video, and sound waveforms, enhancing the Oracle8 database in terms of the different types of data that can be stored within it. In addition, LOBs use locators as opposed to the actual data.
The Oracle8i server supports the following operations on LOBs:
- Random, piece-wise access to the LOB data
- Transferring the LOB data in pieces or as a single unit
- Supporting efficient logging, storage, and retrieval of LOB data
The primary interfaces that help manipulate LOBs include the PL/SQL DBMS_LOB
package and the Oracle Call Interface (OCI)
. There are two types of LOBs: internal and external. Internal LOBs are stored within the database, and external LOBs are stored outside the database within a specified directory or file system.
When you have completed this module, you will be able to:
- Create object tables with
LOB as the data type
- Insert, update, and delete the records from object tables that have
LOB data types
- Load data into object tables with LOBs using SQL*Loader
In the next lesson, you will begin learning about the different types of LOBs and perform a comparative analysis between them.
Large Objects are data types defined within Oracle 8i. They are used for storing upto 4 GB of data.
The DBMS_LOB package provides subprograms to operate on BLOBs, CLOBs, NCLOBs, BFILEs, and temporary LOBs.
You can use DBMS_LOB to access and manipulate specific parts of a LOB or complete LOBs.
Oracle Call Interface:
The general goal of an OCI application is to operate on behalf of multiple users. Within an n-tier configuration, multiple users send HTTP requests to the client application. The client application may need to perform some data operations that include exchanging and performing data processing.
This is a utility provided by Oracle to load data from text files into the Oracle database.