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:

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.