Table of Contents
- Title and Copyright Information
- Preface
- Changes in Oracle Database
-
Part I Getting Started
-
1
Introduction to Large Objects and SecureFiles
- What Are Large Objects?
- Why Use Large Objects?
- Why Not Use LONGs?
- Different Kinds of LOBs
- LOB Locators
- Database Semantics for Internal and External LOBs
- Large Object Data Types
- About Object Data Types and LOBs
- Storage and Creation of Other Data Types with LOBs
- BasicFiles and SecureFiles LOBs
- Database File System (DBFS)
- 2 Working with LOBs
-
3
Using Oracle LOB Storage
- LOB Storage
- CREATE TABLE with LOB Storage
- ALTER TABLE with LOB Storage
- Initialization, Compatibility, and Upgrading
- Migrating Columns from BasicFiles LOBs to SecureFiles LOBs
- 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
- 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
- About Inserting Rows with LOB Locator Bind Variables
- 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
-
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
- 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
- About 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
-
The Data Interface Used for Persistent LOBs in OCI
- LOB Data Types Bound in OCI
- LOB Data Types Defined in OCI
- Multibyte Character Sets Used in OCI with the Data Interface for LOBs
- OCI Functions Used to Perform INSERT or UPDATE on LOB Columns
- The Data Interface Used to Fetch LOB Data in OCI
- PL/SQL and C Binds from OCI
- 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
- 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
- The Data Interface Used with Persistent LOBs in Java
- The Data Interface Used with Remote LOBs
-
Part III Reference Semantics LOBs
-
10
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
- 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 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
- 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
- Providing 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 to Open and Close LOBs
-
Using COBOL (Pro*COBOL) to Work With LOBs
- Providing 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 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
- Calling DBMS_LOB Package from Java (JDBC)
- Prefetching LOBs to Improve Performance
- Zero-Copy Input/Output for SecureFiles to Improve Performance
- 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 Streaming BLOB Data
- 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 Streaming CLOB Data
- JDBC oracle.sql.BFILE Methods to Read or Examine External LOB (BFILE) Values
- JDBC oracle.sql.BFILE Methods and Properties for Streaming BFILE Data
- JDBC Temporary LOB APIs
- JDBC: Opening and Closing LOBs
- JDBC: Opening and Closing BLOBs
- JDBC: Opening and Closing CLOBs
- JDBC: Opening and Closing BFILEs
- Truncating LOBs Using JDBC
- JDBC BLOB Streaming APIs
- JDBC CLOB Streaming APIs
- BFILE Streaming APIs
- JDBC and Empty LOBs
- Oracle Provider for OLE DB (OraOLEDB)
- Overview of Oracle Data Provider for .NET (ODP.NET)
-
11
LOB APIs for BFILE Operations
- Supported Environments for BFILE APIs
- About Accessing BFILEs
- Directory Objects
- BFILENAME and Initialization
- Characteristics of the BFILE Data Type
- BFILE Security
- About Loading a LOB with BFILE Data
- About Opening a BFILE with OPEN
- About Opening a BFILE with FILEOPEN
- About Determining Whether a BFILE Is Open Using ISOPEN
- About Determining Whether a BFILE Is Open with FILEISOPEN
- About Displaying BFILE Data
- About Reading Data from a BFILE
- About 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
- About Assigning a BFILE Locator
- Getting Directory Object Name and File Name of a BFILE
- About Updating a BFILE by Initializing a BFILE Locator
- Closing a BFILE with FILECLOSE
- Closing a BFILE with CLOSE
- Closing All Open BFILEs with FILECLOSEALL
- About Inserting a Row Containing a BFILE
-
12
Using LOB APIs
- Supported Environments
- About Appending One LOB to Another
- About Determining Character Set Form
- About Determining Character Set ID
- Loading a LOB with Data from a BFILE
- About Loading a BLOB with Data from a BFILE
- Loading a CLOB or NCLOB with Data from a BFILE
- Determining Whether a LOB is Open
- About Displaying LOB Data
- About Reading Data from a LOB
- About 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
- About Determining Whether LOB Locator Is Initialized
- About Appending to a LOB
- About Writing Data to a LOB
- LOB Array Write
- About Trimming LOB Data
- About Erasing Part of a LOB
- Determining Whether a LOB instance Is Temporary
- Converting a BLOB to a CLOB
- Converting a CLOB to a BLOB
- Ensuring Read Consistency
-
10
Overview of Supplied LOB APIs
-
Part IV Application Design with LOBs
-
13
LOB Storage with Applications
- Tables That Contain LOBs
- Data Types for LOB Columns
-
LOB Storage Parameters
- Inline 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 Parameter for BasicFiles LOBs
- RETENTION Parameter for SecureFiles LOBs
- CACHE / NOCACHE / CACHE READS
- LOGGING / NOLOGGING Parameter for BasicFiles LOBs
- LOGGING/FILESYSTEM_LIKE_LOGGING for SecureFiles LOBs
- CHUNK
- ENABLE or DISABLE STORAGE IN ROW Clause
- Guidelines for ENABLE or DISABLE STORAGE IN ROW
- LOB Columns Indexing
- LOB Manipulation in Partitioned Tables
- LOBs in Index Organized Tables
- Restrictions for LOBs in Partitioned Index-Organized Tables
- Updating LOBs in Nested Tables
-
14
Advanced Design Considerations
- Opening Persistent LOBs with the OPEN and CLOSE Interfaces
-
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
- LOBs in the Object Cache
- Terabyte-Size LOB Support
- 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
- Benefits of Migrating LONG Columns to LOB Columns
- Preconditions for Migrating LONG Columns to LOB Columns
- Determining how to Optimize the Application Using utldtree.sql
- Converting Tables from LONG to LOB Data Types
-
Migrating Applications from LONGs to LOBs
- About Migrating Applications from Longs to LOBs
- LOB Columns Are Not Allowed in Clustered Tables
- LOB Columns Are Not Allowed in AFTER UPDATE OF Triggers
- Rebuilding 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 VI Oracle File System (OFS) Server
-
Part VII Database File System (DBFS)
- 20 Introducing the Database File System
- 21 DBFS SecureFiles Store
- 22 DBFS Hierarchical Store
-
23
DBFS Content API
- Overview of DBFS Content API
- Stores and DBFS Content API
- Getting Started with DBMS_DBFS_CONTENT Package
- Administrative and Query APIs
- Querying DBFS Content API Space Usage
- DBFS Content API Session Defaults
- DBFS Content API Interface Versioning
- Notes on DBFS Content API 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
- 24 Creating Your Own DBFS Store
-
25
Using DBFS
- DBFS Installation
- Creating a DBFS File System
-
DBFS File System Access
- DBFS Client Prerequisites
- DBFS Client Command-Line Interface Operations
-
DBFS Mounting Interface (Linux and Solaris Only)
- Installing FUSE on Solaris 11 SRU7 and Later
- Mounting the DBFS Store
- Solaris-Specific Privileges
- About the Mount Command for Solaris and Linux
- Mounting a File System with a Wallet
- Mounting a File System with Password at Command Prompt
- Mounting a File System with Password Read from a File
- 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
- HTTP, WebDAV, and FTP Access to DBFS
- DBFS Administration
- Shrinking and Reorganizing DBFS Filesystems
- A LOB Demonstration Files
- Glossary
- Index