Oracle8 and PL/SQL8 support the storage and manipulation of large objects (a.k.a. LOBs). A LOB, which can be a column in a table or an attribute of an object type, may store up to four gigabytes of data, such as character text, graphic images, video, or "raw" data. The DBMS_LOB package (new to Oracle8) provides a set of procedures and functions to access and manipulate LOBs from within PL/SQL programs.
You can also manipulate LOBs from within SQL; refer to the Oracle documentation for these SQL-specific aspects of LOB management.
The DBMS_LOB package is created when the Oracle8 database is installed. The dbmslob.sql script (found in the built-in packages source directory, as described in Chapter 1, Introduction ) contains the source code for this package's specification. This script is called by catproc.sql , which is normally run immediately after database creation. The script creates the public synonym DBMS_LOB for the package and grants EXECUTE privilege on the package to public. All Oracle users can reference and make use of this package.
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.
Table Table 8.3 summarizes the exceptions declared by DBMS_LOB.
Table Table 8.4 summarizes the constants declared by the DBMS_LOB package.
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:
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):
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.