Contents

Title and Copyright Information

Preface

Documentation Accessibility

Related Documents

What's New in Large Objects?

LOB Features Introduced in Oracle Database 10g Release 2

Restrictions Removed in Oracle Database 10g Release 2

LOB Features Introduced in Oracle Database 10g Release 1

Restrictions Removed in Oracle Database 10g Release 1

LOB Features Introduced in Oracle9i Release 2

Restrictions Removed in Oracle9i Release 2

LOB Features Introduced in Oracle9i Release 1

Restrictions Removed in Oracle9i Release 1

Part I Getting Started

1 Introduction to Large Objects

What Are Large Objects?

Why Use Large Objects?

Using LOBs for Semi-structured Data

Using LOBs for Unstructured Data

Why Not Use LONGs?

Different Kinds of LOBs

External LOBs and the BFILE Datatype

Introducing LOB Locators

Database Semantics for Internal and External LOBs

Large Object Datatypes

Object Datatypes and LOBs

Storing and Creating Other Datatypes with LOBs

VARRAYs Stored as LOBs

XMLType Columns Stored as CLOBs

LOBs Used in Oracle interMedia

2 Working with LOBs

LOB Column States

Locking a Row Containing a LOB

Opening and Closing LOBs

LOB Locator and LOB Value

Using the Data Interface for LOBs

Using the LOB Locator to Access and Modify LOB Values

LOB Locators and BFILE Locators

Table print_media

Initializing a LOB Column to Contain a Locator

Initializing a Persistent LOB Column

Initializing BFILEs

Accessing a LOB Using SQL

Accessing a LOB Using the Data Interface

Accessing a LOB Using the Locator Interface

LOB Rules and Restrictions

Rules for LOB Columns

Restrictions for LOB Operations

3 Managing LOBs: Database Administration

Database Utilities for Loading Data into LOBs

Using SQL*Loader to Load LOBs

Using SQL*Loader to Populate a BFILE Column

Using Oracle DataPump to Transfer LOB Data

Managing Temporary LOBs

Managing Temporary Tablespace for Temporary LOBs

Managing BFILEs

Rules for Using Directory Objects and BFILEs

Setting Maximum Number of Open BFILEs

Changing Tablespace Storage for a LOB

Part II Application Design

4 LOBs in Tables

Creating Tables That Contain LOBs

Initializing Persistent LOBs to NULL or Empty

Setting a Persistent LOB to NULL

Setting a Persistent LOB to Empty

Initializing LOBs

Initializing Persistent LOB Columns and Attributes to a Value

Initializing BFILEs to NULL or a File Name

Restriction on First Extent of a LOB Segment

Choosing a LOB Column Datatype

LOBs Compared to LONG and LONG RAW Types

Storing Varying-Width Character Data in LOBs

Implicit Character Set Conversions with LOBs

Selecting a Table Architecture

Inline and Out-of-Line LOB Storage

Defining Tablespace and Storage Characteristics for Persistent LOBs

Assigning a LOB Data Segment Name

LOB Storage Characteristics for LOB Column or Attribute

TABLESPACE and LOB Index

Tablespace for LOB Index in Non-Partitioned Table

CACHE / NOCACHE / CACHE READS

CACHE / NOCACHE / CACHE READS: LOB Values and Buffer Cache

LOGGING / NOLOGGING

LOBs Will Always Generate Undo for LOB Index Pages

When LOGGING is Set Oracle Will Generate Full Redo for LOB Data Pages

Choosing the Value of CHUNK

Set INITIAL and NEXT to Larger than CHUNK

ENABLE or DISABLE STORAGE IN ROW Clause

Guidelines for ENABLE or DISABLE STORAGE IN ROW

Indexing LOB Columns

Using Domain Indexing on LOB Columns

Indexing LOB Columns Using a Text Index

Function-Based Indexes on LOBs

Extensible Indexing on LOB Columns

Extensible Optimizer

Oracle Text Indexing Support for XML

Manipulating LOBs in Partitioned Tables

Partitioning a Table Containing LOB Columns

Creating an Index on a Table Containing Partitioned LOB Columns

Moving Partitions Containing LOBs

Splitting Partitions Containing LOBs

Merging Partitions Containing LOBs

LOBs in Index Organized Tables

Restrictions for LOBs in Partitioned Index-Organized Tables

Updating LOBs in Nested Tables

5 Advanced Design Considerations

LOB Buffering Subsystem

Advantages of LOB Buffering

Guidelines for Using LOB Buffering

LOB Buffering Subsystem Usage

LOB Buffer Physical Structure

