Skip Headers

Oracle® Database Application Developer's Guide - Large Objects
10g Release 1 (10.1)

Part Number B10796-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

Send Us Your Comments

Preface

Audience
Organization
Related Documents
Conventions
Documentation Accessibility

What's New in Large Objects?

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
Abstract 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
Initializing a LOB Column to Contain a Locator
Accessing LOBs
Accessing a LOB Using SQL
Accessing a LOB Using the Data Interface
Accessing a LOB Using the Locator Interface
LOB Restrictions
Restrictions on 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
Initializing LOBs
Initializing Persistent LOB Columns 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
In-line and Out-of-Line LOB Storage
Defining Tablespace and Storage Characteristics for Persistent LOBs
LOB Storage Characteristics for LOB Column or Attribute
TABLESPACE and LOB Index
PCTVERSION
RETENTION
CACHE / NOCACHE / CACHE READS
LOGGING / NOLOGGING
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
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
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
Updating LOBs and Read-Consistency
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
Interfaces Not Supporting LOBs Greater Than 4 Gigabytes
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
PL/SQL Functions and Procedures for LOBs
PL/SQL Functions/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/Procedures for BFILEs
PL/SQL Functions/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
OCILobLoadFromFile2() Amount Parameter
OCILobRead2() Amount Parameter
OCILobLocator Pointer Assignment
LOB Locators in Defines and Out-Bind Variables in OCI
OCI LOB Examples
Further Information About 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
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 (OO4O)) to Work with LOBs
OO4O Syntax Reference
OraBlob, OraClob, and OraBfile Object Interfaces Encapsulate Locators
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 LOB-Buffering
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
Changing Internal Persistent LOBs Using Java
Reading Internal Persistent LOBs and External LOBs (BFILEs) with Java
Calling DBMS_LOB Package from Java (JDBC)
Referencing LOBs Using Java (JDBC)
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
JDBC: Opening and Closing CLOBs
JDBC: Opening and Closing BFILEs
Trimming LOBs Using JDBC
JDBC BLOB Streaming APIs
JDBC CLOB Streaming APIs
New BFILE Streaming APIs
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 Size LOBs
General Performance Guidelines
Temporary LOB Performance Guidelines
Performance Considerations for SQL Semantics and LOBs
Moving Data to LOBs in a Threaded Environment

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 Table 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
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 Not Allowed in Clustered Tables
LOB Columns 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
4,000 Byte Limit on Results of SQL Operator
Restrictions on Binds of More Than 4,000 Bytes
Example: PL/SQL - Using Binds of More Than 4,000 Bytes in INSERT and UPDATE
4,000 Byte Result Limit for SQL Operators
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 Charactersets in OCI with the Data Interface for LOBs
Using OCI Functions to Perform INSERT or UPDATE on LOB Columns
Using the Data Interface to Fetch LOB Data in OCI
PL/SQL and C Binds from OCI
Example: C (OCI) - Binds of More than 4,000 Bytes for INSERT and UPDATE
Using the Data Interface for LOBs in PL/SQL Binds from OCI on LOBs
Binding LONG Data for LOB Columns in Binds Greater Than 4,000 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

14 LOB APIs for Basic Operations

