PL/SQL   «Prev  Next»

Lesson 5 Retrieving LOB data
Objective Write PL/SQL command to retrieve and display LOB data.

Retrieving LOB data

In this lesson, we will explore the different ways to use DBMS_LOB to retrieve and view LOB data from an object table. DBMS_LOB allows you to manipulate LOBs from within PL/SQL and SQL statements.
LOB data cannot be displayed within SQL*Plus. It requires a front-end client software, such as Oracle Developer 2000, VisualBasic, JDeveloper, or JBuilder. However, the data in another format can be displayed, such as the number of bytes stored within the LOB column or the length of the LOB column, which are made available through the use of DBMS_LOB package.
The following simulateion uses a simple SELECT statement to retrieve the length of a LOB column for all the records within the object table:

LOB datatypes

In place of LONG and LONG RAW, you can use the LOB datatypes (BLOB, CLOB, NCLOB, and BFILE) for storage of long data. Although Oracle still allows LONG and LONG RAW columns to be created, Oracle recommends that you create new columns using the LOB datatypes and that you convert existing LONG and LONG RAW columns to CLOB or BLOB columns, respectively. If you use one of these datatypes to store large objects (LOBs), you can take advantage of new capabilities for viewing and manipulating the data.

Available Datatypes

Four types of LOBs are supported:
LOB Datatype Description
BLOB Binary LOB. Binary data stored in the database.
CLOB Character LOB. Character data stored in the database.
BFILE Binary File. Read-only binary data stored outside the database, the length of which is limited by the operating system.
NCLOB A CLOB column that supports a multibyte character set.

In the next lesson, we will explore the techniques and tools available for inserting LOB data.Now that you have learned how to retrieve LOB data, try it with the following tutor-based exercise. In this exercise, you are required to apply the DBMS_LOB package to build an example of viewing the length of a LOB column. The function is DBMS_LOB.GETLENGTH, which requires the name of the variable, which stores the LOB column as a parameter.
Object Oriented Databases