LOB Buffering Subsystem Usage Scenario

Flushing the LOB Buffer

Flushing the Updated LOB

Using Buffer-Enabled Locators

Saving Locator State to Avoid a Reselect

OCI Example of LOB Buffering

Opening Persistent LOBs with the OPEN and CLOSE Interfaces

Index Performance Benefits of Explicitly Opening a LOB

Working with Explicitly Open LOB Instances

Read Consistent Locators

A Selected Locator Becomes a Read Consistent Locator

Example of Updating LOBs and Read-Consistency

Example of Updating LOBs Through Updated Locators

Example of Updating a LOB Using SQL DML and DBMS_LOB

Example of Using One Locator to Update the Same LOB Value

Example of Updating a LOB with a PL/SQL (DBMS_LOB) Bind Variable

LOB Locators and Transaction Boundaries

Reading and Writing to a LOB Using Locators

Selecting the Locator Outside of the Transaction Boundary

Selecting the Locator Within a Transaction Boundary

LOB Locators Cannot Span Transactions

Example of Locator Not Spanning a Transaction

LOBs in the Object Cache

Terabyte-Size LOB Support

Maximum Storage Limit for Terabyte-Size LOBs

Using Terabyte-Size LOBs with JDBC

Using Terabyte-Size LOBs with the DBMS_LOB Package

Using Terabyte-Size LOBs with OCI

Guidelines for Creating Gigabyte LOBs

Creating a Tablespace and Table to Store Gigabyte LOBs

6 Overview of Supplied LOB APIs

Programmatic Environments That Support LOBs

Comparing the LOB Interfaces

Using PL/SQL (DBMS_LOB Package) to Work with LOBs

Provide a LOB Locator Before Running the DBMS_LOB Routine

Guidelines for Offset and Amount Parameters in DBMS_LOB Operations

Determining Character Set ID

PL/SQL Functions and Procedures for LOBs

PL/SQL Functions and Procedures to Modify LOB Values

PL/SQL Functions and Procedures for Introspection of LOBs

PL/SQL Operations on Temporary LOBs

PL/SQL Read-Only Functions and Procedures for BFILEs

PL/SQL Functions and Procedures to Open and Close Internal and External LOBs

Using OCI to Work with LOBs

Setting the CSID Parameter for OCI LOB APIs

Fixed-Width and Varying-Width Character Set Rules for OCI

Other Operations

OCILobLoadFromFile2() Amount Parameter

OCILobRead2() Amount Parameter

OCILobLocator Pointer Assignment

LOB Locators in Defines and Out-Bind Variables in OCI

OCI Functions That Operate on BLOBs, CLOBs, NCLOBs, and BFILEs

OCI Functions to Modify Persistent LOB (BLOB, CLOB, and NCLOB) Values

OCI Functions to Read or Examine Persistent LOB and External LOB (BFILE) Values

OCI Functions for Temporary LOBs

OCI Read-Only Functions for BFILEs

OCI LOB Locator Functions

OCI LOB-Buffering Functions

OCI Functions to Open and Close Internal and External LOBs

OCI LOB Examples

Further Information About OCI

Using C++ (OCCI) to Work with LOBs

OCCI Classes for LOBs

Fixed-Width Character Set Rules

Varying-Width Character Set Rules

Offset and Amount Parameters for Other OCCI Operations

Amount Parameter for OCCI LOB copy() Methods

Amount Parameter for OCCI read() Operations

Further Information About OCCI

OCCI Methods That Operate on BLOBs, BLOBs, NCLOBs, and BFILEs

OCCI Methods to Modify Persistent LOB (BLOB, CLOB, and NCLOB) Values

OCCI Methods to Read or Examine Persistent LOB and BFILE Values

OCCI Read-Only Methods for BFILEs

Other OCCI LOB Methods

OCCI Methods to Open and Close Internal and External LOBs

Using C/C++ (Pro*C) to Work with LOBs

First Provide an Allocated Input Locator Pointer That Represents LOB

Pro*C/C++ Statements That Operate on BLOBs, CLOBs, NCLOBs, and BFILEs

Pro*C/C++ Embedded SQL Statements to Modify Persistent LOB Values

Pro*C/C++ Embedded SQL Statements for Introspection of LOBs

Pro*C/C++ Embedded SQL Statements for Temporary LOBs

Pro*C/C++ Embedded SQL Statements for BFILEs

Pro*C/C++ Embedded SQL Statements for LOB Locators

Pro*C/C++ Embedded SQL Statements for LOB Buffering

Pro*C/C++ Embedded SQL Statements to Open and Close LOBs

