Skip Headers
Oracle® Database Application Developer's Guide - Large Objects
10g Release 2 (10.2)

Part Number B14249-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to next page
Next
View PDF

Contents

Title and Copyright Information

Preface

Audience
Documentation Accessibility
Related Documents
Conventions

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
Internal 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 LOBs
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
LOB Storage
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
PCTVERSION
RETENTION
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
CHUNK
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
NCLOBs in OCI
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
Clob Class
Blob Class
Bfile Class
Fixed-Width Character Set Rules
Varying-Width Character Set Rules
Offset and Amount Parameters for Other OCCI Operations
NCLOBs in OCCI
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
Opening 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
Chunk Size
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
Array Fetch
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
LOB Array Read
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
BFILE Security
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