Oracle Built-in Packages

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

8. Managing Large Objects

Contents:
Getting Started with DBMS_LOB
LOB Concepts
DBMS_LOB Interface

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 PackagesNext: 8.2 LOB Concepts
7.3 DBMS_APPLICATION_INFO Examples Book Index8.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