Oracle PL/SQL Programming, Third Edition

Working with LOBs

The topic of working with large objects is, well, large, and we can’t begin to cover every aspect of LOB programming in this chapter. What we can and will do, however, is provide you with a good introduction to the topic of LOB programming aimed especially at PL/SQL developers. We’ll discuss some of the issues to be aware of and show examples of fundamental LOB operations. All of this, we hope, will provide you with a good foundation for your future LOB programming endeavors.

We will first introduce the different types of LOBs available to PL/SQL developers. Then it’s time to learn about LOB locators and how to create LOBs. We will show you how to use DBMS_LOB to manipulate LOB contents and also introduce new, native syntax in Oracle9i that lets you work with LOBs using built-in functions like SUBSTR directly (albeit with an impact on performance, as is noted in that section).

Before getting into the meat of this section, please note that all LOB examples are based on the following table definition:

CREATE TABLE waterfalls (
   falls_name VARCHAR2(80),
   falls_photo BLOB,
   falls_directions CLOB,
   falls_description NCLOB,
   falls_web_page BFILE);

This table contains rows about waterfalls located in Michigan’s Upper Peninsula. Figure 12-2 shows the Dryer Hose, a falls frequented by ice-climbers, in its frozen state.

Figure 12-2. The Dryer Hose in Munising, Michigan

The table ...