Table of Contents
- Title and Copyright Information
- Preface
- Changes in Oracle Database
-
Part I Getting Started
-
1
Introduction to Large Objects and SecureFiles
- 1.1 What Are Large Objects?
- 1.2 Why Use Large Objects?
- 1.3 Why Not Use LONGs?
- 1.4 Different Kinds of LOBs
- 1.5 LOB Locators
- 1.6 Database Semantics for Internal and External LOBs
- 1.7 Large Object Data Types
- 1.8 About Object Data Types and LOBs
- 1.9 Storage and Creation of Other Data Types with LOBs
- 1.10 BasicFiles and SecureFiles LOBs
- 1.11 Database File System (DBFS)
- 2 Working with LOBs
-
3
Using Oracle LOB Storage
- 3.1 LOB Storage
- 3.2 CREATE TABLE with LOB Storage
-
3.3
ALTER TABLE with LOB Storage
- 3.3.1 About ALTER TABLE and LOB Storage
- 3.3.2 BNF for the ALTER TABLE Statement
- 3.3.3 ALTER TABLE LOB Storage Parameters
- 3.3.4 ALTER TABLE SecureFiles LOB Features
- 3.4 Initialization, Compatibility, and Upgrading
- 3.5 Migrating Columns from BasicFiles LOBs to SecureFiles LOBs
- 3.6 PL/SQL Packages for LOBs and DBFS
- 4 Operations Specific to Persistent and Temporary LOBs
- 5 Distributed LOBs
-
6
DDL and DML Statements with LOBs
- 6.1 Creating a Table Containing One or More LOB Columns
- 6.2 Creating a Nested Table Containing a LOB
- 6.3 Inserting a Row by Selecting a LOB From Another Table
- 6.4 Inserting a LOB Value Into a Table
-
6.5
Inserting a Row by Initializing a LOB Locator Bind Variable
- 6.5.1 About Inserting Rows with LOB Locator Bind Variables
- 6.5.2 PL/SQL: Inserting a Row by Initializing a LOB Locator Bind Variable
- 6.5.3 C (OCI): Inserting a Row by Initializing a LOB Locator Bind Variable
- 6.5.4 COBOL (Pro*COBOL): Inserting a Row by Initializing a LOB Locator Bind Variable
- 6.5.5 C/C++ (Pro*C/C++): Inserting a Row by Initializing a LOB Locator Bind Variable
- 6.5.6 Java (JDBC): Inserting a Row by Initializing a LOB Locator Bind Variable
- 6.6 Updating a LOB with EMPTY_CLOB() or EMPTY_BLOB()
- 6.7 Updating a Row by Selecting a LOB From Another Table
-
1
Introduction to Large Objects and SecureFiles
-
Part II Value Semantics LOBs
- 7 SQL Semantics and LOBs
- 8 PL/SQL Semantics for LOBs
-
9
Data Interface for Persistent LOBs
- 9.1 Overview of the Data Interface for Persistent LOBs
- 9.2 Benefits of Using the Data Interface for Persistent LOBs
-
9.3
Using the Data Interface for Persistent LOBs in PL/SQL
- 9.3.1 About Using the Data Interface for Persistent LOBs in PL/SQL
- 9.3.2 Guidelines for Accessing LOB Columns Using the Data Interface in SQL and PL/SQL
- 9.3.3 Implicit Assignment and Parameter Passing
- 9.3.4 Passing CLOBs to SQL and PL/SQL Built-In Functions
- 9.3.5 Explicit Conversion Functions
- 9.3.6 Calling PL/SQL and C Procedures from SQL
- 9.3.7 Calling PL/SQL and C Procedures from PL/SQL
- 9.3.8 Binds of All Sizes in INSERT and UPDATE Operations
- 9.3.9 4000 Byte Limit on Results of a SQL Operator
- 9.3.10 Example of 4000 Byte Result Limit of a SQL Operator
- 9.3.11 Restrictions on Binds of More Than 4000 Bytes
- 9.3.12 Performing Parallel DDL, Parallel DML (PDML), and Parallel Query (PQ) Operations on LOBs
- 9.3.13 Example: PL/SQL - Using Binds of More Than 4000 Bytes in INSERT and UPDATE
- 9.3.14 Using the Data Interface for LOBs with INSERT, UPDATE, and SELECT Operations
- 9.3.15 Using the Data Interface for LOBs in Assignments and Parameter Passing
- 9.3.16 Using the Data Interface for LOBs with PL/SQL Built-In Functions
-
9.4
The Data Interface Used for Persistent LOBs in OCI
- 9.4.1 LOB Data Types Bound in OCI
- 9.4.2 LOB Data Types Defined in OCI
- 9.4.3 Multibyte Character Sets Used in OCI with the Data Interface for LOBs
- 9.4.4 OCI Functions Used to Perform INSERT or UPDATE on LOB Columns
- 9.4.5 The Data Interface Used to Fetch LOB Data in OCI
- 9.4.6 PL/SQL and C Binds from OCI
- 9.4.7 Example: C (OCI) - Binds of More than 4000 Bytes for INSERT and UPDATE
- 9.4.8 Using the Data Interface for LOBs in PL/SQL Binds from OCI on LOBs
- 9.4.9 Binding LONG Data for LOB Columns in Binds Greater Than 4000 Bytes
- 9.4.10 Binding LONG Data to LOB Columns Using Piecewise INSERT with Polling
- 9.4.11 Binding LONG Data to LOB Columns Using Piecewise INSERT with Callback
- 9.4.12 Binding LONG Data to LOB Columns Using an Array INSERT
- 9.4.13 Selecting a LOB Column into a LONG Buffer Using a Simple Fetch
- 9.4.14 Selecting a LOB Column into a LONG Buffer Using Piecewise Fetch with Polling
- 9.4.15 Selecting a LOB Column into a LONG Buffer Using Piecewise Fetch with Callback
- 9.4.16 Selecting a LOB Column into a LONG Buffer Using an Array Fetch
- 9.5 The Data Interface Used with Persistent LOBs in Java
- 9.6 The Data Interface Used with Remote LOBs
-
Part III Reference Semantics LOBs
-
10
Overview of Supplied LOB APIs
- 10.1 Programmatic Environments That Support LOBs
- 10.2 Comparing the LOB Interfaces
-
10.3
Using PL/SQL (DBMS_LOB Package) to Work With LOBs
- 10.3.1 Provide a LOB Locator Before Running the DBMS_LOB Routine
- 10.3.2 Guidelines for Offset and Amount Parameters in DBMS_LOB Operations
- 10.3.3 Determining Character Set ID
- 10.3.4 PL/SQL Functions and Procedures for LOBs
- 10.3.5 PL/SQL Functions and Procedures to Modify LOB Values
- 10.3.6 PL/SQL Functions and Procedures for Introspection of LOBs
- 10.3.7 PL/SQL Operations on Temporary LOBs
- 10.3.8 PL/SQL Read-Only Functions and Procedures for BFILEs
- 10.3.9 PL/SQL Functions and Procedures to Open and Close Internal and External LOBs
-
10.4
Using OCI to Work With LOBs
- 10.4.1 Prefetching of LOB Data, Length, and Chunk Size
- 10.4.2 Setting the CSID Parameter for OCI LOB APIs
- 10.4.3 Fixed-Width and Varying-Width Character Set Rules for OCI
- 10.4.4 OCILobLoadFromFile2() Amount Parameter
- 10.4.5 OCILobRead2() Amount Parameter
- 10.4.6 OCILobLocator Pointer Assignment
- 10.4.7 LOB Locators in Defines and Out-Bind Variables in OCI
- 10.4.8 OCI Functions That Operate on BLOBs, CLOBs, NCLOBs, and BFILEs
- 10.4.9 OCI Functions to Modify Persistent LOB (BLOB, CLOB, and NCLOB) Values
- 10.4.10 OCI Functions to Read or Examine Persistent LOB and External LOB (BFILE) Values
- 10.4.11 OCI Functions for Temporary LOBs
- 10.4.12 OCI Read-Only Functions for BFILEs
- 10.4.13 OCI LOB Locator Functions
- 10.4.14 OCI Functions to Open and Close Internal and External LOBs
- 10.4.15 OCI LOB Examples
- 10.4.16 Further Information About OCI
-
10.5
Using C++ (OCCI) to Work With LOBs
- 10.5.1 OCCI Classes for LOBs
- 10.5.2 Fixed-Width Character Set Rules
- 10.5.3 Varying-Width Character Set Rules
- 10.5.4 Offset and Amount Parameters for Other OCCI Operations
- 10.5.5 Amount Parameter for OCCI LOB copy() Methods
- 10.5.6 Amount Parameter for OCCI read() Operations
- 10.5.7 Further Information About OCCI
- 10.5.8 OCCI Methods That Operate on BLOBs, BLOBs, NCLOBs, and BFILEs
- 10.5.9 OCCI Methods to Modify Persistent LOB (BLOB, CLOB, and NCLOB) Values
- 10.5.10 OCCI Methods to Read or Examine Persistent LOB and BFILE Values
- 10.5.11 OCCI Read-Only Methods for BFILEs
- 10.5.12 Other OCCI LOB Methods
- 10.5.13 OCCI Methods to Open and Close Internal and External LOBs
-
10.6
Using C/C++ (Pro*C) to Work With LOBs
- 10.6.1 Providing an Allocated Input Locator Pointer That Represents LOB
- 10.6.2 Pro*C/C++ Statements That Operate on BLOBs, CLOBs, NCLOBs, and BFILEs
- 10.6.3 Pro*C/C++ Embedded SQL Statements to Modify Persistent LOB Values
- 10.6.4 Pro*C/C++ Embedded SQL Statements for Introspection of LOBs
- 10.6.5 Pro*C/C++ Embedded SQL Statements for Temporary LOBs
- 10.6.6 Pro*C/C++ Embedded SQL Statements for BFILEs
- 10.6.7 Pro*C/C++ Embedded SQL Statements for LOB Locators
- 10.6.8 Pro*C/C++ Embedded SQL Statements to Open and Close LOBs
-
10.7
Using COBOL (Pro*COBOL) to Work With LOBs
- 10.7.1 Providing an Allocated Input Locator Pointer That Represents LOB
- 10.7.2 Pro*COBOL Statements That Operate on BLOBs, CLOBs, NCLOBs, and BFILEs
- 10.7.3 Pro*COBOL Embedded SQL Statements to Modify Persistent LOB Values
- 10.7.4 Pro*COBOL Embedded SQL Statements for Introspection of LOBs
- 10.7.5 Pro*COBOL Embedded SQL Statements for Temporary LOBs
- 10.7.6 Pro*COBOL Embedded SQL Statements for BFILEs
- 10.7.7 Pro*COBOL Embedded SQL Statements for LOB Locators
- 10.7.8 Pro*COBOL Embedded SQL Statements for Opening and Closing LOBs and BFILEs
-
10.8
Using Java (JDBC) to Work With LOBs
- 10.8.1 Modifying Internal Persistent LOBs Using Java
- 10.8.2 Reading Internal Persistent LOBs and External LOBs (BFILEs) With Java
- 10.8.3 Calling DBMS_LOB Package from Java (JDBC)
- 10.8.4 Prefetching LOBs to Improve Performance
- 10.8.5 Zero-Copy Input/Output for SecureFiles to Improve Performance
- 10.8.6 Referencing LOBs Using Java (JDBC)
- 10.8.7 JDBC Syntax References and Further Information
- 10.8.8 JDBC Methods for Operating on LOBs
- 10.8.9 JDBC oracle.sql.BLOB Methods to Modify BLOB Values
- 10.8.10 JDBC oracle.sql.BLOB Methods to Read or Examine BLOB Values
- 10.8.11 JDBC oracle.sql.BLOB Methods and Properties for Streaming BLOB Data
- 10.8.12 JDBC oracle.sql.CLOB Methods to Modify CLOB Values
- 10.8.13 JDBC oracle.sql.CLOB Methods to Read or Examine CLOB Value
- 10.8.14 JDBC oracle.sql.CLOB Methods and Properties for Streaming CLOB Data
- 10.8.15 JDBC oracle.sql.BFILE Methods to Read or Examine External LOB (BFILE) Values
- 10.8.16 JDBC oracle.sql.BFILE Methods and Properties for Streaming BFILE Data
- 10.8.17 JDBC Temporary LOB APIs
- 10.8.18 JDBC: Opening and Closing LOBs
- 10.8.19 JDBC: Opening and Closing BLOBs
- 10.8.20 JDBC: Opening and Closing CLOBs
- 10.8.21 JDBC: Opening and Closing BFILEs
- 10.8.22 Truncating LOBs Using JDBC
- 10.8.23 JDBC BLOB Streaming APIs
- 10.8.24 JDBC CLOB Streaming APIs
- 10.8.25 BFILE Streaming APIs
- 10.8.26 JDBC and Empty LOBs
- 10.9 Oracle Provider for OLE DB (OraOLEDB)
- 10.10 Overview of Oracle Data Provider for .NET (ODP.NET)
-
11
LOB APIs for BFILE Operations
- 11.1 Supported Environments for BFILE APIs
- 11.2 About Accessing BFILEs
- 11.3 Directory Objects
- 11.4 BFILENAME and Initialization
- 11.5 Characteristics of the BFILE Data Type
-
11.6
BFILE Security
- 11.6.1 Ownership and Privileges
- 11.6.2 Read Permission on a DIRECTORY Object
- 11.6.3 SQL DDL for BFILE Security
- 11.6.4 SQL DML for BFILE Security
- 11.6.5 Catalog Views on Directories
- 11.6.6 Guidelines for DIRECTORY Usage
- 11.6.7 BFILEs in Shared Server (Multithreaded Server) Mode
- 11.6.8 External LOB (BFILE) Locators
- 11.7 About Loading a LOB with BFILE Data
- 11.8 About Opening a BFILE with OPEN
- 11.9 About Opening a BFILE with FILEOPEN
- 11.10 About Determining Whether a BFILE Is Open Using ISOPEN
- 11.11 About Determining Whether a BFILE Is Open with FILEISOPEN
- 11.12 About Displaying BFILE Data
- 11.13 About Reading Data from a BFILE
- 11.14 About Reading a Portion of BFILE Data Using SUBSTR
- 11.15 Comparing All or Parts of Two BFILES
- 11.16 Checking If a Pattern Exists in a BFILE Using INSTR
- 11.17 Determining Whether a BFILE Exists
- 11.18 Getting the Length of a BFILE
- 11.19 About Assigning a BFILE Locator
- 11.20 Getting Directory Object Name and File Name of a BFILE
- 11.21 About Updating a BFILE by Initializing a BFILE Locator
- 11.22 Closing a BFILE with FILECLOSE
- 11.23 Closing a BFILE with CLOSE
- 11.24 Closing All Open BFILEs with FILECLOSEALL
- 11.25 About Inserting a Row Containing a BFILE
-
12
Using LOB APIs
- 12.1 Supported Environments
- 12.2 About Appending One LOB to Another
- 12.3 About Determining Character Set Form
- 12.4 About Determining Character Set ID
- 12.5 Loading a LOB with Data from a BFILE
- 12.6 About Loading a BLOB with Data from a BFILE
- 12.7 Loading a CLOB or NCLOB with Data from a BFILE
- 12.8 Determining Whether a LOB is Open
- 12.9 About Displaying LOB Data
- 12.10 About Reading Data from a LOB
- 12.11 About LOB Array Read
- 12.12 Reading a Portion of a LOB (SUBSTR)
- 12.13 Comparing All or Part of Two LOBs
- 12.14 Patterns: Checking for Patterns in a LOB Using INSTR
- 12.15 Length: Determining the Length of a LOB
- 12.16 Copying All or Part of One LOB to Another LOB
- 12.17 Copying a LOB Locator
- 12.18 Equality: Checking If One LOB Locator Is Equal to Another
- 12.19 About Determining Whether LOB Locator Is Initialized
- 12.20 About Appending to a LOB
- 12.21 About Writing Data to a LOB
- 12.22 LOB Array Write
- 12.23 About Trimming LOB Data
- 12.24 About Erasing Part of a LOB
- 12.25 Determining Whether a LOB instance Is Temporary
- 12.26 Converting a BLOB to a CLOB
- 12.27 Converting a CLOB to a BLOB
- 12.28 Ensuring Read Consistency
-
10
Overview of Supplied LOB APIs
-
Part IV Application Design with LOBs
-
13
LOB Storage with Applications
- 13.1 Tables That Contain LOBs
- 13.2 Data Types for LOB Columns
-
13.3
LOB Storage Parameters
- 13.3.1 Inline and Out-of-Line LOB Storage
- 13.3.2 Defining Tablespace and Storage Characteristics for Persistent LOBs
- 13.3.3 LOB Storage Characteristics for LOB Column or Attribute
- 13.3.4 TABLESPACE and LOB Index
- 13.3.5 PCTVERSION
- 13.3.6 RETENTION Parameter for BasicFiles LOBs
- 13.3.7 RETENTION Parameter for SecureFiles LOBs
- 13.3.8 CACHE / NOCACHE / CACHE READS
- 13.3.9 LOGGING / NOLOGGING Parameter for BasicFiles LOBs
- 13.3.10 LOGGING/FILESYSTEM_LIKE_LOGGING for SecureFiles LOBs
- 13.3.11 CHUNK
- 13.3.12 ENABLE or DISABLE STORAGE IN ROW Clause
- 13.3.13 Guidelines for ENABLE or DISABLE STORAGE IN ROW
- 13.4 LOB Columns Indexing
-
13.5
LOB Manipulation in Partitioned Tables
- 13.5.1 About Manipulating LOBs in Partitioned Tables
- 13.5.2 Partitioning a Table Containing LOB Columns
- 13.5.3 Creating an Index on a Table Containing Partitioned LOB Columns
- 13.5.4 Moving Partitions Containing LOBs
- 13.5.5 Splitting Partitions Containing LOBs
- 13.5.6 Merging Partitions Containing LOBs
- 13.6 LOBs in Index Organized Tables
- 13.7 Restrictions on Index Organized Tables with LOB Columns
- 13.8 Restrictions for LOBs in Partitioned Index-Organized Tables
- 13.9 Updating LOBs in Nested Tables
-
14
Advanced Design Considerations
- 14.1 Opening Persistent LOBs with the OPEN and CLOSE Interfaces
-
14.2
Read-Consistent Locators
- 14.2.1 A Selected Locator Becomes a Read-Consistent Locator
- 14.2.2 Example of Updating LOBs and Read-Consistency
- 14.2.3 Example of Updating LOBs Through Updated Locators
- 14.2.4 Example of Updating a LOB Using SQL DML and DBMS_LOB
- 14.2.5 Example of Using One Locator to Update the Same LOB Value
- 14.2.6 Example of Updating a LOB with a PL/SQL (DBMS_LOB) Bind Variable
- 14.2.7 Example of Deleting a LOB Using Locator
-
14.3
LOB Locators and Transaction Boundaries
- 14.3.1 About LOB Locators and Transaction Boundaries
- 14.3.2 Read and Write Operations on a LOB Using Locators
- 14.3.3 Selecting the Locator Outside of the Transaction Boundary
- 14.3.4 Selecting the Locator Within a Transaction Boundary
- 14.3.5 LOB Locators Cannot Span Transactions
- 14.3.6 Example of Locator Not Spanning a Transaction
- 14.4 LOBs in the Object Cache
- 14.5 Terabyte-Size LOB Support
- 14.6 Guidelines for Creating Gigabyte LOBs
- 15 Performance Guidelines
-
13
LOB Storage with Applications
-
Part V LOB Administration
- 16 Managing LOBs: Database Administration
-
17
Migrating Columns from LONGs to LOBs
- 17.1 Benefits of Migrating LONG Columns to LOB Columns
- 17.2 Preconditions for Migrating LONG Columns to LOB Columns
- 17.3 Determining how to Optimize the Application Using utldtree.sql
- 17.4 Converting Tables from LONG to LOB Data Types
-
17.5
Migrating Applications from LONGs to LOBs
- 17.5.1 About Migrating Applications from Longs to LOBs
- 17.5.2 LOB Columns Are Not Allowed in Clustered Tables
- 17.5.3 LOB Columns Are Not Allowed in AFTER UPDATE OF Triggers
- 17.5.4 Rebuilding Indexes on Columns Converted from LONG to LOB Data Types
- 17.5.5 Empty LOBs Compared to NULL and Zero Length LONGs
- 17.5.6 Overloading with Anchored Types
- 17.5.7 Some Implicit Conversions Are Not Supported for LOB Data Types
- Part VI Oracle File System (OFS) Server
-
Part VII Database File System (DBFS)
- 20 Introducing the Database File System
-
21
DBFS SecureFiles Store
- 21.1 Setting Up a SecureFiles Store
- 21.2 Using a DBFS SecureFiles Store File System
- 21.3 About DBFS SecureFiles Store Package, DBMS_DBFS_SFS
- 21.4 Database File System (DBFS)— POSIX File Locking
- 22 DBFS Hierarchical Store
-
23
DBFS Content API
- 23.1 Overview of DBFS Content API
- 23.2 Stores and DBFS Content API
-
23.3
Getting Started with DBMS_DBFS_CONTENT Package
- 23.3.1 DBFS Content API Role
- 23.3.2 Path Name Constants and Types
- 23.3.3 Path Properties
- 23.3.4 Content IDs
- 23.3.5 Path Name Types
- 23.3.6 Store Features
- 23.3.7 Lock Types
- 23.3.8 Standard Properties
- 23.3.9 Optional Properties
- 23.3.10 User-Defined Properties
- 23.3.11 Property Access Flags
- 23.3.12 Exceptions
- 23.3.13 Property Bundles
- 23.3.14 Store Descriptors
-
23.4
Administrative and Query APIs
- 23.4.1 Registering a Content Store
- 23.4.2 Unregistering a Content Store
- 23.4.3 Mounting a Registered Store
- 23.4.4 Unmounting a Previously Mounted Store
- 23.4.5 Listing all Available Stores and Their Features
- 23.4.6 Listing all Available Mount Points
- 23.4.7 Looking Up Specific Stores and Their Features
- 23.5 Querying DBFS Content API Space Usage
- 23.6 DBFS Content API Session Defaults
- 23.7 DBFS Content API Interface Versioning
- 23.8 Notes on DBFS Content API Path Names
- 23.9 DBFS Content API Creation Operations
- 23.10 DBFS Content API Deletion Operations
- 23.11 DBFS Content API Path Get and Put Operations
- 23.12 DBFS Content API Rename and Move Operations
- 23.13 Directory Listings
- 23.14 DBFS Content API Directory Navigation and Search
- 23.15 DBFS Content API Locking Operations
- 23.16 DBFS Content API Access Checks
- 23.17 DBFS Content API Abstract Operations
- 23.18 DBFS Content API Path Normalization
- 23.19 DBFS Content API Statistics Support
- 23.20 DBFS Content API Tracing Support
- 23.21 Resource and Property Views
- 24 Creating Your Own DBFS Store
-
25
Using DBFS
- 25.1 DBFS Installation
- 25.2 Creating a DBFS File System
-
25.3
DBFS File System Access
- 25.3.1 DBFS Client Prerequisites
- 25.3.2 DBFS Client Command-Line Interface Operations
-
25.3.3
DBFS Mounting Interface (Linux and Solaris Only)
- 25.3.3.1 Installing FUSE on Solaris 11 SRU7 and Later
- 25.3.3.2 Mounting the DBFS Store
- 25.3.3.3 Solaris-Specific Privileges
- 25.3.3.4 About the Mount Command for Solaris and Linux
- 25.3.3.5 Mounting a File System with a Wallet
- 25.3.3.6 Mounting a File System with Password at Command Prompt
- 25.3.3.7 Mounting a File System with Password Read from a File
- 25.3.3.8 Unmounting a File System
- 25.3.3.9 Mounting DBFS Through fstab Utility for Linux
- 25.3.3.10 Mounting DBFS Through the vfstab Utility for Solaris
- 25.3.3.11 Restrictions on Mounted File Systems
- 25.3.4 File System Security Model
- 25.3.5 HTTP, WebDAV, and FTP Access to DBFS
- 25.4 DBFS Administration
- 25.5 Shrinking and Reorganizing DBFS Filesystems
- A LOB Demonstration Files
- Glossary
- Index