Supported Environments
Appending One LOB to Another
PL/SQL DBMS_LOB Package: Appending One LOB to Another
C (OCI): Appending One LOB to Another
COBOL (Pro*COBOL): Appending One LOB to Another
C/C++ (Pro*C/C++): Appending One LOB to Another
Visual Basic (OO4O): Appending One LOB to Another
Java (JDBC): Appending One LOB to Another
Determining Character Set Form
C (OCI): Determining Character Set Form
Determining Character Set ID
C (OCI): Determining Character Set ID
Loading a LOB with Data from a BFILE
PL/SQL (DBMS_LOB): Loading a LOB with Data from a BFILE
C (OCI): Loading a LOB with Data from a BFILE
COBOL (Pro*COBOL): Loading a LOB with Data from a BFILE
Visual Basic (OO4O): Loading a LOB with Data from a BFILE
Java (JDBC): Loading a LOB with Data from a BFILE
Loading a BLOB with Data from a BFILE
PL/SQL: Loading a BLOB with BFILE Data
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
PL/SQL (DBMS_LOB Package): Checking If a LOB Is Open
C (OCI): Checking If a LOB Is Open
COBOL (Pro*COBOL): Checking If a LOB Is Open
C/C++ (Pro*C/C++): Checking If a LOB Is Open
Java (JDBC): Checking If a LOB Is Open
Displaying LOB Data
PL/SQL (DBMS_LOB Package): Displaying LOB Data
C (OCI): Displaying LOB Data
COBOL (Pro*COBOL): Displaying LOB Data
C/C++ (Pro*C/C++): Displaying LOB Data
Visual Basic (OO4O): Displaying LOB Data
Java (JDBC): Displaying LOB Data
Reading Data from a LOB
PL/SQL (DBMS_LOB Package): Reading Data from a LOB
C (OCI): Reading Data from a LOB
COBOL (Pro*COBOL): Reading Data from a LOB
C/C++ (Pro*C/C++): Reading Data from a LOB
Visual Basic (OO4O): Reading Data from a LOB
Java (JDBC): Reading Data from a LOB
Reading a Portion of a LOB (SUBSTR)
PL/SQL (DBMS_LOB Package): Reading a Portion of the LOB (substr)
COBOL (Pro*COBOL): Reading a Portion of the LOB (substr)
C/C++ (Pro*C/C++): Reading a Portion of the LOB (substr)
Visual Basic (OO4O): Reading a Portion of the LOB (substr)
Java (JDBC): Reading a Portion of the LOB (substr)
Comparing All or Part of Two LOBs
PL/SQL (DBMS_LOB Package): Comparing All or Part of Two LOBs
COBOL (Pro*COBOL): Comparing All or Part of Two LOBs
C/C++ (Pro*C/C++): Comparing All or Part of Two LOBs
Visual Basic (OO4O): Comparing All or Part of Two LOBs
Java (JDBC): Comparing All or Part of Two LOBs
Patterns: Checking for Patterns in a LOB Using INSTR
PL/SQL (DBMS_LOB Package): Checking for Patterns in a LOB (instr)
COBOL (Pro*COBOL): Checking for Patterns in a LOB (instr)
C/C++ (Pro*C/C++): Checking for Patterns in a LOB (instr)
Java (JDBC): Checking for Patterns in a LOB (instr)
Length: Determining the Length of a LOB
PL/SQL (DBMS_LOB Package): Determining the Length of a LOB
C (OCI): Determining the Length of a LOB
COBOL (Pro*COBOL): Determining the Length of a LOB
C/C++ (Pro*C/C++): Determining the Length of a LOB
Visual Basic (OO4O): Determining the Length of a LOB
Java (JDBC): Determining the Length of a LOB
Copying All or Part of One LOB to Another LOB
PL/SQL (DBMS_LOB Package): Copying All or Part of One LOB to Another LOB
C (OCI): Copying All or Part of One LOB to Another LOB
COBOL (Pro*COBOL): Copying All or Part of One LOB to Another LOB
C/C++ (Pro*C/C++): Copying All or Part of a LOB to Another LOB
Visual Basic (OO4O): Copying All or Part of One LOB to Another LOB
Java (JDBC): Copying All or Part of One LOB to Another LOB
Copying a LOB Locator
PL/SQL (DBMS_LOB Package): Copying a LOB Locator
C (OCI): Copying a LOB Locator
COBOL (Pro*COBOL): Copying a LOB Locator
C/C++ (Pro*C/C++): Copying a LOB Locator
Visual Basic (OO4O): Copying a LOB Locator
Java (JDBC): Copying a LOB Locator
Equality: Checking If One LOB Locator Is Equal to Another
C (OCI): Checking If One LOB Locator Is Equal to Another
C/C++ (Pro*C/C++): Checking If One LOB Locator Is Equal to Another
Java (JDBC): Checking If One LOB Locator Is Equal to Another
Determining Whether LOB Locator Is Initialized
C (OCI): Determining Whether a LOB Locator Is Initialized
C/C++ (Pro*C/C++): Determining Whether a LOB Locator Is Initialized
Appending to a LOB
PL/SQL (DBMS_LOB Package): Writing to the End of (Appending to) a LOB
C (OCI): Writing to the End of (Appending to) a LOB
COBOL (Pro*COBOL): Writing to the End of (Appending to) a LOB
C/C++ (Pro*C/C++): Writing to the End of (Appending to) a LOB
Java (JDBC): Writing to the End of (Append-Write to) a LOB
Writing Data to a LOB
PL/SQL (DBMS_LOB Package): Writing Data to a LOB
C (OCI): Writing Data to a LOB
COBOL (Pro*COBOL): Writing Data to a LOB
C/C++ (Pro*C/C++): Writing Data to a LOB
Visual Basic (OO4O):Writing Data to a LOB
Java (JDBC): Writing Data to a LOB
Trimming LOB Data
PL/SQL (DBMS_LOB Package): Trimming LOB Data
C (OCI): Trimming LOB Data
COBOL (Pro*COBOL): Trimming LOB Data
C/C++ (Pro*C/C++): Trimming LOB Data
Visual Basic (OO4O): Trimming LOB Data
Java (JDBC): Trimming LOB Data
Erasing Part of a LOB
PL/SQL (DBMS_LOB Package): Erasing Part of a LOB
C (OCI): Erasing Part of a LOB
COBOL (Pro*COBOL): Erasing Part of a LOB
C/C++ (Pro*C/C++): Erasing Part of a LOB
Visual Basic (OO4O): Erasing Part of a LOB
Java (JDBC): Erasing Part of a LOB
Enabling LOB Buffering
COBOL (Pro*COBOL): Enabling LOB Buffering
C/C++ (Pro*C/C++): Enabling LOB Buffering
Visual Basic (OO4O): Enabling LOB Buffering
Flushing the Buffer
COBOL (Pro*COBOL): Flushing the Buffer
C/C++ (Pro*C/C++): Flushing the Buffer
Disabling LOB Buffering
C (OCI): Disabling LOB Buffering
COBOL (Pro*COBOL): Disabling LOB Buffering
C/C++ (Pro*C/C++): Disabling LOB Buffering
Visual Basic (OO4O): Disabling LOB Buffering
Determining Whether a LOB instance Is Temporary
PL/SQL (DBMS_LOB Package): Determining Whether a LOB Is Temporary
C (OCI): Determining Whether a LOB Is Temporary
COBOL (Pro*COBOL): Determining Whether a LOB Is Temporary
C/C++ (Pro*C/C++): Determining Whether a LOB Is Temporary
Java (JDBC): Determining Whether a BLOB Is Temporary
Java (JDBC): Determining Whether a CLOB 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 Object
Initializing a BFILE Locator
How to Associate Operating System Files with Database Records
BFILENAME() and Initialization
Characteristics of the BFILE Datatype
DIRECTORY Name Specification
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
Loading a LOB with BFILE Data
PL/SQL (DBMS_LOB): Loading a LOB with BFILE Data
C (OCI): Loading a LOB with BFILE Data
COBOL (Pro*COBOL): Loading a LOB with BFILE Data
C/C++ (Pro*C/C++): Loading a LOB with BFILE Data
Visual Basic (OO4O): Loading a LOB with BFILE Data
Opening a BFILE with OPEN
PL/SQL (DBMS_LOB): Opening a BFILE with OPEN
C (OCI): Opening a BFILE with OPEN
COBOL (Pro*COBOL): Opening a BFILE with OPEN
C/C++ (Pro*C/C++): Opening a BFILE with OPEN
Visual Basic (OO4O) Opening a BFILE with OPEN
Java (JDBC): Opening a BFILE with OPEN
Opening a BFILE with FILEOPEN
PL/SQL (DBMS_LOB): Opening a BFILE with FILEOPEN
C (OCI): Opening a BFILE with FILEOPEN
Java (JDBC): Opening a BFILE with FILEOPEN
Determining Whether a BFILE Is Open Using ISOPEN
PL/SQL (DBMS_LOB): Determining Whether a BFILE Is Open with ISOPEN
C (OCI): Determining Whether a BFILE Is Open with ISOPEN
COBOL (Pro*COBOL): Determining Whether a BFILE Is Open with ISOPEN
C/C++ (Pro*C/C++): Determining Whether a BFILE Is Open with ISOPEN
Visual Basic (OO4O): Determining Whether a BFILE Is Open with ISOPEN
Java (JDBC): Determining Whether a BFILE Is Open with ISOPEN
Determining Whether a BFILE Is Open with FILEISOPEN
PL/SQL (DBMS_LOB): Determining Whether a BFILE Is Open with FILEISOPEN
C (OCI): Determining Whether a BFILE Is Open with FILEISOPEN
Java (JDBC): Determining Whether a BFILE Is Open with FILEISOPEN
Displaying BFILE Data
PL/SQL (DBMS_LOB): Displaying BFILE Data
C (OCI): Displaying BFILE Data
COBOL (Pro*COBOL): Displaying BFILE Data
C/C++ (Pro*C/C++): Displaying BFILE Data
Visual Basic (OO4O): Displaying BFILE Data
Java (JDBC): Displaying BFILE Data
Reading Data from a BFILE
PL/SQL (DBMS_LOB): Reading Data from a BFILE
C (OCI): Reading Data from a BFILE
COBOL (Pro*COBOL): Reading Data from a BFILE
C/C++ (Pro*C/C++): Reading Data from a BFILE
Visual Basic (OO4O): Reading Data from a BFILE
Java (JDBC): Reading Data from a BFILE
Reading a Portion of BFILE Data Using SUBSTR
PL/SQL (DBMS_LOB): Reading a Portion of BFILE Data Using SUBSTR
COBOL (Pro*COBOL): Reading a Portion of BFILE Data Using SUBSTR
C/C++ (Pro*C/C++): Reading a Portion of BFILE Data Using SUBSTR
Visual Basic (OO4O): Reading a Portion of BFILE Data Using SUBSTR
Java (JDBC): Reading a Portion of BFILE Data Using SUBSTR
Comparing All or Parts of Two BFILES
PL/SQL (DBMS_LOB): Comparing All or Parts of Two BFILES
COBOL (Pro*COBOL): Comparing All or Parts of Two BFILES
C/C++ (Pro*C/C++): Comparing All or Parts of Two BFILES
Visual Basic (OO4O): Comparing All or Parts of Two BFILES
Java (JDBC): Comparing All or Parts of Two BFILES
Checking If a Pattern Exists in a BFILE Using INSTR
PL/SQL (DBMS_LOB): Checking If a Pattern Exists in a BFILE Using INSTR
COBOL (Pro*COBOL): Checking If a Pattern Exists in a BFILE Using INSTR
C/C++ (Pro*C/C++): Checking If a Pattern Exists in a BFILE Using INSTR
Java (JDBC): Checking If a Pattern Exists in a BFILE Using INSTR
Determining Whether a BFILE Exists
PL/SQL (DBMS_LOB): Determining Whether a BFILE Exists
C (OCI): Determining Whether a BFILE Exists
COBOL (Pro*COBOL): Determining Whether a BFILE Exists
C/C++ (Pro*C/C++): Determining Whether a BFILE Exists
Visual Basic (OO4O): Determining Whether a BFILE Exists
Java (JDBC): Determining Whether a BFILE Exists
Getting the Length of a BFILE
PL/SQL (DBMS_LOB): Getting the Length of a BFILE
C (OCI): Getting the Length of a BFILE
COBOL (Pro*COBOL): Getting the Length of a BFILE
C/C++ (Pro*C/C++): Getting the Length of a BFILE
Visual Basic (OO4O): Getting the Length of a BFILE
Java (JDBC): Getting the Length of a BFILE
Assigning a BFILE Locator
PL/SQL: Assigning a BFILE Locator
C (OCI): Assigning a BFILE Locator
COBOL (Pro*COBOL): Assigning a BFILE Locator
C/C++ (Pro*C/C++): Assigning a BFILE Locator
Java (JDBC): Assigning a BFILE Locator
Getting Directory Object Name and Filename of a BFILE
PL/SQL (DBMS_LOB): Getting Directory Object Name and Filename
C (OCI): Getting Directory Object Name and Filename
COBOL (Pro*COBOL): Getting Directory Object Name and Filename
C/C++ (Pro*C/C++): Getting Directory Object Name and Filename
Visual Basic (OO4O): Getting Directory Object Name and Filename
Java (JDBC): Getting Directory Object Name and Filename
Updating a BFILE by Initializing a BFILE Locator
PL/SQL: Updating a BFILE by Initializing a BFILE Locator
C (OCI): Updating a BFILE by Initializing a BFILE Locator
COBOL (Pro*COBOL): Updating a BFILE by Initializing a BFILE Locator
C/C++ (Pro*C/C++): Updating a BFILE by Initializing a BFILE Locator
Visual Basic (OO4O): Updating a BFILE by Initializing a BFILE Locator
Java (JDBC): Updating a BFILE by Initializing a BFILE Locator
Closing a BFILE with FILECLOSE
PL/SQL (DBMS_LOB): Closing a BFILE with FILECLOSE
C (OCI): Closing a BFILE with FILECLOSE
Java (JDBC): Closing a BFile with FILECLOSE
Closing a BFILE with CLOSE
PL/SQL (DBMS_LOB): Closing a BFILE with CLOSE
C (OCI): Closing a BFile with CLOSE
COBOL (Pro*COBOL): Closing a BFILE with CLOSE
C/C++ (Pro*C/C++): Closing a BFile with CLOSE
Visual Basic (OO4O): Closing a BFile with CLOSE
Java (JDBC): Closing a BFile with CLOSE
Closing All Open BFILEs with FILECLOSEALL
PL/SQL (DBMS_LOB): Closing All Open BFiles
C (OCI): Closing All Open BFiles
COBOL (Pro*COBOL): Closing All Open BFiles
C/C++ (Pro*C/C++): Closing All Open BFiles
Visual Basic (OO4O): Closing All Open BFiles
Java (JDBC): Closing All Open BFiles
Inserting a Row Containing a BFILE
PL/SQL (DBMS_LOB): Inserting a Row Containing a BFILE
C (OCI): Inserting a Row Containing a BFILE
COBOL (Pro*COBOL): Inserting a Row Containing a BFILE
C/C++ (Pro*C/C++): Inserting a Row Containing a BFILE
Visual Basic (OO4O): Inserting a Row Containing a BFILE
Java (JDBC): Inserting a Row Containing a BFILE

A LOB Demonstration Files

PL/SQL LOB Demonstration Files
OCI LOB Demonstration Files
Pro*COBOL LOB Demonstration Files
Pro*C LOB Demonstration Files
Visual Basic OO4O LOB Demonstration Files
Java LOB Demonstration Files

Glossary

Index