Lesson 1

In this module, we will look at large objects LOBs^[1]. Prior to Oracle8, unstructured binary information of up to 2 GB could be stored using the LONG RAW data type. However, only one LONG RAW (or RAW, for long character data) column could be defined per table. With Oracle8, the LOB 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 material.

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:

Data type	Description
LONG	Stores a variable-length character string containing up to 2 gigabytes -1, or 231-1 bytes.
LONG RAW	Stores a variable-length binary string containing up to 2 gigabytes -1, or 231-1 bytes.
BLOB	Stores a binary large object (BLOB) containing up to 4 gigabytes -1, or 232-1 bytes.
CLOB	Stores a character large object (CLOB) containing up to 4 gigabytes -1, or 232-1 bytes.
NCLOB	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 (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:

1. CLOB - The CLOB (character large object) datatype is used to store large blocks of single-byte character data in the database.
2. 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)
3. BFILE - The BFILE (binary file) datatype is used to store large binary objects in operating system files outside the database.
4. 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.

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:

1. Random, piece-wise access to the LOB data
2. Transferring the LOB data in pieces or as a single unit
3. Supporting efficient logging, storage, and retrieval of LOB data

The primary interfaces that help manipulate LOBs include the PL/SQL DBMS_LOB^[2] package and the Oracle Call Interface (OCI)^[3]. 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:

1. Create object tables with LOB as the data type
2. Insert, update, and delete the records from object tables that have LOB data types
3. Load data into object tables with LOBs using SQL*Loader^[4]

In the next lesson, you will begin learning about the different types of LOBs and perform a comparative analysis between them.

[1]LOB: Large Objects are data types defined within Oracle 8i. They are used for storing upto 4 GB of data.

[2]DBMS_LOB : 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.

[3]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.

[4]SQL*Loader: This is a utility provided by Oracle to load data from text files into the Oracle database.