Figure 12-3. A LOB locator points to its associated large object data within the database
This is different from the way in which other database column types work. Database LOB columns store LOB locators, and those locators point to the real data stored elsewhere in the database. Likewise, PL/SQL LOB variables hold those same LOB locators, which point to LOB data within the database. To work with LOB data, you first retrieve a LOB locator, and you then use a built-in package named DBMS_LOB to retrieve and/or modify the actual LOB data. For example, to retrieve the binary photo data from the falls_photo BLOB column used in the previous example, you would go through the following steps:
1. Issue a SELECT statement to retrieve the LOB locator for the photo you wish to display.
2. Open the LOB via a call to DBMS_LOB.OPEN.
3. Make a call to DBMS_LOB.GETCHUNKSIZE to get the optimal chunk size to use when reading (and writing) the LOB's value.
4. Make a call to DBMS_LOB.GETLENGTH to get the number of bytes or characters in the LOB value.
5. Make multiple calls to DBMS_LOB.READ in order to retrieve the LOB data.
6. Close the LOB, if you previously opened it.
Not all of these steps are necessary, and don't worry if you don't understand them fully right now. We'll explain all the steps and operations shortly.
| Oracle's LOB Documentation If you are working with LOBs, we strongly recommend that you familiarize yourself with the following portions of Oracle's documentation set: · Oracle9i Application Developer's Guide—Large Objects (LOBs). Contains complete information on LOB programming. · Oracle9i Supplied PL/SQL Packages and Types Reference. See the chapter on the DBMS_LOB package. · Oracle9i SQL Reference. The "Datatypes" section in Chapter 2, Basic Elements of Oracle SQL, contains important information about LOBs. This is not an exhaustive list of LOB documentation, but you'll find all the essential information in these sources. |
LOB locators and the need to use the DBMS_LOB package add a certain amount of complexity to working with LOBs as opposed to working with other datatypes. However, there are good reasons for the approach Oracle chose. Remember that LOB columns can hold up to four gigabytes of data. Imagine for a moment what might happen without LOB locators if you selected several 4 GB LOB columns in one SELECT statement. First off, your PL/SQL program would need to retrieve all that LOB data as part of executing the SELECT statement. Worse yet, you'd need enough memory to hold all that data. And what if you didn't really need to see all the LOB data in a particular column, but only part of it? LOB locators enable SQL statements involving LOB columns to execute more quickly because only the locators are moved back and forth. Once you have the locator, you can retrieve all of a LOB's data or only a small portion of the data. If you need to see only 100 bytes out of a 4 GB LOB, you're much better off retrieving just the 100 bytes you need instead of retrieving the entire four gigabytes.
The bottom line is that LOB columns hold pointers, not data, and those pointers are what you get when you SELECT a LOB column into a PL/SQL LOB variable. This leads to the rather interesting issue of empty LOBs versus NULL LOBs.