Contents

List of Examples

List of Figures

List of Tables

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

What's New in Oracle Database SecureFiles and Large Objects Developer's Guide?

• LOB Features Introduced in Oracle Database 11g Release 2
• LOB Features Introduced in Oracle Database 11g Release 1

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 Data Type
• Introducing LOB Locators
• Database Semantics for Internal and External LOBs
• Large Object Data Types
• Object Data Types and LOBs
• Storing and Creating Other Data Types with LOBs
  • VARRAYs Stored as LOBs
  • XMLType Columns Stored as CLOBs
  • LOBs Used in Oracle Multimedia

Part I Getting Started

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 Data Pump 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 SecureFiles LOBs

4 Using Oracle SecureFiles LOBs

• About SecureFiles LOBs
  • About Compression
  • About Deduplication
  • About Encryption
• Using CREATE TABLE with SecureFiles LOBs
  • Parameters of CREATE TABLE for SecureFiles LOB
    • BASICFILE
    • SECUREFILE
    • CHUNK
    • RETENTION
    • MAXSIZE
    • FREEPOOLS
    • LOGGING, NOLOGGING, or FILESYSTEM_LIKE_LOGGING
    • FREELISTS or FREELIST GROUPS
    • PCTVERSION
    • COMPRESS or NOCOMPRESS
    • DEDUPLICATE or KEEP_DUPLICATES
    • ENCRYPT or DECRYPT
  • CREATE TABLE Compression
    • Usage Notes for CREATE TABLE Compression
    • Examples of CREATE TABLE Compression
  • CREATE TABLE Deduplication
    • Usage Notes for CREATE TABLE Deduplication
    • Examples of CREATE TABLE Deduplication
  • CREATE TABLE Encryption
    • Usage Notes for CREATE TABLE Encryption
    • Examples of CREATE TABLE Encryption
• Using ALTER TABLE with SecureFiles LOBs
  • Parameters of CREATE TABLE for SecureFiles LOB
    • RETENTION
    • COMPRESS or NOCOMPRESS
    • DEDUPLICATE or KEEP_DUPLICATES
    • ENCRYPT or DECRYPT
  • ALTER TABLE Compression
    • Usage Notes for ALTER TABLE Compression
    • Examples of ALTER TABLE Compression
  • ALTER TABLE Deduplication
    • Usage Notes for ALTER TABLE Deduplication
    • Examples of ALTER TABLE Deduplication
  • ALTER TABLE Encryption
    • Usage Notes for ALTER TABLE Encryption
    • Examples of ALTER TABLE Encryption
• Database File System Links
  • Overview of Database File System Links
  • Creating Database File System Links
  • Copying Database File System Links
  • Copying a Linked LOB Between Tables
  • Online Redefinition and DBFS Links
  • Transparent Read
• Initialization Parameter db_securefile for SecureFiles LOBs
• Compatibility and Upgrading
• Migrating Columns from BasicFiles LOBs to SecureFiles LOBs
  • Preventing Generation of REDO Space when Migrating to SecureFiles LOBs
  • Online Redefinition for BasicFiles LOBs
    • Online Redefinition Advantages
    • Online Redefinition Disadvantages
  • Using Online Redefinition for Migrating Tables with BasicFiles LOBs
  • Parallel Online Redefinition
• PL/SQL Packages for SecureFiles LOBs and DBFS
  • DBMS_LOB Package
    • GETOPTIONS()
    • SETOPTIONS()
    • ISSECUREFILE()
    • MOVE_TO_DBFS_LINK()
    • COPY_FROM_DBFS_LINK()
    • COPY_DBFS_LINK()
    • GET_DBFS_LINK()
    • SET_DBFS_LINK()
    • GET_DBFS_LINK_STATE()
    • DBFS_LINK_GENERATE_PATHNAME()
    • SETCONTENTTYPE()
    • GETCONTENTTYPE()
    • APPEND()
    • COMPARE()
    • CONVERTTOBLOB()
    • CONVERTTOCLOB()
    • COPY()
    • ERASE()
    • FRAGMENT_DELETE()
    • FRAGMENT_INSERT()
    • FRAGMENT_MOVE()
    • FRAGMENT_REPLACE()
    • LOADBLOBFROMFILE()
    • LOADCLOBFROMFILE()
    • LOADFROMFILE()
    • READ()
    • SUBSTR()
    • TRIM()
    • WRITE()
    • WRITEAPPEND()
  • DBMS_SPACE Package
    • SPACE_USAGE()

