Oracle8i Application Developer's Guide - Large Objects (LOBs)
Release 2 (8.1.6)
Part Number A76940-01

Contents

Title and Copyright Information

Send Us Your Comments

Preface

Use Cases Diagram Elements

1 Introduction

Why Use LOBs?

Unstructured Data

LOB Datatype Helps Support Internet Applications

Why Not Use LONGs?

LOBs Help Control Semantics

LOBS Enable interMEDIA

LOB "Demo" Directory

Compatibility and Migration Issues

Examples in This Manual Use Multimedia_Tab

For Further Information

2 Basic Components

The LOB Datatype

External LOBs (BFILEs)

Internal LOBs Use Copy Semantics, External LOBs Use Reference Semantics

Varying-Width Character Data

The LOB Locator

LOB Value and Locators

LOB Locator Operations

Creating Tables that Contain LOBs

Initializing Internal LOBs to NULL or Empty

Initializing Internal LOB Columns to a Value

Initializing External LOBs to NULL or a File Name

3 LOB Programmatic Environments

Six Programmatic Environments Operate on LOBs

Comparison of the Six LOB Interfaces

Using PL/SQL (DBMS_LOB Package) to Work With LOBs

Provide a LOB Locator Before Invoking the DBMS_LOB Routine

Client PL/SQL Procedures Cannot Call DBMS_LOB Routines

Offset and Amount Parameters: Fixed-Width Versus Varying-Width, Character or Byte For DBMS_LOB Package

DBMS_LOB.LOADFROMFILE: Specify Amount Parameter to be Less than Size of BFILE!

DBMS_LOB.READ: Amount Parameter Can be Larger than Data Size

PL/SQL Functions and Procedures that Operate on BLOBs, CLOBs, NCLOBs, and BFILEs

PL/SQL Functions/Procedures To Modify BLOB, CLOB, and NCLOB Values

PL/SQL Functions/Procedures To Read or Examine Internal and External LOB Values

PL/SQL Functions/Procedures To Operate on Temporary LOBs

PL/SQL Read-Only Functions/Procedures for BFILEs

PL/SQL Functions/Procedures To Open and Close Internal and External LOBs

Using C (OCI) to Work With LOBs

Set CSID Parameter to OCI_UCS2ID to Read/Write in UCS2

Offset and Amount Parameters: Fixed-Width Versus Varying-Width, Character or Byte With OCI

OCILobLoadFromFile: Specify Amount Parameter to be Less than Length of BFILE

OCILobRead: Specify Amount Parameter to be 4 gigabytes - 1

OCI LOB Examples

Further Information About OCI:

OCI Functions that Operate on BLObs, BLOBs, NCLOBs, and BFILEs

OCI Functions To Modify Internal LOB (BLOB, CLOB, and NCLOB) Values

OCI Functions To Read or Examine Internal 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

Example Procedure: main() and seeIfLOBOpen

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 Internal LOBs (BLOB, CLOB, and NCLOB) Values

Pro*C/C++ Embedded SQL Statements To Read or Examine Internal and External LOB Values

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 Internal LOBs and External LOBs (BFILEs)

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 Emvbedded SQL Statements To Modify Internal LOBs (BLOB, CLOB, and NCLOB) Values

Pro*COBOL Embedded SQL Statements To Read or Examine Internal and External LOB Values

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 To Open and Close Internal LOBs and External LOBs (BFILEs)

Using Visual Basic (Oracle Objects for OLE (OO4O)) to Work with LOBs

OO4O Syntax Reference and Further Information

OraBlob, OraClob, and OraBfile Object Interfaces Encapsulate Locators

Example of OraBlob and OraBfile

OO4O Methods and Properties to Access Data Stored in BLOBs, CLOBs, NCLOBs, and BFILEs