Using COBOL (Pro*COBOL) to Work with LOBs

First Provide an Allocated Input Locator Pointer That Represents LOB

Pro*COBOL Statements That Operate on BLOBs, CLOBs, NCLOBs, and BFILEs

Pro*COBOL Embedded SQL Statements to Modify Persistent LOB Values

Pro*COBOL Embedded SQL Statements for Introspection of LOBs

Pro*COBOL Embedded SQL Statements for Temporary LOBs

Pro*COBOL Embedded SQL Statements for BFILEs

Pro*COBOL Embedded SQL Statements for LOB Locators

Pro*COBOL Embedded SQL Statements for LOB Buffering

Pro*COBOL Embedded SQL Statements for Opening and Closing LOBs and BFILEs

Using Visual Basic (Oracle Objects for OLE) to Work with LOBs

OO4O Syntax Reference

OraBlob, OraClob, and OraBfile Object Interfaces Encapsulate Locators

OraBlob and OraClob Objects Are Retrieved as Part of Dynaset

Use the Clone Method to Retain Locator Independent of the Dynaset Move

Example of OraBlob and OraBfile

OO4O Methods and Properties to Access Data Stored in LOBs

OO4O Methods to Modify BLOB, CLOB, and NCLOB Values

OO4O Methods to Read or Examine Internal and External LOB Values

OO4O Methods to Open and Close External LOBs (BFILEs)

OO4O Methods for Persistent LOBBuffering

OO4O Properties for Operating on LOBs

OO4O Read-Only Methods for External Lobs (BFILEs)

OO4O Properties for Operating on External LOBs (BFILEs)

Using Java (JDBC) to Work with LOBs

Modifying Internal Persistent LOBs Using Java

Reading Internal Persistent LOBs and External LOBs (BFILEs) with Java

BLOB, CLOB, and BFILE Classes

Calling DBMS_LOB Package from Java (JDBC)

Referencing LOBs Using Java (JDBC)

Using OracleResultSet: BLOB and CLOB Objects Retrieved

JDBC Syntax References and Further Information

JDBC Methods for Operating on LOBs

JDBC oracle.sql.BLOB Methods to Modify BLOB Values

JDBC oracle.sql.BLOB Methods to Read or Examine BLOB Values

JDBC oracle.sql.BLOB Methods and Properties for BLOB Buffering

JDBC oracle.sql.CLOB Methods to Modify CLOB Values

JDBC oracle.sql.CLOB Methods to Read or Examine CLOB Value

JDBC oracle.sql.CLOB Methods and Properties for CLOB Buffering

JDBC oracle.sql.BFILE Methods to Read or Examine External LOB (BFILE) Values

JDBC oracle.sql.BFILE Methods and Properties for BFILE Buffering

JDBC Temporary LOB APIs

JDBC: Opening and Closing LOBs

JDBC: Opening and Closing BLOBs

Opening the BLOB Using JDBC

Checking If the BLOB Is Open Using JDBC

Closing the BLOB Using JDBC

JDBC: Opening and Closing CLOBs

Opening the CLOB Using JDBC

Checking If the CLOB Is Open Using JDBC

Closing the CLOB Using JDBC

JDBC: Opening and Closing BFILEs

Checking If the BFILE Is Open

Closing the BFILE

Usage Example (OpenCloseLob.java)

Truncating LOBs Using JDBC

JDBC: Truncating BLOBs

JDBC: Truncating CLOBs

JDBC BLOB Streaming APIs

JDBC CLOB Streaming APIs

BFILE Streaming APIs

JDBC BFILE Streaming Example (NewStreamLob.java)

JDBC and Empty LOBs

Oracle Provider for OLE DB (OraOLEDB)

Overview of Oracle Data Provider for .NET (ODP.NET)

7 Performance Guidelines

LOB Performance Guidelines

Performance Guidelines for Small LOBs

General Performance Guidelines

Temporary LOB Performance Guidelines

Performance Considerations for SQL Semantics and LOBs

Moving Data to LOBs in a Threaded Environment

LOB Access Statistics

Example of Retrieving LOB Access Statistics

Part III SQL Access to LOBs

8 DDL and DML Statements with LOBs

Creating a Table Containing One or More LOB Columns

Creating a Nested Table Containing a LOB

Inserting a Row by Selecting a LOB From Another Table

Inserting a LOB Value Into a Table

Inserting a Row by Initializing a LOB Locator Bind Variable

PL/SQL: Inserting a Row by Initializing a LOB Locator Bind Variable

C (OCI): Inserting a Row by Initializing a LOB Locator Bind Variable

