Oracle PL/SQL Programming, 5th Edition

Working with LOBs

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

Before getting into the meat of this section, please note that all LOB examples are based on the following table definition (which can be found in the ch13_code.sql file on the book’s web site):

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 13-2 shows the Dryer Hose, a falls near Munising frequented by ice climbers in its frozen state.

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

The table implements one column for each of the four LOB types. Photos consist of large amounts of binary data, so the falls_photo column is defined as a BLOB. Directions and descriptions are text, so those columns are CLOB and NCLOB, respectively. Normally, you’d use either CLOB or NCLOB for both, but I wanted to provide an example that used each LOB ...