8.1.1 DBMS_LOB Programs

Table 8.1 summarizes the programs available in DBMS_LOB.

Table 8.1: DBMS_LOB Programs

Name	Description	Use in SQL
APPEND	Appends the contents of a source internal LOB to a destination internal LOB	No
COMPARE	Compares two LOBs of the same type; parts of LOBs can also be compared	Yes
COPY	Copies all or part of the contents of a source internal LOB to a destination internal LOB	No
ERASE	Erases all or part of an internal LOB	No
FILECLOSE	Closes an open BFILE	No
FILECLOSEALL	Closes all open BFILEs	No
FILEEXISTS	Checks if a given file exists	Yes
FILEGETNAME	Returns directory alias and filename of given file locator	No
FILEOPEN	Opens a BFILE for read-only access	No
FILEISOPEN	Determines if a BFILE was opened with the given file locator	Yes
GETLENGTH	Returns the length of the input LOB; length is in bytes for BFILEs and BLOBs; length is in characters for CLOBs and NCLOBs	Yes
INSTR	Returns matching offset location in the input LOB of the Nth occurrence of a given pattern	Yes
LOADFROMFILE	Loads all or part of external LOB to internal LOB	No
READ	Provides piece-wise read access to a LOB	No
SUBSTR	Provides piece-wise read access to a LOB	Yes
TRIM	Trims the contents of an internal LOB to the length specified by the newlenparameter	No
WRITE	Writes a given number of bytes or characters to an internal LOB at a specified offset	No

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 8.2: DBMS_LOB Programs Can Manipulate These LOB Types

Program	BFILE	BLOB	CLOB	NCLOB
APPEND		X	X	X
COMPARE	X	X	X	X
COPY		X	X	X
ERASE		X	X	X
FILECLOSE	X
FILECLOSEALL	X
FILEEXISTS	X
FILEGETNAME	X
FILEISOPEN	X
FILEOPEN	X
GETLENGTH	X	X	X	X
INSTR	X	X	X	X
LOADFROMFILE	X	X	X	X
READ	X	X	X	X
SUBSTR	X	X	X	X
TRIM		X	X	X
WRITE		X	X	X

8.1.2 DBMS_LOB Exceptions

Table Table 8.3 summarizes the exceptions declared by DBMS_LOB.

Table 8.3: DBMS_LOB Exceptions

Exception	SQLCODE	Cause
INVALID_ARGVAL	-21560	DBMS_LOB expects a valid argument to be passed, but the argument was NULL or invalid. Example: FILEOPEN is passed an invalid open mode. Example: a positional or size argument is outside of the range 1 through (4 gigabytes-1).
ACCESS_ERROR	-22925	An attempt to read or write beyond maximum LOB size has occurred.
NOEXIST_DIRECTORY	-22285	The directory specified does not exist in the data dictionary.
NOPRIV_DIRECTORY	-22286	The user does not have the required privileges on either the specified directory object or the specified file.
INVALID_DIRECTORY	-22287	The directory specified is not valid or has been modified by the database administrator since the last access.
OPERATION_FAILED	-22288	An operation attempted on a file failed.
UNOPENED_FILE	-22289	An operation was performed on a file that was not open.
OPEN_TOOMANY	-22290	The maximum number of open files has been reached. This maximum is set via the SESSION_MAX_OPEN_FILES database initialization parameter. The maximum applies to many kinds of files, not only BFILES; for example, it applies to files opened using the UTL_FILE package.

8.1.3 DBMS_LOB Nonprogram Elements

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

Table 8.4: DBMS_LOB Constants

Element Name	Type	Value
FILE_READONLY	CONSTANT BINARY_INTEGER	Zero. Mode used to open files.
LOBMAXSIZE	CONSTANT INTEGER	4,294,967,295 (4 gigabytes-1). Positional and size arguments cannot exceed this value.

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.