COBOL (Pro*COBOL): Inserting a Row by Initializing a LOB Locator Bind Variable

C/C++ (Pro*C/C++): Inserting a Row by Initializing a LOB Locator Bind Variable

Visual Basic (OO4O): Inserting a Row by Initializing a LOB Locator Bind Variable

Java (JDBC): Inserting a Row by Initializing a LOB Locator Bind Variable

Updating a LOB with EMPTY_CLOB() or EMPTY_BLOB()

Updating a Row by Selecting a LOB From Another Table

9 SQL Semantics and LOBs

Using LOBs in SQL

SQL Functions and Operators Supported for Use with LOBs

UNICODE Support

Codepoint Semantics

Return Values for SQL Semantics on LOBs

LENGTH Return Value for LOBs

Implicit Conversion of LOB Datatypes in SQL

Implicit Conversion Between CLOB and NCLOB Datatypes in SQL

Unsupported Use of LOBs in SQL

VARCHAR2 and RAW Semantics for LOBs

LOBs Returned from SQL Functions

IS NULL and IS [NOT] NULL Usage with VARCHAR2s and CLOBs

WHERE Clause Usage with LOBs

10 PL/SQL Semantics for LOBs

PL/SQL Statements and Variables

Implicit Conversions Between CLOB and VARCHAR2

Explicit Conversion Functions

VARCHAR2 and CLOB in PL/SQL Built-In Functions

PL/SQL CLOB Comparison Rules

CLOBs Follow the VARCHAR2 Collating Sequence

11 Migrating Columns from LONGs to LOBs

Benefits of Migrating LONG Columns to LOB Columns

Preconditions for Migrating LONG Columns to LOB Columns

Dropping a Domain Index on a LONG Column Before Converting to a LOB

Preventing Generation of Redo Space on Tables Converted to LOB Datatypes

Using utldtree.sql to Determine Where Your Application Needs Change

Converting Tables from LONG to LOB Datatypes

Using ALTER TABLE to Convert LONG Columns to LOB Columns

Migration Issues

Copying a LONG to a LOB Column Using the TO_LOB Operator

Online Redefinition of Tables with LONG Columns

Migrating Applications from LONGs to LOBs

LOB Columns Are Not Allowed in Clustered Tables

LOB Columns Are Not Allowed in UPDATE OF Triggers

Indexes on Columns Converted from LONG to LOB Datatypes

Empty LOBs Compared to NULL and Zero Length LONGs

Overloading with Anchored Types

Some Implicit Conversions Are Not Supported for LOB Datatypes

Part IV Using LOB APIs

12 Operations Specific to Persistent and Temporary LOBs

Persistent LOB Operations

Inserting a LOB into a Table

Selecting a LOB from a Table

Temporary LOB Operations

Creating and Freeing a Temporary LOB

Creating Persistent and Temporary LOBs in PL/SQL

13 Data Interface for Persistent LOBs

Overview of the Data Interface for Persistent LOBs

Benefits of Using the Data Interface for Persistent LOBs

Using the Data Interface for Persistent LOBs in PL/SQL

Guidelines for Accessing LOB Columns Using the Data Interface in SQL and PL/SQL

Implicit Assignment and Parameter Passing

Passing CLOBs to SQL and PL/SQL Built-In Functions

Explicit Conversion Functions

Calling PL/SQL and C Procedures from SQL

Calling PL/SQL and C Procedures from PL/SQL

Binds of All Sizes in INSERT and UPDATE Operations

4000 Byte Limit on Results of a SQL Operator

Example of 4000 Byte Result Limit of a SQL Operator

Restrictions on Binds of More Than 4000 Bytes

Parallel DML Support for LOBs

Example: PL/SQL - Using Binds of More Than 4000 Bytes in INSERT and UPDATE

Using the Data Interface for LOBs with INSERT, UPDATE, and SELECT Operations

Using the Data Interface for LOBs in Assignments and Parameter Passing

Using the Data Interface for LOBs with PL/SQL Built-In Functions

Using the Data Interface for Persistent LOBs in OCI

Binding LOB Datatypes in OCI

Defining LOB Datatypes in OCI

Using Multibyte Character Sets in OCI with the Data Interface for LOBs

Using OCI Functions to Perform INSERT or UPDATE on LOB Columns

Simple INSERTs or UPDATEs in One Piece

Using Piecewise INSERTs and UPDATEs with Polling

Piecewise INSERTs and UPDATEs with Callback

Array INSERT and UPDATE Operations

Using the Data Interface to Fetch LOB Data in OCI

Simple Fetch in One Piece

Piecewise Fetch with Polling

Piecewise with Callback