5 Introducing the Oracle Database File System

• Why a Database File System?
• What is the Oracle Database File System (DBFS)?

6 DBFS File System Client

• Installing DBFS
  • DBFS Prerequisites
  • Installing FUSE (Linux Only)
  • DBFS Installation Home
  • Creating a File System
  • Dropping a File System
• DBFS Mounting Interface
  • Mounting the DBFS Store
  • Unmounting a File System in Linux
  • Restrictions on Mounted File Systems
  • Mounting DBFS Through fstab Utility in Linux
  • Oracle Database Release 11.2.0.3 with Solaris 11 SRU7
    • Installing FUSE on Solaris 11 SRU7 and Later
    • Unmounting DBFS
    • Mounting the DBFS Store: Solaris-Specific Privileges
    • Mounting DBFS Through the vfstab Utility for Solaris
• Using the DBFS Command Interface
  • Using DBFS
    • Creating a Directory
    • Listing a Directory
    • Copying Files and Directories
    • Removing Files and Directories
• DBFS Administration
  • Using Oracle Wallet with DBFS Client
  • File System Security Model
    • Enabling Shared Root Access
    • Enabling DBFS Access Among Multiple RDBMS Users
  • Performing DBFS Diagnostics
  • Managing DBFS Client Failover
  • Sharing and Caching DBFS
  • Backing up DBFS
    • Backing up DBFS at the Database Level
    • Backing up DBFS through a File System Utility
  • Improving Small File Performance of DBFS
  • Enabling Advanced SecureFiles LOB Features for DBFS

7 DBFS Content API

• Overview of DBFS Content API
• Stores and Package DBMS_DBFS_CONTENT
  • Getting Started with DBMS_DBFS_CONTENT Package
    • DBFS Content API Role
    • Path Name Constants and Types
    • Content Properties
    • Path Name Types
    • Store Features
    • Lock Types
    • Standard Properties
    • Optional Properties
    • Property Access Flags
    • Exceptions
    • Property Bundles
    • Store Descriptors
  • Administrative and Query APIs
  • Registering a Content Store
  • Unregistering A Content Store
  • Mounting a Registered Store
  • Unmounting a Previously Mounted Store
  • List all Available Stores and Their Features
  • List all Available Mount Points
  • Look-up Specific Stores and Their Features
  • DBFS Content API Space Usage
  • DBFS Content API Session Defaults
  • DBFS Content API Interface Versioning
  • DBFS Content API Notes on Path Names
  • DBFS Content API Creation Operations
  • DBFS Content API Deletion Operations
  • DBFS Content API Path Get and Put Operations
  • DBFS Content API Rename and Move Operations
  • Directory Listings
  • DBFS Content API Directory Navigation and Search
  • DBFS Content API Locking Operations
  • DBFS Content API Abstract Operations
  • DBFS Content API Access Checks
  • DBFS Content API Path Normalization
  • DBFS Content API Statistics Support
  • DBFS Content API Tracing Support
  • Resource and Property Views

8 DBFS SecureFiles Store

• The DBFS SecureFiles Store Package, DBMS_DBFS_SFS
  • Creating and Registering a New SecureFiles Store
  • Initializing or Re-initializing a SecureFiles Store
  • Unregister and Drop A SecureFiles Store File System Store
• Using a DBFS SecureFiles Store File System
  • Permissions Management
  • Creating a SecureFiles File System Store
  • Comparing SecureFiles LOBs to BasicFiles LOBs
  • Initializing SecureFiles Store File Systems
  • Drop SecureFiles Store File Systems
  • Working with DBFS Content API

9 DBFS Hierarchical Store

• Wallet Management
• Managing Storage with DBMS_DBFS_HS
  • Constants for DBMS_DBFS_HS Package
