Oracle 9i. Application Developers Guide - Large Objects (LOBs)

1102 Pages·2001·6.708 MB·English

by  Higgins S., Kotsovolos S., Raphaely D.

Oracle9i
Application Developer’s Guide - Large Objects (LOBs)
Release 1 (9.0.1)
June 2001
Part No. A88879-01
Oracle9i Application Developer’s Guide - LOBs, Release 1 (9.0.1)
Part No. A88879-01
Copyright © 2001 Oracle Corporation. All rights reserved.
Primary Authors: Shelley Higgins, Susan Kotsovolos, Den Raphaely
Contributing Authors: Kiminari Akiyama, Geeta Arora, Sandeepan Banerjee, Thomas Chang, Eugene
Chong, Souri Das, Chuck Freiwald, Chandrasekharan Iyer, Mahesh Jagannath, Ramkumar Krishnan,
Murali Krishnaprasad, Shoaib Lari, Li-Sen Liu, Dan Mullen,Visar Nimani, Anindo Roy, Samir S. Shah,
Ashok Shivarudraiah, Jags Srinivasan, Rosanne Toohey, Anh-Tuan Tran, Guhan Viswana, Aravind
Yalamanchi
Contributors: Jeya Balaji, Maria Chien, John Kalogeropoulos, Vishy Karra, Padmanabanh Manavazhi,
SujathaMuthulingam,RajivRatnam,ChristianShay,AliShehade,EdShirk,SundaramVedala,EricWan,
Joyce Yang
Graphics: Valerie Moore, Charles Keller
The Programs (which include both the software and documentation) contain proprietary information of
Oracle Corporation; they are provided under a license agreement containing restrictions on use and
disclosure and are also protected by copyright, patent, and other intellectual and industrial property
laws. Reverse engineering, disassembly, or decompilation of the Programs is prohibited.
Theinformationcontainedinthisdocumentissubjecttochangewithoutnotice.Ifyoufindanyproblems
in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this
document is error free. Except as may be expressly permitted in your license agreement for these
Programs, no part of these Programs may be reproduced or transmitted in any form or by any means,
electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation.
If the Programs are delivered to the U.S. Government or anyone licensing or using the programs on
behalf of the U.S. Government, the following notice is applicable:
Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercial
computer software" and use, duplication, and disclosure of the Programs, including documentation,
shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement.
Otherwise, Programs delivered subject to the Federal Acquisition Regulations are "restricted computer
software" and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR
52.227-19, Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500
Oracle Parkway, Redwood City, CA 94065.
The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently
dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup,
redundancy, and other measures to ensure the safe use of such applications if the Programs are used for
such purposes, and Oracle Corporation disclaims liability for any damages caused by such use of the
Programs.
Oracle, Oracle Store, and SQL*Plus are registered trademarks, and ConText, JDeveloper, Net8, Oracle
Call Interface, Oracle interMedia, Oracle Spatial, Oracle WebDB, Oracle8i, Oracle9i, PL/SQL, Pro*C, and
Pro*C/C++ are trademarks or registered trademarks of Oracle Corporation. Other names may be
trademarks of their respective owners.
Contents
Send Us Your Comments............................................................................................................. xxxiii
Preface....................................................................................................................................................... xxxv
Intended Audience.......................................................................................................................... xxxvi
Structure............................................................................................................................................ xxxvi
Related Documents.......................................................................................................................... xxxix
How to Order this Manual.................................................................................................................. xli
Conventions........................................................................................................................................... xli
Documentation Accessibility............................................................................................................ xliv
What’s New with Large Objects (LOBs)?............................................................................... xlv
LOB Features Introduced with Oracle9i, Release 1 (9.0.1)............................................................. xlv
LOB Features Introduced with Oracle8i Release 2 (8.1.6)............................................................ xlviii
LOB Features Introduced with Oracle8i, Release 8.1.5................................................................ xlviii
1 Introduction to LOBs
Why Use LOBs?................................................................................................................................... 1-2
Unstructured Data........................................................................................................................ 1-2
LOB Datatype Helps Support Internet Applications.............................................................. 1-3
Using XML, LOBs, and Oracle Text (interMedia Text)........................................................... 1-3
Why Not Use LONGs?....................................................................................................................... 1-4
LONG-to-LOB Migration API.......................................................................................................... 1-5
SQL Semantics Support for LOBs................................................................................................... 1-5
Partitioned Index-Organized Tables and LOBs............................................................................ 1-6
iii
Extensible Indexing on LOBs........................................................................................................... 1-6
Function-Based Indexing on LOBs.................................................................................................. 1-8
XML Documents Can be Stored in XMLType Columns as CLOBs........................................... 1-8
LOB "Demo" Directory...................................................................................................................... 1-8
Compatibility and Migration Issues............................................................................................... 1-9
Examples in This Manual Use the Multimedia Schema........................................................... 1-10
2 Basic LOB Components
The LOB Datatype.............................................................................................................................. 2-2
Internal LOBs................................................................................................................................. 2-2
External LOBs (BFILEs)................................................................................................................ 2-2
Internal LOBs Use Copy Semantics, External LOBs Use Reference Semantics................... 2-3
Varying-Width Character Data......................................................................................................... 2-4
Using DBMS_LOB.LOADFROMFILE and Functions that Access OCI................................ 2-5
LOB Value and Locators.................................................................................................................... 2-6
Inline storage of the LOB value.................................................................................................. 2-6
LOB Locators................................................................................................................................. 2-6
Setting the LOB Column/Attribute to Contain a Locator...................................................... 2-6
Accessing a LOB Through a Locator.......................................................................................... 2-8
Creating Tables that Contain LOBs................................................................................................. 2-9
Initializing Internal LOBs to NULL or Empty.......................................................................... 2-9
Initializing LOBs Example Using Table Multimedia_tab..................................................... 2-10
Initializing Internal LOB Columns to a Value........................................................................ 2-11
Initializing External LOBs to NULL or a File Name.............................................................. 2-11
3 LOB Support in Different Programmatic Environments
Eight Programmatic Environments Operate on LOBs................................................................. 3-2
Comparing the LOB Interfaces......................................................................................................... 3-3
Using PL/SQL (DBMS_LOB Package) To Work With LOBs...................................................... 3-6
Provide a LOB Locator Before Invoking the DBMS_LOB Routine....................................... 3-6
PL/SQL - LOB Guidelines........................................................................................................... 3-7
PL/SQL Functions and Procedures that Operate on BLOBs, CLOBs, NCLOBs, and BFILEs....
3-7
PL/SQL Functions/Procedures To Modify BLOB, CLOB, and NCLOB Values................ 3-8
PL/SQL Functions/Procedures To Read or Examine Internal and External LOB Values 3-8
iv
PL/SQL Functions/Procedures To Operate on Temporary LOBs....................................... 3-9
PL/SQL Read-Only Functions/Procedures for BFILEs......................................................... 3-9
PL/SQL Functions/Procedures To Open and Close Internal and External LOBs............. 3-9
Using C (OCI) To Work With LOBs............................................................................................... 3-10
Set CSID Parameter To OCI_UCS2ID to Read/Write in UCS2........................................... 3-10
Offset and Amount Parameters: Fixed-Width Versus Varying-Width, Character or Byte
With OCI 3-11
OCILobLoadFromFile: Specify Amount Parameter to be Less than Length of BFILE..... 3-12
OCILobRead: Specify Amount Parameter to be 4 gigabytes - 1.......................................... 3-12
OCI LOB Examples..................................................................................................................... 3-12
Further Information About OCI............................................................................................... 3-13
OCI Functions that Operate on BLOBs, BLOBs, NCLOBs, and BFILEs............................. 3-13
OCI Functions To Modify Internal LOB (BLOB, CLOB, and NCLOB) Values.................. 3-14
OCI Functions To Read or Examine Internal LOB and External LOB (BFILE) Values.... 3-14
OCI Functions For Temporary LOBs....................................................................................... 3-14
OCI Read-Only Functions For BFILEs..................................................................................... 3-15
OCI LOB Locator Functions...................................................................................................... 3-15
OCI LOB-Buffering Functions.................................................................................................. 3-15
OCI Functions To Open and Close Internal and External LOBs......................................... 3-16
OCI Example — Is the LOB Open: main() and seeIfLOBIsOpen........................................ 3-16
Using C++ (OCCI) To Work With LOBs....................................................................................... 3-21
Distinct Classes for Each LOB Type......................................................................................... 3-22
Offset and Amount Parameters: Fixed-Width Versus Varying-Width, Character or Byte
With OCCI 3-23
Loading from Files with OCCIClob.copy() and OCCIBlob.copy(): Specify the Amount
Parameter to be Less than Length of BFILE 3-24
OCCIClob.read(), OCCIBlob.read(), and OCCIBfile.read(): Specify Amount Parameter to be
4 gigabytes - 1 3-25
Further Information About OCCI............................................................................................ 3-25
OCCI Methods that Operate on BLOBs, BLOBs, NCLOBs, and BFILEs............................ 3-25
OCCI Methods To Modify Internal LOB (BLOB, CLOB, and NCLOB) Values................ 3-25
OCCI Methods To Read or Examine Internal LOB and BFILE Values............................... 3-26
OCCI Read-Only Methods For BFILEs................................................................................... 3-26
Other OCCI LOB Methods........................................................................................................ 3-27
OCCI Methods To Open and Close Internal and External LOBs........................................ 3-27
Using C/C++ (Pro*C) To Work with LOBs................................................................................... 3-28
v
First Provide an Allocated Input Locator Pointer that Represents LOB............................ 3-28
Pro*C/C++ Statements that Operate on BLOBs, CLOBs, NCLOBs, and BFILEs.............. 3-28
Pro*C/C++ Embedded SQL Statements To Modify Internal LOBs (BLOB, CLOB, and
NCLOB) Values 3-29
Pro*C/C++ Embedded SQL Statements To Read or Examine Internal and External LOB
Values 3-29
Pro*C/C++ Embedded SQL Statements For Temporary LOBs........................................... 3-30
Pro*C/C++ Embedded SQL Statements For BFILEs............................................................. 3-30
Pro*C/C++ Embedded SQL Statements For LOB Locators................................................. 3-30
Pro*C/C++ Embedded SQL Statements For LOB Buffering............................................... 3-31
Pro*C/C++ Embedded SQL Statements To Open and Close Internal LOBs and External
LOBs (BFILEs) 3-31
Using COBOL (Pro*COBOL) to Work with LOBs..................................................................... 3-32
First Provide an Allocated Input Locator Pointer that Represents LOB............................ 3-32
Pro*COBOL Statements that Operate on BLOBs, CLOBs, NCLOBs, and BFILEs............ 3-32
Pro*COBOL Embedded SQL Statements To Modify Internal BLOB, CLOB, and NCLOB
Values 3-33
Pro*COBOL Embedded SQL Statements To Read or Examine Internal and External LOB
Values 3-34
Pro*COBOL Embedded SQL Statements For Temporary LOBs.......................................... 3-34
Pro*COBOL Embedded SQL Statements For BFILEs............................................................ 3-34
Pro*COBOL Embedded SQL Statements For LOB Locators................................................ 3-34
Pro*COBOL Embedded SQL Statements For LOB Buffering.............................................. 3-35
Pro*COBOL Embedded SQL Statements To Open and Close Internal LOBs and BFILEs.........
3-35
Using Visual Basic (Oracle Objects for OLE (OO4O)) to Work with LOBs.......................... 3-36
OO4O Syntax Reference and Further Information................................................................ 3-36
OraBlob, OraClob, and OraBfile Object Interfaces Encapsulate Locators.......................... 3-37
Example of OraBlob and OraBfile............................................................................................ 3-37
OO4O Methods and Properties to Access Data Stored in BLOBs, CLOBs, NCLOBs, and
BFILEs 3-38
OO4O Methods To Modify BLOB, CLOB, and NCLOB Values.......................................... 3-40
OO4O Methods To Read or Examine Internal and External LOB Values.......................... 3-41
OO4O Methods To Open and Close External LOBs (BFILEs)............................................. 3-41
OO4O Methods For Internal LOB-Buffering.......................................................................... 3-42
OO4O Properties For Operating on LOBs.............................................................................. 3-42
vi
OO4O Read-Only Methods For External Lobs (BFILEs)...................................................... 3-42
OO4O Properties For Operating on External LOBs (BFILEs).............................................. 3-43
Using Java (JDBC) To Work with LOBs....................................................................................... 3-44
Changing Internal Persistent LOBs Using Java...................................................................... 3-44
Reading Internal Persistent LOBs and External LOBs (BFILEs) with Java........................ 3-44
Calling DBMS_LOB Package From Java (JDBC).................................................................... 3-45
Referencing LOBs Using Java (JDBC)...................................................................................... 3-45
JDBC Syntax References and Further Information................................................................ 3-45
JDBC Methods for Operating on LOBs................................................................................... 3-45
JDBC oracle.sql.blob Methods To Modify BLOB Values...................................................... 3-47
JDBC oracle.sql.blob Methods To Read or Examine BLOB Values..................................... 3-47
JDBC oracle.sql.blob Methods and Properties for BLOB-Buffering.................................... 3-48
JDBC oracle.sql.clob Methods To Modify CLOB Values...................................................... 3-48
JDBC oracle.sql.CLOB Methods To Read or Examine CLOB Value................................... 3-48
JDBC oracle.sql.clob Methods and Properties for CLOB-Buffering.................................... 3-49
JDBC oracle.sql.bfile Methods To Read or Examine External LOB (BFILE) Values......... 3-49
JDBC oracle.sql.BFILE Methods and Properties for BFILE-Buffering................................ 3-50
JDBC: OracleBlob and OracleClob Do Not Work in Oracle8i 8.1.x and Higher Releases 3-50
JDBC Temporary LOB APIs...................................................................................................... 3-50
JDBC: Opening and Closing LOBs........................................................................................... 3-51
JDBC: Opening and Closing BLOBs........................................................................................ 3-52
JDBC: Opening and Closing CLOBs........................................................................................ 3-53
JDBC: Opening and Closing BFILEs........................................................................................ 3-55
Trimming LOBs using JDBC..................................................................................................... 3-59
JDBC: New LOB Streaming APIs............................................................................................. 3-60
New CLOB Streaming APIs...................................................................................................... 3-61
New BFILE Streaming APIs...................................................................................................... 3-62
JDBC and Empty LOBs.............................................................................................................. 3-67
OLEDB (Oracle Provider for OLEDB — OraOLEDB)............................................................... 3-69
4 Managing LOBs
DBA Actions Required Prior to Working with LOBs.................................................................. 4-2
Set Maximum Number of Open BFILEs................................................................................... 4-2
Using SQL DML for Basic Operations on LOBs...................................................................... 4-2
Changing Tablespace Storage for a LOB................................................................................... 4-3
vii
Managing Temporary LOBs.............................................................................................................. 4-4
Using SQL*Loader to Load LOBs.................................................................................................... 4-5
LOBFILES....................................................................................................................................... 4-5
Inline versus Out-of-Line LOBs....................................................................................................... 4-5
Loading InLine and Out-Of-Line Data into Internal LOBs Using SQL*Loader.................... 4-6
SQL*Loader Performance: Loading Into Internal LOBs......................................................... 4-6
Loading Inline LOB Data.................................................................................................................. 4-7
Loading Inline LOB Data in Predetermined Size Fields......................................................... 4-7
Loading Inline LOB Data in Delimited Fields.......................................................................... 4-8
Loading Inline LOB Data in Length-Value Pair Fields........................................................... 4-8
Loading Out-Of-Line LOB Data...................................................................................................... 4-9
Loading One LOB Per File......................................................................................................... 4-10
Loading Out-of-Line LOB Data in Predetermined Size Fields............................................ 4-11
Loading Out-of-Line LOB Data in Delimited Fields............................................................. 4-11
Loading Out-of-Line LOB Data in Length-Value Pair Fields............................................... 4-12
SQL*Loader LOB Loading Tips..................................................................................................... 4-13
LOB Restrictions............................................................................................................................... 4-14
LONG to LOB Migration Limitations........................................................................................... 4-17
Removed Restrictions...................................................................................................................... 4-17
5 Large Objects: Advanced Topics
Introducing Large Objects: Advanced Topics............................................................................... 5-2
Read Consistent Locators.................................................................................................................. 5-2
A Selected Locator Becomes a Read Consistent Locator........................................................ 5-2
Updating LOBs and Read-Consistency..................................................................................... 5-3
Example of an Update Using Read Consistent Locators........................................................ 5-3
Updating LOBs Via Updated Locators...................................................................................... 5-6
Example of Updating a LOB Using SQL DML and DBMS_LOB.......................................... 5-6
Example of Using One Locator to Update the Same LOB Value........................................... 5-8
Example of Updating a LOB with a PL/SQL (DBMS_LOB) Bind Variable....................... 5-10
LOB Locators Cannot Span Transactions................................................................................ 5-13
Example of Locator Not Spanning a Transaction.................................................................. 5-14
LOB Locators and Transaction Boundaries.................................................................................. 5-15
Transaction IDs: Reading and Writing to a LOB Using Locators........................................ 5-15
Non-Serializable Example: Selecting the Locator with No Current Transaction.............. 5-16
viii
Non-Serializable Example: Selecting the Locator within a Transaction............................. 5-17
LOBs in the Object Cache............................................................................................................... 5-18
LOB Buffering Subsystem.............................................................................................................. 5-19
Advantages of LOB Buffering................................................................................................... 5-19
Guidelines for Using LOB Buffering....................................................................................... 5-19
LOB Buffering Usage Notes...................................................................................................... 5-21
Flushing the LOB Buffer ........................................................................................................... 5-23
Flushing the Updated LOB....................................................................................................... 5-24
Using Buffer-Enabled Locators................................................................................................. 5-25
Saving Locator State to Avoid a Reselect................................................................................ 5-25
OCI Example of LOB Buffering................................................................................................ 5-26
Creating a Varray Containing References to LOBs.................................................................... 5-29
LOBs in Partitioned Index-Organized Tables............................................................................. 5-30
Example of LOB Columns in Partitioned Index-Organized Tables.................................... 5-30
Restrictions for LOBs in Partitioned Index-Organized Tables................................................ 5-31
Range Partitioned Index-Organized Table LOB Restrictions.............................................. 5-31
Hash Partitioned Index-Organized Table LOB Restrictions................................................ 5-32
6 Frequently Asked Questions about LOBs
Converting Data Types to LOB Data Types................................................................................... 6-4
Can I Insert or Update Any Length Data Into a LOB Column?............................................ 6-4
Does COPY LONG to LOB Work if Data is > 64K?................................................................. 6-4
General.................................................................................................................................................. 6-5
How Do I Determine if the LOB Column with a Trigger is Being Updated?...................... 6-5
Reading and Loading LOB Data: What Should Amount Parameter Size Be?..................... 6-5
Is LOB Data Lost After a Crash?................................................................................................. 6-7
Index-Organized Tables (IOTs) and LOBs..................................................................................... 6-7
Is Inline Storage Allowed for LOBs in Index-Organized Tables?......................................... 6-7
Initializing LOB Locators.................................................................................................................. 6-8
When Do I Use EMPTY_BLOB() and EMPTY_CLOB()?........................................................ 6-8
How Do I Initialize a BLOB Attribute Using EMPTY_BLOB() in Java?............................... 6-9
JDBC, JPublisher and LOBs............................................................................................................. 6-9
How Do I Insert a Row With Empty LOB Locator into Table Using JDBC?....................... 6-9
How Do I setData to EMPTY_BLOB() Using JPublisher?.................................................... 6-10
JDBC: Do OracleBlob and OracleClob Work in 8.1.x?.......................................................... 6-10
ix
How Do I Manipulate LOBs With the 8.1.5 JDBC Thin Driver?.......................................... 6-11
Is the FOR UPDATE Clause Needed on SELECT When Writing to a LOB?..................... 6-12
What Does DBMS_LOB.ERASE Do?....................................................................................... 6-12
Can I Use putChars()?................................................................................................................ 6-12
Manipulating CLOB CharSetId in JDBC................................................................................. 6-13
Why is Inserting into BLOBs Slower than into LONG Raws?............................................. 6-13
Why Do I Get an ORA-03127 Error with LobLength on a LONG Column?...................... 6-13
How Do I Create a CLOB Object in a Java Program?............................................................ 6-14
How do I Load a 1Mb File into a CLOB Column?................................................................. 6-15
How Do We Improve BLOB and CLOB Performance When Using JDBC Driver To Load?.....
6-15
LOB Indexing..................................................................................................................................... 6-18
Is LOB Index Created in Same Tablespace as LOB Data?..................................................... 6-18
Indexing: Why is a BLOB Column Removed on DELETing but not a BFILE Column?.. 6-18
Which Views Can I Query to Find Out About a LOB Index?.............................................. 6-19
LOB Storage and Space Issues....................................................................................................... 6-19
What Happens If I Specify LOB Tablespace and ENABLE STORAGE IN ROW?............ 6-19
What Are the Pros and Cons of Storing Images in a BFILE Versus a BLOB?.................... 6-20
When Should I Specify DISABLE STORAGE IN ROW?....................................................... 6-21
Do <4K BLOBs Go Into the Same Segment as Table Data, >4K BLOBs Go Into a Specified
Segment? 6-21
Is 4K LOB Stored Inline?............................................................................................................ 6-21
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? 6-22
Storing CLOBs Inline: DISABLING STORAGE and Space Used........................................ 6-23
Should I Include a LOB Storage Clause When Creating Tables With Varray Columns? 6-23
LONG to LOB Migration................................................................................................................. 6-25
How Can We Migrate LONGs to LOBs, If Our Application Cannot Go Down?.............. 6-25
Converting Between Different LOB Types.................................................................................. 6-26
Is Implicit LOB Conversion Between Different LOB Types Allowed?............................... 6-26
Performance....................................................................................................................................... 6-26
What Can We Do To Improve the Poor LOB Loading Performance When Using Veritas File
System on Disk Arrays, UNIX, and Oracle? 6-26
Is There a Difference in Performance When Using DBMS_LOB.SUBSTR Versus DBMS_
LOB.READ? 6-28
Are There Any White Papers or Guidelines on Tuning LOB Performance?..................... 6-28
x