PL/SQL and C Binds from OCI

Calling PL/SQL Out-binds in the "begin foo(:1); end;" Manner

Calling PL/SQL Out-binds in the "call foo(:1);" Manner

Example: C (OCI) - Binds of More than 4000 Bytes for INSERT and UPDATE

Using the Data Interface for LOBs in PL/SQL Binds from OCI on LOBs

Calling PL/SQL Out-binds in the "begin foo(:1); end;" Manner

Calling PL/SQL Out-binds in the "call foo(:1);" Manner

Binding LONG Data for LOB Columns in Binds Greater Than 4000 Bytes

Binding LONG Data to LOB Columns Using Piecewise INSERT with Polling

Binding LONG Data to LOB Columns Using Piecewise INSERT with Callback

Binding LONG Data to LOB Columns Using an Array INSERT

Selecting a LOB Column into a LONG Buffer Using a Simple Fetch

Selecting a LOB Column into a LONG Buffer Using Piecewise Fetch with Polling

Selecting a LOB Column into a LONG Buffer Using Piecewise Fetch with Callback

Selecting a LOB Column into a LONG Buffer Using an Array Fetch

Using the Data Interface for Persistent LOBs in Java

Using the Data Interface with Remote LOBs

Remote Data Interface Example in PL/SQL

Remote Data Interface Example in OCI

Remote Data Interface Examples in JDBC

14 LOB APIs for Basic Operations

Supported Environments

Appending One LOB to Another

Determining Character Set Form

Determining Character Set ID

Loading a LOB with Data from a BFILE

Loading a BLOB with Data from a BFILE

Loading a CLOB or NCLOB with Data from a BFILE

PL/SQL: Loading Character Data from a BFILE into a LOB

PL/SQL: Loading Segments of Character Data into Different LOBs

Determining Whether a LOB is Open

Java (JDBC): Checking If a LOB Is Open

Checking If a CLOB Is Open

Checking If a BLOB Is Open

Displaying LOB Data

Reading Data from a LOB

Reading a Portion of a LOB (SUBSTR)

Comparing All or Part of Two LOBs

Patterns: Checking for Patterns in a LOB Using INSTR

Length: Determining the Length of a LOB

Copying All or Part of One LOB to Another LOB

Copying a LOB Locator

Equality: Checking If One LOB Locator Is Equal to Another

Determining Whether LOB Locator Is Initialized

Appending to a LOB

Writing Data to a LOB

LOB Array Write

Trimming LOB Data

Erasing Part of a LOB

Enabling LOB Buffering

Flushing the Buffer

Disabling LOB Buffering

Determining Whether a LOB instance Is Temporary

Java (JDBC): Determining Whether a BLOB Is Temporary

Converting a BLOB to a CLOB

Converting a CLOB to a BLOB

15 LOB APIs for BFILE Operations

Supported Environments for BFILE APIs

Accessing BFILEs

Directory Objects

Initializing a BFILE Locator

How to Associate Operating System Files with a BFILE

BFILENAME and Initialization

Characteristics of the BFILE Datatype

DIRECTORY Name Specification

On Windows Platforms

Ownership and Privileges

Read Permission on a DIRECTORY Object

SQL DDL for BFILE Security

SQL DML for BFILE Security

Catalog Views on Directories

Guidelines for DIRECTORY Usage

BFILEs in Shared Server (Multithreaded Server) Mode

External LOB (BFILE) Locators

When Two Rows in a BFILE Table Refer to the Same File

BFILE Locator Variable

Guidelines for BFILEs

Loading a LOB with BFILE Data

Opening a BFILE with OPEN

Opening a BFILE with FILEOPEN

Determining Whether a BFILE Is Open Using ISOPEN

Determining Whether a BFILE Is Open with FILEISOPEN

Displaying BFILE Data

Reading Data from a BFILE

Reading a Portion of BFILE Data Using SUBSTR

Comparing All or Parts of Two BFILES

Checking If a Pattern Exists in a BFILE Using INSTR

Determining Whether a BFILE Exists

Getting the Length of a BFILE

Assigning a BFILE Locator

Getting Directory Object Name and File Name of a BFILE

Updating a BFILE by Initializing a BFILE Locator

Closing a BFILE with FILECLOSE

Closing a BFILE with CLOSE

Closing All Open BFILEs with FILECLOSEALL

Inserting a Row Containing a BFILE

A LOB Demonstration Files

PL/SQL LOB Demonstration Files

OCI LOB Demonstration Files

Visual Basic OO4O LOB Demonstration Files

Java LOB Demonstration Files

Glossary

Index