• Methods of DBMS_DBFS_HS Package
  • CREATESTORE()
  • DROPSTORE()
  • RECONFIGCACHE()
  • SETSTOREPROPERTY()
  • GETSTOREPROPERTY()
  • CREATEBUCKET()
  • STOREPUSH()
  • CLEANUPUNUSEDBACKUPFILES()
  • REGISTERSTORECOMMAND()
  • DEREGSTORECOMMAND()
  • SENDCOMMAND()
• User View for DBFS Hierarchical Store
  • USER_DBFS_HS_FILES
• Examples Using DBMS_DBFS_HS
  • Setting up the Store
  • Using the Hierarchical Store
    • Using Hierarchical Store as a File System
    • Using Hierarchical Store as an Archive Solution For SecureFiles LOBs
    • Dropping a Hierarchical Store
  • Example: Using Amazon S3
  • Example: Using Tape

10 Creating a DBFS Store

• Package DBMS_DBFS_CONTENT_SPI

Part III Application Design

11 LOB Storage

• 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 Data Type
  • LOBs Compared to LONG and LONG RAW Types
  • Storing Varying-Width Character Data in LOBs
  • Implicit Character Set Conversions with LOBs
• LOB Storage Parameters
  • 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 Parameter for BasicFiles LOBs
  • RETENTION Parameter for SecureFiles LOBs
  • CACHE / NOCACHE / CACHE READS
    • CACHE / NOCACHE / CACHE READS: LOB Values and Buffer Cache
  • LOGGING / NOLOGGING Parameter for BasicFiles LOBs
    • LOBs Always Generate Undo for LOB Index Pages
    • When LOGGING is Set Oracle Generates Full Redo for LOB Data Pages
  • LOGGING/FILESYSTEM_LIKE_LOGGING for SecureFiles LOBs
    • CACHE Implies LOGGING
    • SecureFiles and an Efficient Method of Generating REDO and UNDO
    • FILESYSTEM_LIKE_LOGGING is Useful for Bulk Loads or Inserts
  • 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

12 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

13 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
  • Prefetching of LOB Data, Length, and Chunk Size
  • 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 COM (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 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
  • 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)
  • LOB Prefetching to Improve Performance
  • Zero-Copy Input/Output for SecureFiles to Improve Performance
    • Zero-Copy Input/Output on the Server
    • Zero-Copy Input/Output in the JDBC Thin Driver
    • JDBC-OCI Driver Considerations
  • 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)

14 Performance Guidelines

• LOB Performance Guidelines
  • Chunk Size
  • Performance Guidelines for Small BasicFiles LOBs
  • General Performance Guidelines for BasicFiles LOBs
  • 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 IV SQL Access to LOBs

15 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
  • COM (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

16 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 Data Types in SQL
  • Implicit Conversion Between CLOB and NCLOB Data Types 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
• Built-in Functions for Remote LOBs and BFILEs

17 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
• PL/SQL Functions for Remote LOBs and BFILEs
  • Restrictions on Remote User-Defined Functions
  • Remote Functions in PL/SQL, OCI, and JDBC

18 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 Data Types
• Using utldtree.sql to Determine Where Your Application Needs Change
• Converting Tables from LONG to LOB Data Types
  • 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
  • Using Oracle Data Pump to Migrate a Database
• Migrating Applications from LONGs to LOBs
  • LOB Columns Are Not Allowed in Clustered Tables
  • LOB Columns Are Not Allowed in AFTER UPDATE OF Triggers
  • Indexes on Columns Converted from LONG to LOB Data Types
  • Empty LOBs Compared to NULL and Zero Length LONGs
  • Overloading with Anchored Types
  • Some Implicit Conversions Are Not Supported for LOB Data Types

Part V Using LOB APIs

19 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
• Freeing Temporary LOBs in OCI

20 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
  • Non-Supported Syntax
  • Remote Data Interface Example in PL/SQL
  • Remote Data Interface Example in OCI
  • Remote Data Interface Examples in JDBC

21 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 Data Type
  • 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

22 Using LOB APIs

• 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
• Ensuring Read Consistency

A LOB Demonstration Files

• PL/SQL LOB Demonstration Files
• OCI LOB Demonstration Files
• COM OO4O LOB Demonstration Files
• Java LOB Demonstration Files

Glossary

Index