1/37

Contents

List of Examples

List of Figures

List of Tables

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

Changes in This Release for Oracle Database SecureFiles and Large Objects Developer's Guide

• Changes in Oracle Database 12c Release 1 (12.1)

Part I Getting Started

1 Introduction to Large Objects and SecureFiles

• What Are Large Objects?
• Why Use Large Objects?
  • Using LOBs for Semistructured Data
  • Using LOBs for Unstructured Data
• Why Not Use LONGs?
• Different Kinds of LOBs
  • Internal LOBs
  • External LOBs and the BFILE Data Type
• 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
  • LOBs Used in Oracle Multimedia
• BasicFiles and SecureFiles LOBs
• Database File System (DBFS)

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

4 Using Oracle LOB Storage

• About LOB Storage
  • BasicFiles LOB Storage
  • SecureFiles LOB Storage
    • About Advanced LOB Compression
    • About Advanced LOB Deduplication
    • About SecureFiles Encryption
• Using CREATE TABLE with LOB Storage
  • CREATE TABLE LOB Storage Parameters
  • CREATE TABLE and SecureFiles LOB Features
    • CREATE TABLE with Advanced LOB Compression
    • CREATE TABLE with Advanced LOB Deduplication
    • CREATE TABLE with SecureFiles Encryption
• Using ALTER TABLE with LOB Storage
  • ALTER TABLE LOB Storage Parameters
  • ALTER TABLE SecureFiles LOB Features
    • ALTER TABLE with Advanced LOB Compression
    • ALTER TABLE with Advanced LOB Deduplication
    • ALTER TABLE with SecureFiles Encryption
• Initialization, Compatibility, and Upgrading
  • Compatibility and Upgrading
  • Initialization Parameter for SecureFiles LOBs
• Migrating Columns from BasicFiles LOBs to SecureFiles LOBs
  • Preventing Generation of REDO Data 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 LOBs and DBFS
  • Using DBMS_LOB with SecureFiles LOBs and DBFS
    • DBMS_LOB Constants Used with SecureFiles LOBs and DBFS
    • DBMS_LOB Subprograms Used with SecureFiles LOBs and DBFS
  • DBMS_SPACE Package
    • DBMS_SPACE.SPACE_USAGE()

Part II Database File System (DBFS)

5 Introducing the Database File System

• Why a Database File System?
• What is Database File System (DBFS)?
  • DBFS Server
  • DBFS Client
• What Is a Content Store?

6 DBFS SecureFiles Store

• Setting Up a SecureFiles Store
  • Managing Permissions
  • Creating a SecureFiles File System Store
  • Initializing SecureFiles Store File Systems
  • Comparing SecureFiles LOBs to BasicFiles LOBs
• Using a DBFS SecureFiles Store File System
  • Working with DBFS Content API
  • Dropping SecureFiles Store File Systems
• About DBFS SecureFiles Store Package, DBMS_DBFS_SFS

7 DBFS Hierarchical Store

• About the Hierarchical Store Package, DBMS_DBFS_HS
• Setting up the Store
  • Managing a HS Store Wallet
  • Creating, Registering, and Mounting 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
  • Using Compression with the Hierarchical Store
  • Using Tape
  • Using Amazon S3
• 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
• The DBMS_DBFS_HS Package
  • Constants for DBMS_DBFS_HS Package
  • Methods for DBMS_DBFS_HS Package
• Views for DBFS Hierarchical Store
  • DBA Views
  • User Views

8 DBFS Content API

• Overview of DBFS Content API
• Stores and DBFS Content API
  • Getting Started with DBMS_DBFS_CONTENT Package
    • DBFS Content API Role
    • Path Name Constants and Types
    • Path Properties
    • Content IDs
    • Path Name Types
    • Store Features
    • Lock Types
    • Standard Properties
    • Optional Properties
    • User-Defined 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
    • Listing all Available Stores and Their Features
    • Listing all Available Mount Points
    • Looking 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 Access Checks
  • DBFS Content API Abstract Operations
  • DBFS Content API Path Normalization
  • DBFS Content API Statistics Support
  • DBFS Content API Tracing Support
  • Resource and Property Views

9 Creating Your Own DBFS Store

• Overview of DBFS Store Creation and Use
• DBFS Content Store Provider Interface (DBFS Content SPI)
• Creating a Custom Provider
  • Mechanics
    • Installation and Setup
    • TBFS Use
    • TBFS Internals
  • TBFS.SQL
  • TBL.SQL
  • spec.sql
  • body.sql
  • capi.sql

10 Using DBFS

• DBFS Installation
• Creating a DBFS File System
  • Privileges Required to Create a DBFS File System
  • Advantages of Non-Partitioned Versus Partitioned DBFS File Systems
  • Creating a Non-Partitioned File System
  • Creating a Partitioned File System
  • Dropping a File System
• Accessing a DBFS File System
  • DBFS Client Prerequisites
  • Using the DBFS Client Command-Line Interface
    • Creating Content Store Paths
    • Creating a Directory
    • Listing a Directory
    • Copying Files and Directories
    • Removing Files and Directories
  • DBFS Mounting Interface (Linux and Solaris Only)
    • Installing FUSE on Solaris 11 SRU7 and Later
    • Mounting the DBFS Store
    • Unmounting a File System
    • Mounting DBFS Through fstab Utility for Linux
    • Mounting DBFS Through the vfstab Utility for Solaris
    • Restrictions on Mounted File Systems
  • File System Security Model
    • Enabling Shared Root Access
    • Enabling DBFS Access Among Multiple Database Users
  • HTTP, WebDAV, and FTP Access to DBFS
    • Internet Access to DBFS Through XDB
    • Web Distributed Authoring and Versioning (WebDAV) Access
    • FTP Access to DBFS
    • HTTP Access to DBFS
• DBFS Administration
  • Using Oracle Wallet with DBFS Client
  • 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
  • Small File Performance of DBFS
  • Enabling Advanced SecureFiles LOB Features for DBFS
• Shrinking and Reorganizing DBFS Filesystems
  • Advantages of Online Filesystem Reorganization
  • Determining Availability of Online Filesystem Reorganization
  • Invoking Online Filesystem Reorganization

Part III Application Design with LOBs

11 LOB Storage with Applications

• 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 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
  • 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
  • CLOBs and NCLOBs Do Not Follow Session Collation Settings
  • 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 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 how to Optimize the Application
• 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 (PDML) 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
• Java LOB Demonstration Files

Glossary

Index