PL/SQL   «Prev  Next»

Lesson 1

Querying and modifying LOBs

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.

Learning bridge to [PL/SQL Variable: LOB]

The second course in the Oracle8 series addresses [PL/SQL Variable: LOB]. If you need a refresher, read through the following material.

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:
  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.

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:
  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

Primary Interfaces

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.

Module objectives

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.