Oracle9i Application Developer's Guide - Large Objects (LOBs) using PL/SQL

606 Pages·2001·3.597 MB·English

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

Oracle9i
Application Developer’s Guide - Large Objects (LOBs) using PL/SQL
Release 1 (9.0.1)
June 2001
Part No. A88882-01
Oracle9i Application Developer’s Guide - Large Objects (LOBs) using PL/SQL, Release 1 (9.0.1)
Part No. A88882-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 Shores, 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 is a registered trademark, and PL/SQL, Pro*Ada, Pro*C, Pro*C/C++ , Pro*COBOL, SQL*Forms,
SQL*Loader, SQL*Plus,Oracle7, Oracle8, Oracle8i, Oracle9i are trademarks or registered trademarks of
OracleCorporation.Allothercompanyorproductnamesmentionedareusedforidentificationpurposes
only and may be trademarks of their respective owners.
Contents
Send Us Your Comments................................................................................................................. xxi
Preface........................................................................................................................................................ xxiii
Intended Audience............................................................................................................................ xxiv
Structure.............................................................................................................................................. xxiv
Related Documents........................................................................................................................... xxvii
How to Order this Manual............................................................................................................... xxix
Conventions........................................................................................................................................ xxix
Documentation Accessibility.......................................................................................................... xxxii
....................................................................................................................................................................... xxxiii
What’s New with Large Objects (LOBs)?........................................................................... xxxiii
LOB Features Introduced with Oracle9i, Release 1 (9.0.1)......................................................... xxxiii
LOB Features Introduced with Oracle8i Release 2 (8.1.6).......................................................... xxxvi
LOB Features Introduced with Oracle8i, Release 8.1.5.............................................................. xxxvi
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
iii
SQL Semantics Support for LOBs................................................................................................... 1-5
Partitioned Index-Organized Tables and LOBs............................................................................ 1-6
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
iv
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
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
....................................................................................................................................................... 3-10
OLEDB (Oracle Provider for OLEDB — OraOLEDB).............................................................. 3-11
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
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
v
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
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
vi
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
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
vii
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 in Oracle8i?........... 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
When Should I Use Chunks Over Reading the Whole Thing?............................................ 6-28
Is Inlining the LOB a Good Idea and If So When?................................................................. 6-29
How Can I Store LOBs >4Gb in the Database?...................................................................... 6-29
Why is Performance Affected When Temporary LOBs are Created in a Called Routine?.........
6-30
PL/SQL................................................................................................................................................ 6-32
UPLOAD_AS_BLOB.................................................................................................................. 6-32
7 Modeling and Design
Selecting a Datatype........................................................................................................................... 7-2
LOBs Compared to LONG and LONG RAW Types............................................................... 7-2
Character Set Conversions: Working with Varying-Width and Multibyte Fixed-Width
Character Data 7-3
Selecting a Table Architecture.......................................................................................................... 7-3
LOB Storage......................................................................................................................................... 7-4
Where are NULL Values in a LOB Column Stored?............................................................... 7-4
Defining Tablespace and Storage Characteristics for Internal LOBs.................................... 7-5
LOB Storage Characteristics for LOB Column or Attribute................................................... 7-6
TABLESPACE and LOB Index.................................................................................................... 7-6
PCTVERSION................................................................................................................................ 7-7
CACHE / NOCACHE / CACHE READS................................................................................ 7-8
LOGGING / NOLOGGING........................................................................................................ 7-9
viii
CHUNK........................................................................................................................................ 7-10
ENABLE | DISABLE STORAGE IN ROW............................................................................. 7-11
Guidelines for ENABLE or DISABLE STORAGE IN ROW................................................. 7-11
How to Create Gigabyte LOBs....................................................................................................... 7-12
Example 1: Creating a Tablespace and Table to Store Gigabyte LOBs............................... 7-12
Example 2: Creating a Tablespace and Table to Store Gigabyte LOBs............................... 7-13
LOB Locators and Transaction Boundaries.................................................................................. 7-14
Binds Greater Than 4,000 Bytes in INSERTs and UPDATEs.................................................... 7-14
Binds Greater than 4,000 Bytes are Now Allowed For LOB INSERTs and UPDATEs.... 7-14
Binds of More Than 4,000 Bytes ... No HEX to RAW or RAW to HEX Conversion......... 7-15
4,000 Byte Limit On Results of SQL Operator........................................................................ 7-16
Binds of More Than 4,000 Bytes: Restrictions......................................................................... 7-16
Example: PL/SQL - Using Binds of More Than 4,000 Bytes in INSERT and UPDATE... 7-16
Example: PL/SQL - Binds of More Than 4,000 Bytes -- Inserts Not Supported Because Hex
to Raw/Raw to Hex Conversion is Not Supported 7-18
Example: PL/SQL - 4,000 Byte Result Limit in Binds of More than 4,000 Bytes When Data
Includes SQL Operator 7-18
Example: C (OCI) - Binds of More than 4,000 Bytes For INSERT and UPDATE.............. 7-19
OPEN, CLOSE, and ISOPEN Interfaces for Internal LOBs...................................................... 7-22
Example 1: Correct Use of OPEN/CLOSE Calls to LOBs in a Transaction....................... 7-23
Example 2: Incorrect Use of OPEN/CLOSE Calls to a LOB in a Transaction................... 7-24
LOBs in Index Organized Tables (IOT)........................................................................................ 7-24
Example of Index Organized Table (IOT) with LOB Columns........................................... 7-25
Manipulating LOBs in Partitioned Tables................................................................................... 7-26
Creating and Partitioning a Table Containing LOB Data..................................................... 7-28
Creating an Index on a Table Containing LOB Columns..................................................... 7-30
Exchanging Partitions Containing LOB Data......................................................................... 7-31
Adding Partitions to Tables Containing LOB Data............................................................... 7-31
Moving Partitions Containing LOBs....................................................................................... 7-31
Splitting Partitions Containing LOBs...................................................................................... 7-31
Indexing a LOB Column.................................................................................................................. 7-32
Functional Indexes on LOB Columns...................................................................................... 7-32
SQL Semantics Support for LOBs................................................................................................. 7-33
Improved LOB Usability: You can Now Access LOBs Using SQL “Character” Functions........
7-33
SQL and PL/SQL VARCHAR2 Functions/Operators Now Allowed for CLOBs............ 7-34
ix
PL/SQL Relational Operators Now Allowed for LOBs........................................................ 7-34
SQL and PL/SQL CHAR to CLOB Conversion Functions................................................... 7-35
Non-Supported SQL Functionality for LOBs......................................................................... 7-35
Using SQL Functions and Operators for VARCHAR2s on CLOBs..................................... 7-35
UNICODE Support for VARCHAR2 and CLOB................................................................... 7-39
SQL Features Where LOBs Cannot be Used........................................................................... 7-40
How SQL VARCHAR2/RAW Semantics Apply to CLOBs/BLOBs......................................... 7-40
Defining CHAR Buffer on CLOB............................................................................................. 7-40
Accepting CLOBs in VARCHAR2 Operators/Functions..................................................... 7-41
Returning CLOB Values from SQL Functions/Operators................................................... 7-41
IS [NOT] NULL in VARCHAR2s and CLOBs........................................................................ 7-43
SQL RAW Type and BLOBs............................................................................................................ 7-44
SQL DML Changes For LOBs......................................................................................................... 7-44
SQL Functions/Operators for VARCHAR2s/RAWs and CLOBs/BLOBs............................... 7-45
PL/SQL Statements and Variables: New Semantics Changes.................................................. 7-45
Implicit Conversions Between CLOB and VARCHAR2....................................................... 7-46
PL/SQL Example 1: Prior Release SQL Interface for a CLOB/VARCHAR2 Application.........
7-46
PL/SQL Example 2: Accessing CLOB Data When Treated as VARCHAR2s.................... 7-47
PL/SQL Example 3: Defining a CLOB Variable on a VARCHAR2.................................... 7-47
Explicit Conversion Functions.................................................................................................. 7-48
VARCHAR2 and CLOB in PL/SQL Built-in Functions........................................................ 7-48
PL/SQL Example 4: CLOB Variables in PL/SQL.................................................................. 7-49
PL/SQL Example 5: Change in Locator-Data Linkage......................................................... 7-49
PL/SQL Example 6: Freeing Temporary LOBs Automatically and Manually.................. 7-50
PL/SQL CLOB Comparison Rules................................................................................................. 7-51
Interacting with SQL and PL/SQL in OCI and Java Interfaces............................................... 7-52
Performance Attributes When Using SQL Semantics with LOBs.......................................... 7-52
Inserting More than 4K Bytes Data Into LOB Columns........................................................ 7-52
Temporary LOB Creation/Deallocation................................................................................. 7-53
Performance Measurement....................................................................................................... 7-53
User-Defined Aggregates and LOBs.............................................................................................. 7-54
UDAGs: DDL Support............................................................................................................... 7-55
UDAGs: DML and Query Support.......................................................................................... 7-55
x