home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam  


Oracle Built-in Packages

Oracle Built-in PackagesSearch this book
Previous: 7.3 DBMS_APPLICATION_INFO Examples Chapter 8 Next: 8.2 LOB Concepts
 

8. Managing Large Objects

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.

8.1 Getting Started with DBMS_LOB

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.

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.


Previous: 7.3 DBMS_APPLICATION_INFO Examples Oracle Built-in Packages Next: 8.2 LOB Concepts
7.3 DBMS_APPLICATION_INFO Examples Book Index 8.2 LOB Concepts

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference