8.1.1 DBMS_LOB Programs

Table 8.1 summarizes the programs available in DBMS_LOB.

Table Table 8.2 shows which LOB types you can manipulate with the individual DBMS_LOB programs. For an explanation of these LOB types, see the section Section 8.2, "LOB Concepts"" later in this chapter.

8.1.2 DBMS_LOB Exceptions

Table Table 8.3 summarizes the exceptions declared by DBMS_LOB.

8.1.3 DBMS_LOB Nonprogram Elements

Table Table 8.4 summarizes the constants declared by the DBMS_LOB package.

8.1.4 About the Examples

This chapter contains many examples of DBMS_LOB usage. For my examples, I use tables called my_book_files and my_book_text, which contain (or point to) large volumes of text for a book. The structures of these tables follow:

/* Filename on companion disk: lobtabs.sql */*
CREATE TABLE my_book_files (
   file_descr VARCHAR2(100),
   book_file BFILE);

CREATE TABLE my_book_text (
   chapter_descr VARCHAR2(100),
   chapter_text CLOB);

Often, I'll query one of the fields from the table for a given chapter (chapter_desc) value. To avoid repetition of code, here are the implementations of functions that will be used throughout the examples:

/* Filename on companion disk: lobfuncs.sql */*
CREATE OR REPLACE FUNCTION book_file (chapter_in IN VARCHAR2) 
   RETURN BFILE
IS
   CURSOR book_cur
   IS
      SELECT book_file
        FROM my_book_files
       WHERE file_descr = chapter_in;
   book_rec book_cur%ROWTYPE;
BEGIN
   OPEN book_cur;
   FETCH book_cur INTO book_rec;
   CLOSE book_cur;
   RETURN book_rec.book_file;
END;
/
CREATE OR REPLACE FUNCTION book_text (chapter_in IN VARCHAR2) 
   RETURN CLOB
IS
   CURSOR book_cur
   IS
      SELECT chapter_text
        FROM my_book_text
       WHERE chapter_descr = chapter_in;
   book_rec book_cur%ROWTYPE;
BEGIN
   OPEN book_cur;
   FETCH book_cur INTO book_rec;
   CLOSE book_cur;
   RETURN book_rec.chapter_text;
END;
/
CREATE OR REPLACE FUNCTION book_text_forupdate (chapter_in IN VARCHAR2) 
   RETURN CLOB
IS
   CURSOR book_cur
   IS
      SELECT chapter_text
        FROM my_book_text
       WHERE chapter_descr = chapter_in
         FOR UPDATE;
   book_rec book_cur%ROWTYPE;
BEGIN
   OPEN book_cur;
   FETCH book_cur INTO book_rec;
   CLOSE book_cur;
   RETURN book_rec.chapter_text;
END;
/

In several of the examples, I'll compare before and after "images" of LOB content using the following statements (stored in the compare_text.sql file):

   SELECT chapter_descr, chapter_text
     FROM my_book_text
    WHERE chapter_descr = '&1'
   ROLLBACK;

   EXEC DBMS_OUTPUT.PUT_LINE ('Rollback completed');

   SELECT chapter_descr, chapter_text
     FROM my_book_text
    WHERE chapter_descr = '&1'
END;
/

NOTE: It's a good practice to include exception handlers in any program working with LOBs to trap and deal with LOB-related errors. Not all of the programs and anonymous blocks shown in this chapter include exception handlers, but that is done only to reduce overall code volume.