OO4O Methods To Modify Internal LOBs (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 Internal 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 LOBs with Java

Reading Internal LOBs and External LOBs (BFILEs) with Java

Calling DBMS_LOB Package

Referencing the LOBs

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 Values

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: OracleBlob and OracleClob Do Not Work in 8.1.x and Future Releases

4 Managing LOBs

DBA Actions Required Prior to Working with LOBs

Set Maximum Number of Open BFILEs

Using SQL DML for Basic Operations on LOBs

Changing Tablespace Storage for a LOB

Managing Temporary LOBs

Using SQL Loader to Load LOBs

Loading InLine and Out-Of-Line Data into Internal LOBs Using SQL Loader

SQL Loader Performance: Loading Into Internal LOBs

Loading Inline LOB Data

Loading Inline LOB Data in Predetermined Size Fields

Loading Inline LOB Data in Delimited Fields

Loading Inline LOB Data in Length-Value Pair Fields

Loading Out-Of-Line LOB Data

Loading One LOB Per File

Loading Out-of-Line LOB Data in Predetermined Size Fields

Loading Out-of-Line LOB Data in Delimited Fields

Loading Out-of-Line LOB Data in Length-Value Pair Fields

SQL Loader LOB Loading Tips

LOB Restrictions

Removed Restrictions

5 Advanced Topics

Read-Consistent Locators

A Selected Locator Becomes a Read Consistent Locator

Updating LOBs and Read-Consistency

Example of an Update Using Read Consistent Locators

Updated LObs Via 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 Cannot Span Transactions

Example of Locator Not Spanning a Transaction

LOB Locators and Transaction Boundaries

Locators Contain Transaction IDs When...

Locators Do Not Contain Transaction IDs When...

Transaction IDs: Reading and Writing to a LOB Using Locators

Non-Serializable Example: Selecting the Locator with No Current Transaction

Non-Serializable Example: Selecting the Locator within a Transaction

LOBs in the Object Cache

LOB Buffering Subsystem

Advantages of LOB Buffering

Guidelines for Using LOB Buffering

LOB Buffering Usage Notes

Flushing the LOB Buffer

Flushing the Updated LOB

Using Buffer-Enabled Locators

Saving Locator State to Avoid a Reselect

OCI Example of LOB Buffering

Creating a Varray Containing References to LOBs

6 Frequently Asked Questions

Converting Data Types to LOB Data Types

Can I Insert or Update Any Length Data Into a LOB Column?

Does COPY LONG to LOB Work if Data is > 64K?

How Do I Determine if the LOB Column with a Trigger is Being Updated?

Reading and Loading LOB Data: What Should Amount Parameter Size Be?

Index-Organized Tables (IOTs) and LOBs

Is Inline Storage Allowed for LOBs in Index-Organized Tables?

Initializing LOB Locators

When Do I Use EMPTY_BLOB() and EMPTY_CLOB()?

How Do I Initialize a BLOB Attribute Using EMPTY_BLOB() in Java?

JDBC, JPublisher and LOBs

How Do I Insert a Row With Empty LOB Locator into Table Using JDBC?

How Do I setData to EMPTY_BLOB() Using JPublisher?

JDBC: Do OracleBlob and OracleClob Work in 8.1.x?

How Do I Manipulate LOBs With the 8.1.5 JDBC Thin Driver?

Is the FOR UPDATE Clause Needed on SELECT When Writing to a LOB?

Loading LOBs and Data Into LOBs

How do I Load a 1Mb File into a CLOB Column?

How Do We Improve BLOB and CLOB Performance When Using JDBC Driver To Load?

LOB Indexing

Is LOB Index Created in Same Tablespace as LOB Data?

Indexing: Why is a BLOB Column Removed on DELETing but not a BFILE Column?

Which Views Can I Query to Find Out About a LOB Index?

LOB Storage and Space Issues

What Happens If I Specify LOB Tablespace and ENABLE STORAGE IN ROW?

What Are the Pros and Cons of Storing Images in a BFILE Versus a BLOB?

When Should I Specify DISABLE STORAGE IN ROW?

Do <4K BLOBs Go Into the Same Segment as Table Data, >4K BLOBs Go Into a Specified Segment?

Is 4K LOB Stored Inline?

How is a LOB Locator Stored If the LOB Column is EMPTY_CLOB() or EMPTY_BLOB() Instead of NULL? Are Extra Data Blocks Used For This?

Migrating From Other Database Systems

Is Implicit LOB Conversion Between Different LOB Types Allowed in Oracle8i?

Performance

What Can We Do To Improve the Poor LOB Loading Performance When Using Veritas File System on Disk Arrays, UNIX, and Oracle?

Is There a Difference in Performance When Using DBMS_LOB.SUBSTR Versus DBMS_LOB.READ?

Are There Any White Papers or Guidelines on Tuning LOB Performance?

When Should I Use Chunks Over Reading the Whole Thing?

Is Inlining the LOB a Good Idea and If So When?

How Can I Store LOBs >4Gb in the Database?

7 Modeling and Design

Selecting a Datatype

LOBs in Comparison to LONG and LONG RAW Types

Character Set Conversions: Working with Varying-Width Character Data

Selecting a Table Architecture

LOB Storage

Where are NULL Values in a LOB Column Stored?

Defining Tablespace and Storage Characteristics for Internal LOBs

LOB Storage Characteristics for LOB Column or Attribute

TABLESPACE and LOB Index

CACHE / NOCACHE / CACHE READS

LOGGING / NOLOGGING

ENABLE | DISABLE STORAGE IN ROW

How to Create Gigabyte LOBs

Example: Creating a Tablespace and Table to Store Gigabyte LOBs

LOB Locators and Transaction Boundaries

Binds Greater Than 4,000 Bytes in INSERTs and UPDATEs

Binds Greater than 4,000 Bytes are Now Allowed For LOB INSERTs and UPDATEs

Binds of More Than 4,000 Bytes ... No HEX to RAW or RAW to HEX Conversion

4,000 Byte Limit On Results of SQL Operator

Binds of More Than 4,000 Bytes: Restrictions

Example: PL/SQL - Using Binds of More Than 4,000 Bytes in INSERT and UPDATE

Example: PL/SQL - Binds of More Than 4,000 Bytes -- Inserts Not Supported Because Hex to Raw/Raw to Hex Conversion is Not Supported

Example: PL/SQL - 4,000 Byte Result Limit in Binds of More than 4,000 Bytes When Data Includes SQL Operator

Open, Close and IsOpen Interfaces for Internal LOBs

LOBs in Index Organized Tables (IOT)

Example of Index Organized Table (IOT) with LOB Columns

Manipulating LOBs in Partitioned Tables

Creating and Partitioning a Table Containing LOB Data

Creating an Index on a Table Containing LOB Columns

Exchanging Partitions Containing LOB Data

Adding Partitions to Tables Containing LOB Data

Moving Partitions Containing LOBs

Splitting Partitions Containing LOBs

Indexing a LOB Column

Best Performance Practices

Using SQL Loader

Guidelines for Best Performance

Moving Data to LOB in Threaded Environment

8 Sample Application

A Sample Application

The Multimedia Content-Collection System

Applying an Object-Relational Design to the Application

Structure of Multimedia_tab Table

9 Internal Persistent LOBs

Use Case Model: Internal Persistent LOBs

Three Ways to Create a Table Containing a LOB

Usage Notes

CREATE a Table Containing One or More LOB Columns

SQL: Create a Table Containing One or More LOB Columns

CREATE a Table Containing an Object Type with a LOB Attribute

SQL: Create a Table Containing an Object Type with a LOB Attribute

CREATE a Nested Table Containing a LOB

SQL: Create a Nested Table Containing a LOB

Three Ways Of Inserting One or More LOB Values into a Row

INSERT a LOB Value using EMPTY_CLOB() or EMPTY_BLOB()

SQL: Insert a Value Using EMPTY_CLOB() / EMPTY_BLOB()

Java (JDBC): Insert a Value Using EMPTY_CLOB() / EMPTY_BLOB()

INSERT a Row by Selecting a LOB From Another Table

SQL: Insert a Row by Selecting a LOB from Another Table

INSERT Row by Initializing a LOB Locator Bind Variable

PL/SQL (DBMS_LOB Package): Insert Row by Initializing a LOB Locator Bind Variable

C (OCI): Insert Row by Initializing a LOB Locator Bind Variable

COBOL (Pro*COBOL): Insert Row by Initializing a LOB Locator Bind Variable

C/C++ (ProC/C++): Insert Row by Initializing a LOB Locator Bind Variable

Visual Basic (OO4O): Insert Row by Initializing a LOB Locator Bind Variable

Java (JDBC): Insert Row by Initializing a LOB Locator Bind Variable

Load Data into an Internal LOB (BLOB, CLOB, NCLOB)

Usage Notes and Examples

Load a LOB with Data from a BFILE

PL/SQL (DBMS_LOB Package): Load a LOB with Data from a BFILE

C (OCI): Load a LOB with Data from a BFILE

COBOL (Pro*COBOL): Load a LOB with Data from a BFILE

C/C++ (ProC/C++): Load a LOB with Data from a BFILE

Visual Basic (OO4O): Load a LOB with Data from a BFILE

Java (JDBC): Load a LOB with Data from a BFILE

See If a LOB Is Open

PL/SQL (DBMS_LOB Package): See if a LOB is Open

C (OCI): See if a LOB is Open

COBOL (Pro*COBOL): See if a LOB is Open

C/C++ (ProC/C++): See if a LOB is Open

Java (JDBC): See if a LOB is Open

Copy LONG to LOB

SQL: Copy LONG to LOB

Checkout a LOB

PL/SQL (DBMS_LOB Package): Checkout a LOB

C (OCI): Checkout a LOB

COBOL (Pro*COBOL): Checkout a LOB

C/C++ (ProC/C++): Checkout a LOB

Visual Basic (OO4O): Checkout a LOB

Java (JDBC): Checkout a LOB

Checkin a LOB

PL/SQL (DBMS_LOB Package): Checkin a LOB

C (OCI): Checkin a LOB

COBOL (Pro*COBOL): Checkin a LOB

C/C++ (ProC/C++): Checkin a LOB

Visual Basic (OO4O): Checkin a LOB

Java (JDBC): Checkin a LOB

Display LOB Data

PL/SQL (DBMS_LOB Package): Display LOB Data

C (OCI): Display LOB Data

COBOL (Pro*COBOL): Display LOB Data

C/C++ (ProC/C++): Display LOB Data

Visual Basic (OO4O): Display LOB Data

Java (JDBC): Display LOB Data

Read Data from LOB

PL/SQL (DBMS_LOB Package): Read Data from LOB

C (OCI): Read Data from LOB

COBOL (Pro*COBOL): Read Data from LOB

C/C++ (Pro*C/C++): Read Data from LOB

Visual Basic (OO4O): Read Data from LOB

Java (JDBC): Read Data from LOB

Read a Portion of the LOB (substr)

PL/SQL (DBMS_LOB Package): Read a Portion of the LOB (substr)

COBOL (Pro*COBOL): Read a Portion of the LOB (substr)

C/C++ (Pro*C/C++): Read a Portion of the LOB (substr)

Visual Basic (OO4O): Read a Portion of the LOB (substr)

Java (JDBC): Read a Portion of the LOB (substr)

Compare All or Part of Two LOBs

PL/SQL (DBMS_LOB Package): Compare All or Part of Two LOBs

COBOL (Pro*COBOL): Compare All or Part of Two LOBs

C/C++ (Pro*C/C++): Compare All or Part of Two LOBs

Visual Basic (OO4O): Compare All or Part of Two LOBs

Java (JDBC): Compare All or Part of Two LOBs

See If a Pattern Exists in the LOB (instr)

PL/SQL (DBMS_LOB Package): See If a Pattern Exists in the LOB (instr)

COBOL (Pro*COBOL): See If a Pattern Exists in the LOB (instr)

C/C++ (Pro*C/C++): See If a Pattern Exists in the LOB (instr)

Java (JDBC): See If a Pattern Exists in the LOB (instr)

Get the Length of a LOB

PL/SQL (DBMS_LOB Package): Get the Length of a LOB

C (OCI): Get the Length of a LOB

COBOL (Pro*COBOL): Get the Length of a LOB

C/C++ (Pro*C/C++): Get the Length of a LOB

Visual Basic (OO4O): Get the Length of a LOB

Java (JDBC): Get the Length of a LOB

Copy All or Part of a LOB to Another LOB

PL/SQL (DBMS_LOB Package): Copy All or Part of a LOB to Another LOB

C (OCI): Copy All or Part of a LOB to Another LOB

COBOL (Pro*COBOL): Copy All or Part of a LOB to Another LOB

C/C++ (Pro*C/C++): Copy All or Part of a LOB to Another LOB

Visual Basic (OO4O): Copy All or Part of a LOB to Another LOB

Java (JDBC): Copy All or Part of a LOB to Another LOB

Copy a LOB Locator

PL/SQL (DBMS_LOB Package): Copy a LOB Locator

C (OCI): Copy a LOB Locator

COBOL (Pro*COBOL): Copy a LOB Locator

C/C++ (Pro*C/C++): Copy a LOB Locator

Visual Basic (OO4O)Copy a LOB Locator

Java (JDBC): Copy a LOB Locator

See If One LOB Locator Is Equal to Another

C (OCI): See If One LOB Locator Is Equal to Another

C/C++ (Pro*C/C++): See If One LOB Locator Is Equal to Another

Java (JDBC): See If One LOB Locator Is Equal to Another

See If a LOB Locator Is Initialized

C (OCI): See If a LOB Locator Is Initialized

C/C++ (Pro*C/C++): See If a LOB Locator Is Initialized

Get Character Set ID

C (OCI): Get Character Set ID

Get Character Set Form

C (OCI): Get Character Set Form

Append One LOB to Another

PL/SQL (DBMS_LOB Package): Append One LOB to Another

C (OCI): Append One LOB to Another

COBOL (Pro*COBOL): Append One LOB to Another

C/C++ (Pro*C/C++): Append One LOB to Another

Visual Basic (OO4O): Append One LOB to Another

Java (JDBC): Append One LOB to Another

Write Append to a LOB

PL/SQL (DBMS_LOB Package): Write Append to a LOB

C (OCI): Write Append to a LOB

COBOL (Pro*COBOL): Write Append to a LOB

C/C++ (Pro*C/C++): Write Append to a LOB

Java (JDBC): Write Append to a LOB

Write Data to a LOB

PL/SQL (DBMS_LOB Package): Write Data to a LOB

C (OCI): Write Data to a LOB

COBOL (Pro*COBOL): Write Data to a LOB

C/C++ (Pro*C/C++): Write Data to a LOB

Visual Basic (OO4O):Write Data to a LOB

Java (JDBC): Write Data to a LOB

Trim LOB Data

PL/SQL (DBMS_LOB Package): Trim LOB Data

C (OCI): Trim LOB Data

COBOL (Pro*COBOL): Trim LOB Data

C/C++ (Pro*C/C++): Trim LOB Data

Visual Basic (OO4O): Trim LOB Data

Java (JDBC): Trim LOB Data

Erase Part of a LOB

PL/SQL (DBMS_LOB Package): Erase Part of a LOB

C (OCI): Erase Part of a LOB

COBOL (Pro*COBOL): Erase Part of a LOB

C/C++ (Pro*C/C++): Erase Part of a LOB

Visual Basic (OO4O): Erase Part of a LOB

Java (JDBC): Erase Part of a LOB

Enable LOB Buffering

C (OCI): Enable LOB Buffering

COBOL (Pro*COBOL): Enable LOB Buffering

C/C++ (Pro*C/C++): Enable LOB Buffering

Visual Basic (OO4O): Enable LOB Buffering

Flush Buffer

C (OCI): Flush Buffer

COBOL (Pro*COBOL): Flush Buffer

C/C++ (Pro*C/C++): Flush Buffer

Visual Basic (OO4O): Flush Buffer

Disable LOB Buffering

C (OCI): Disable LOB Buffering

COBOL (Pro*COBOL): Disable LOB Buffering

C/C++ (Pro*C/C++): Disable LOB Buffering

Visual Basic (OO4O): Disable LOB Buffering

Three Ways to Update a LOB or Entire LOB Data

UPDATE a LOB with EMPTY_CLOB() or EMPTY_BLOB()

SQL: UPDATE a LOB with EMPTY_CLOB() or EMPTY_BLOB()

UPDATE a Row by Selecting a LOB From Another Table

SQL: Update a Row by Selecting a LOB From Another Table

UPDATE by Initializing a LOB Locator Bind Variable

SQL: Update by Initializing a LOB Locator Bind Variable

C (OCI): Update by Initializing a LOB Locator Bind Variable

COBOL (Pro*COBOL): Update by Initializing a LOB Locator Bind Variable

C/C++ (Pro*C/C++): Update by Initializing a LOB Locator Bind Variable

Visual Basic (OO4O): Update by Initializing a LOB Locator Bind Variable

Java (JDBC): Update by Initializing a LOB Locator Bind Variable

DELETE the Row of a Table Containing a LOB

SQL: Delete a LOB

10 Temporary LOBs

Use Case Model: Internal Temporary LOBs

Programmatic Environments

Temporary LOB Locators Can be IN Values

Can You Use the Same Functions for Temporary and Internal Persistent LOBs?

Temporary LOB Data is Stored in Temporary Tablespace

Lifetime and Duration of Temporary LOBs

Memory Handling

Locators and Semantics

Features Specific to Temporary LOBs

Security Issues with Temporary LOBs

NOCOPY Restrictions

Managing Temporary LOBs

Create a Temporary LOB

PL/SQL (DBMS_LOB Package): Create a Temporary LOB

C (OCI): Create a Temporary LOB

COBOL (Pro*COBOL): Create a Temporary LOB

C/C++ (Pro*C/C++): Create a Temporary LOB

See If a LOB is Temporary

PL/SQL (DBMS_LOB Package): See If a LOB is Temporary

C (OCI): See If a LOB is Temporary

COBOL (Pro*COBOL): See If a LOB is Temporary

C/C++ (Pro*C/C++): See If a LOB is Temporary

Free a Temporary LOB

PL/SQL (DBMS_LOB Package): Free a Temporary LOB

C (OCI): Free a Temporary LOB

COBOL (Pro*COBOL): Free a Temporary LOB

C/C++ (Pro*C/C++): Free a Temporary LOB

Load a Temporary LOB with Data from a BFILE

PL/SQL (DBMS_LOB Package): Load a Temporary LOB with Data from a BFILE

C (OCI): Load a Temporary LOB with Data from a BFILE

COBOL (Pro*COBOL): Load a Temporary LOB with Data from a BFILE

C/C++ (Pro*C/C++): Load a Temporary LOB with Data from a BFILE

See If a Temporary LOB Is Open

PL/SQL: See if a Temporary LOB is Open

C (OCI): See if a Temporary LOB is Open

COBOL (Pro*COBOL): See if a Temporary LOB is Open

C/C++ (Pro*C/C++): See if a Temporary LOB is Open

Display Temporary LOB Data

PL/SQL (DBMS_LOB Package): Display Temporary LOB Data

C (OCI): Display Temporary LOB Data

COBOL (Pro*COBOL): Display the Temporary LOB Data

C/C++ (Pro*C/C++): Display Temporary LOB Data

Read Data from a Temporary LOB

PL/SQL (DBMS_LOB Package): Read Data from a Temporary LOB

C (OCI): Read Data from a Temporary LOB

COBOL (Pro*COBOL): Read Data from a Temporary LOB

C/C++ (Pro*C/C++): Read Data from a Temporary LOB

Read Portion of Temporary LOB (substr)

PL/SQL (DBMS_LOB Package): Read a Portion of Temporary LOB (substr)

COBOL (Pro*COBOL): Read a Portion of Temporary LOB (substr)

C/C++ (Pro*C/C++): Read a Portion of Temporary LOB (substr)

Compare All or Part of Two (Temporary) LOBs

PL/SQL (DBMS_LOB Package): Compare All or Part of Two (Temporary) LOBs

COBOL (Pro*COBOL): Compare All or Part of Two (Temporary) LOBs

C/C++ (Pro*C/C++): Compare All or Part of Two (Temporary) LOBs

See If a Pattern Exists in a Temporary LOB (instr)

PL/SQL (DBMS_LOB Package): See If a Pattern Exists in a Temporary LOB (instr)

COBOL (Pro*COBOL): See If a Pattern Exists in a Temporary LOB (instr)

C/C++ (Pro*C/C++): See If a Pattern Exists in a Temporary LOB (instr)

Get the Length of a Temporary LOB

PL/SQL (DBMS_LOB Package): Get the Length of a Temporary LOB

C (OCI): Get the Length of a Temporary LOB

COBOL (Pro*COBOL: Get the Length of a Temporary LOB

C/C++ (Pro*C/C++): Get the Length of a Temporary LOB

Copy All or Part of One (Temporary) LOB to Another

PL/SQL (DBMS_LOB Package): Copy All or Part of One (Temporary) LOB to Another

C (OCI): Copy All or Part of One (Temporary) LOB to Another

COBOL (Pro*COBOL): Copy All or Part of One (Temporary) LOB to Another

C/C++ (Pro*C/C++): Copy All or Part of One (Temporary) LOB to Another

Copy a LOB Locator for a Temporary LOB

PL/SQL (DBMS_LOB Package): Copy a LOB Locator for a Temporary LOB

C (OCI): Copy a LOB Locator for a Temporary LOB

COBOL (Pro*COBOL): Copy a LOB Locator for a Temporary LOB

C/C++ (Pro*C/C++): Copy a LOB Locator for a Temporary LOB

Is One Temporary LOB Locator Equal to Another

C (OCI): See If One LOB Locator for a Temporary LOB Is Equal to Another

C/C++ (Pro*C/C++): See If One LOB Locator for a Temporary LOB Is Equal to Another

See If a LOB Locator for a Temporary LOB Is Initialized

C (OCI): See If a LOB Locator for a Temporary LOB Is Initialized

C/C++ (Pro*C/C++): See If a LOB Locator for a Temporary LOB Is Initialized

Get Character Set ID of a Temporary LOB

C (OCI): Get Character Set ID of a Temporary LOB

Get Character Set Form of a Temporary LOB

C (OCI): Get Character Set Form of a Temporary LOB

Append One (Temporary) LOB to Another

PL/SQL (DBMS_LOB Package): Append One (Temporary) LOB to Another

C (OCI): Append One (Temporary) LOB to Another

COBOL (Pro*COBOL): Append One (Temporary) LOB to Another

C/C++ (Pro*C/C++): Append One (Temporary) LOB to Another

Write Append to a Temporary LOB

PL/SQL (DBMS_LOB Package): Write Append to a Temporary LOB

C (OCI): Write Append to a Temporary LOB

COBOL (Pro*COBOL): Write Append to a Temporary LOB

C/C++ (Pro*C/C++): Write Append to a Temporary LOB

Write Data to a Temporary LOB

PL/SQL (DBMS_LOB Package): Write Data to a Temporary LOB

C (OCI): Write Data to a Temporary LOB

COBOL (Pro*COBOL):Write Data to a Temporary LOB

C/C++ (Pro*C/C++): Write Data to a Temporary LOB

Trim Temporary LOB Data

PL/SQL (DBMS_LOB Package): Trim Temporary LOB Data

C (OCI): Trim Temporary LOB Data

COBOL (Pro*COBOL): Trim Temporary LOB Data

C/C++ (Pro*C/C++): Trim Temporary LOB Data

Erase Part of a Temporary LOB

PL/SQL (DBMS_LOB Package): Erase Part of a Temporary LOB

C (OCI): Erase Part of a Temporary LOB

COBOL (Pro*COBOL): Erase Part of a Temporary LOB

C/C++ (Pro*C/C++): Erase Part of a Temporary LOB

Enable LOB Buffering for a Temporary LOB

C (OCI): Enable LOB Buffering for a Temporary LOB

COBOL (Pro*COBOL): Enable LOB Buffering for a Temporary LOB

C/C++ (Pro*C/C++): Enable LOB Buffering for a Temporary LOB

Flush Buffer for a Temporary LOB

C (OCI): Flush Buffer for a Temporary LOB

COBOL (Pro*COBOL): Flush Buffer for a Temporary LOB

C/C++ (Pro*C/C++): Flush Buffer for a Temporary LOB

Disable LOB Buffering for a Temporary LOB

C (OCI): Disable LOB Buffering

COBOL (Pro*COBOL): Disable LOB Buffering for a Temporary LOB

C/C++ (Pro*C/C++): Disable LOB Buffering for a Temporary LOB

11 External LOBs (BFILEs)

Use Case Model: External LOBs (BFILEs)

Accessing External LOBs (BFILEs)

Directory Object

Initializing a BFILE Locator

How to Associate Operating System Files with Database Records

BFILENAME() and Initialization

DIRECTORY Name Specification

BFILE Security

Ownership and Privileges

Read Permission on Directory Object

SQL DDL for BFILE Security

SQL DML for BFILE Security

Catalog Views on Directories

Guidelines for DIRECTORY Usage

BFILEs in Multi-Threaded Server (MTS) Mode

External LOB (BFILE) Locators

Three Ways to Create a Table Containing a BFILE

CREATE a Table Containing One or More BFILE Columns

SQL: Create a Table Containing One or More BFILE Columns

CREATE a Table of an Object Type with a BFILE Attribute

SQL: Create a Table of an Object Type with a BFILE Attribute

CREATE a Table with a Nested Table Containing a BFILE

SQL: Create a Table with a Nested Table Containing a BFILE

Three Ways to Insert a Row Containing a BFILE

INSERT a Row Using BFILENAME()

SQL: Insert a Row by means of BFILENAME()

C (OCI): Insert a Row by means of BFILENAME()

COBOL (Pro*COBOL): Insert a Row by means of BFILENAME()

C/C++ (Pro*C/C++): Insert a Row by means of BFILENAME()

Visual Basic (OO4O): Insert a Row by means of BFILENAME()

Java (JDBC): Insert a Row by means of BFILENAME()

INSERT a BFILE Row by Selecting a BFILE From Another Table

SQL: Insert a Row Containing a BFILE by Selecting a BFILE From Another Table

INSERT Row With BFILE by Initializing BFILE Locator

PL/SQL: Insert a Row Containing a BFILE by Initializing a BFILE Locator

C (OCI: Insert a Row Containing a BFILE by Initializing a BFILE Locator

COBOL (Pro*COBOL)Insert a Row Containing a BFILE by Initializing a BFILE Locator

C/C++ (Pro*C/C++): Insert a Row Containing a BFILE by Initializing a BFILE Locator

Visual Basic (OO4O): Insert a Row Containing a BFILE by Initializing a BFILE Locator

Java (JDBC): Insert a Row Containing a BFILE by Initializing a BFILE Locator

Load Data Into External LOB (BFILE)

Loading Data Into BFILES: File Name Only is Specified Dynamically

Loading Data into BFILES: File Name and DIRECTORY Object Dynamically Specified

Load a LOB with BFILE Data

PL/SQL (DBMS_LOB Package): Load a LOB with BFILE Data

C (OCI): Load a LOB with BFILE Data

COBOL (Pro*COBOL): Load a LOB with BFILE Data

C/C++ (Pro*C/C++): Load a LOB with BFILE Data

Visual Basic (OO4O): Load a LOB with BFILE Data

Java (JDBC): Load a LOB with BFILE Data

Two Ways to Open a BFILE

Recommendation: Use OPEN to Open BFILE

Specify the Maximum Number of Open BFILEs: SESSION_MAX_OPEN_FILES

Open a BFILE with FILEOPEN

PL/SQL: Open a BFILE with FILEOPEN

C (OCI): Open a BFILE with FILEOPEN

Visual Basic (OO4O): Open a BFILE with FILEOPEN

Java (JDBC): Open a BFILE with FILEOPEN

Open a BFILE with OPEN

PL/SQL: Open a BFILE with OPEN

C (OCI): Open a BFILE with OPEN

COBOL (Pro*COBOL): Open a BFILE with OPEN

C/C++ (Pro*C/C++): Open a BFILE with OPEN

Visual Basic (OO4O): Open a BFILE with OPEN

Java (JDBC): Open a BFILE with OPEN

Two Ways to See If a BFILE is Open

Recommendation: Use OPEN to Open BFILE

Specify the Maximum Number of Open BFILEs: SESSION_MAX_OPEN_FILES

See If the BFILE is Open with FILEISOPEN

PL/SQL (DBMS_LOB Package): See If the BFILE is Open with FILEISOPEN

C (OCI): See If the BFILE is Open with FILEISOPEN

Visual Basic (OO4O): See If the BFILE is Open with FILEISOPEN

Java (JDBC): See If the BFILE is Open with FILEISOPEN

See If a BFILE is Open Using ISOPEN

PL/SQL (DBMS_LOB Package): See If the BFILE is Open with ISOPEN

C (OCI): See If the BFILE is Open with ISOPEN

COBOL (Pro*COBOL): See If the BFILE is Open with ISOPEN

C/C++ (Pro*C/C++): See If the BFILE is Open with ISOPEN

Visual Basic (OO4O): See If the BFILE is Open with ISOPEN

Java (JDBC): See If the BFILE is Open with ISOPEN

Display BFILE Data

PL/SQL: Display BFILE Data

C (OCI): Display BFILE Data

COBOL (Pro*COBOL): Display BFILE Data

C/C++ (Pro*C/C++): Display BFILE Data

Visual Basic (OO4O): Display BFILE Data

Java (JDBC): Display BFILE Data

Read Data from a BFILE

PL/SQL (DBMS_LOB Package): Read Data from a BFILE

C (OCI): Read Data from a BFILE

COBOL (Pro*COBOL): Read Data from a BFILE

C/C++ (Pro*C/C++): Read Data from a BFILE

Visual Basic (OO4O): Read Data from a BFILE

Java (JDBC): Read Data from a BFILE

Read a Portion of BFILE Data (substr)

PL/SQL (DBMS_LOB Package): Read a Portion of BFILE Data (substr)

COBOL (Pro*COBOL): Read a Portion of BFILE Data (substr)

C/C++ (Pro*C/C++): Read a Portion of BFILE Data (substr)

Visual Basic (OO4O): Read a Portion of BFILE Data (substr)

Java (JDBC): Read a Portion of BFILE Data (substr)

Compare All or Parts of Two BFILES

PL/SQL (DBMS_LOB Package): Compare All or Parts of Two BFILES

COBOL (Pro*COBOL): Compare All or Parts of Two BFILES

C/C++ (Pro*C/C++): Compare All or Parts of Two BFILES

Visual Basic (OO4O): Compare All or Parts of Two BFILES

Java (JDBC): Compare All or Parts of Two BFILES

See If a Pattern Exists (instr) in the BFILE

PL/SQL (DBMS_LOB Package): See If a Pattern Exists (instr) in the BFILE

COBOL (Pro*COBOL): See If a Pattern Exists (instr) in the BFILE

C/C++ (Pro*C/C++): See If a Pattern Exists (instr) in the BFILE

Java (JDBC): See If a Pattern Exists (instr) in the BFILE

See If the BFILE Exists

PL/SQL (DBMS_LOB Package): See If the BFILE Exists

C (OCI): See If the BFILE Exists

COBOL (Pro*COBOL): See If the BFILE Exists

C/C++ (Pro*C/C++): See If the BFILE Exists

Visual Basic (OO4O): See If the BFILE Exists

Java (JDBC): See If the BFILE Exists

Get the Length of a BFILE

PL/SQL (DBMS_LOB Package): Get the Length of a BFILE

C (OCI): Get the Length of a BFILE

COBOL (Pro*COBOL): Get the Length of a BFILE

C/C++ (Pro*C/C++): Get the Length of a BFILE

Visual Basic (OO4O): Get the Length of a BFILE

Java (JDBC): Get the Length of a BFILE

Copy a LOB Locator for a BFILE

PL/SQL: Copy a LOB Locator for a BFILE

C (OCI): Copy a LOB Locator for a BFILE

COBOL (Pro*COBOL): Copy a LOB Locator for a BFILE

C/C++ (Pro*C/C++): Copy a LOB Locator for a BFILE

Java (JDBC): Copy a LOB Locator for a BFILE

See If a LOB Locator for a BFILE Is Initialized

C (OCI): See If a LOB Locator for a BFILE Is Initialized

C/C++ (Pro*C/C++): See If a LOB Locator for a BFILE Is Initialized

See If One LOB Locator for a BFILE Is Equal to Another

C (OCI): See If One LOB Locator for a BFILE Is Equal to Another

C/C++ (Pro*C/C++): See If One LOB Locator for a BFILE Is Equal to Another

Java (JDBC): See If One LOB Locator for a BFILE Is Equal to Another

Get DIRECTORY Alias and Filename

PL/SQL (DBMS_LOB Package): Get Directory Alias and Filename

C (OCI): Get Directory Alias and Filename

COBOL (Pro*COBOL): Get Directory Alias and Filename

C/C++ (Pro*C/C++): Get Directory Alias and Filename

Visual Basic (OO4O): Get Directory Alias and Filename

Java (JDBC): Get Directory Alias and Filename

Three Ways to Update a Row Containing a BFILE

UPDATE a BFILE Using BFILENAME()

SQL: Update a BFILE by means of BFILENAME()

UPDATE a BFILE by Selecting a BFILE From Another Table

SQL: Update a BFILE by Selecting a BFILE From Another Table

UPDATE a BFILE by Initializing a BFILE Locator

PL/SQL: Update a BFILE by Initializing a BFILE Locator

C (OCI): Update a BFILE by Initializing a BFILE Locator

COBOL (Pro*COBOL): Update a BFILE by Initializing a BFILE Locator

C/C++ (Pro*C/C++): Update a BFILE by Initializing a BFILE Locator

Visual Basic (OO4O): Update a BFILE by Initializing a BFILE Locator

Java (JDBC): Update a BFILE by Initializing a BFILE Locator

Two Ways to Close a BFILE

Close a BFILE with FILECLOSE

PL/SQL (DBMS_LOB Package): Close a BFile with FILECLOSE

C (OCI): Close a BFile with FILECLOSE

Visual Basic (OO4O): Close a BFile with FILECLOSE

Java (JDBC): Close a BFile with FILECLOSE

Close a BFILE with CLOSE

PL/SQL (DBMS_LOB Package): Close a BFile with CLOSE

C (OCI): Close a BFile with CLOSE

COBOL (Pro*COBOL): Close a BFile with CLOSE

C/C++ (Pro*C/C++): Close a BFile with CLOSE

Visual Basic (OO4O): Close a BFile with CLOSE

Java (JDBC): Close a BFile with CLOSE

Close All Open BFILEs

PL/SQL (DBMS_LOB Package): Close All Open BFiles

C (OCI): Close All Open BFiles

COBOL (Pro*COBOL): Close All Open BFiles

C/C++ (Pro*C/C++): Close All Open BFiles

Visual Basic (OO4O): Close All Open BFiles

Java (JDBC): Close All Open BFiles

DELETE the Row of a Table Containing a BFILE

SQL: Delete a Row from a Table

Index

Oracle

Copyright © 1996-2000, Oracle Corporation.
All Rights Reserved.